MRP Finance Internship Unit 03

Unit 03 · Practice solutions

Practice solutions

Forecasting the Financing Gap · Ghana · GH₵

Worked answers to the optional practice questions. The required question is turned in to the TAs, and its answer is not published here.

Practice solutions

3.1

  1. First, when revenue is changed the inventory figure will not move, so every downstream total, the plug, and the financing gap will be silently wrong. Second, anyone reviewing the model cannot see what assumption produced 4,200, so the error is undetectable without rebuilding it. The cell should contain the calculation — days of inventory × cost of goods sold ÷ 365 — so it recalculates whenever any driver changes.
  2. Inputs: enrolment growth, fee per student, staff cost per student, construction cost of the campus, timing of the build, existing debt and its rate, fee collection period, and any government grant. Output: the funding shortfall — how much the school must borrow or raise to complete the campus without running out of cash.
  3. The comparison is fair and the conclusion is not: the model's job is not to hit the point estimate but to show what the requirement depends on. Last year's error is useful information — we should look at which assumption was wrong and by how much, and widen the range on that input this year. Treat GH₵3.8 million as the centre of a range, and let the sensitivity table tell you how wide the range should be.

3.2

  1. The question is whether the jump from 14% to 22% reflects a one-off event or a change in how the business collects. Evidence for a one-off: a single large invoice raised close to year end, a known customer dispute since resolved, or an unusual sales pattern in the final month. Evidence for a trend: the ratio rose steadily rather than jumping, credit terms were formally relaxed, or the receivables ageing shows a general drift rather than one large balance.
  2. Let last year's balance be B and last year's sales be S, so the ratio is B/S. Next year's sales are S(1+g). Applying the ratio: (B/S) × S(1+g) = B(1+g). Growing the balance: B × (1+g) = B(1+g). Identical.
  3. At 9%: 40 × 9% = GH₵3.6M. At 3%: 40 × 3% = GH₵1.2M. Use the 9% figure, because two years of consistent history and a specific, checkable explanation for the third year is the classic case for treating the deviation as noise. Ask for the payables ageing at the year-end date and one month later — if the balance rebounded in the new year, management's explanation is confirmed.

3.3

  1. Sales-driven: cost of goods sold; accounts payable. Asset-driven: depreciation. Debt-driven: interest expense. Policy: share capital. Identity: retained earnings; gross profit. Rate applied to a computed figure: taxes.
  2. Closing PP&E = 42 + 9 − 6 = GH₵45M. A percentage of sales would be unreliable because capital spending is lumpy — the firm buys assets in discrete blocks, so in any single year actual capex may be far above or far below the smooth average that a percentage implies, and the closing balance would be wrong in both the year of purchase and the years around it.
  3. Equity is an identity, not a ratio to be assumed — it is determined by opening equity plus net income less dividends, plus any new shares issued. Forecasting it as a share of assets means the balance sheet will balance by construction, which destroys the entire purpose of the model: the financing gap only appears because equity is computed independently of the asset side. The correct mechanic is closing equity = opening equity + net income − dividends + new share issues.

3.4

  1. Equity falls from 12.96 to 11.46, so funding falls by GH₵1.5M and required long-term debt rises to GH₵16.81M. Gap = GH₵5.31M, up from GH₵3.81M. Paying a dividend takes cash out of the business, so every cedi distributed is a cedi that must be replaced by outside finance.
  2. Repay debt — right when leverage is high or covenants are tight, as at Adom. Return cash to owners through a dividend or share buy-back — right when the firm has no investment opportunities earning more than its cost of capital. Accumulate cash — right when a known large outlay is coming, or when funding conditions are unreliable and holding a reserve has option value.
  3. One workable combination is DSO 50 and DIO 46: receivables 100 × 50/365 = 13.70 and inventory 85 × 46/365 = 10.71, giving total assets of 38.91 and a plug of 11.64, so the gap is GH₵0.14M — comfortably inside the limit. A lighter combination, DSO 55 and DIO 46, gives a gap of about GH₵1.5M. Both are realistic in the sense that Adom achieved better than these figures in Year 1, but neither happens automatically: 50-day collections require a change in credit control that takes months and may cost some accounts.

3.5

  1. Retained profit is 6 − 2.4 = GH₵3.6M, so closing equity is GH₵33.6M. Keeping D/E at 1.0 requires closing debt of GH₵33.6M, which means GH₵3.6M of new borrowing. Sustainable growth means no new equity at unchanged leverage; it does not mean no new debt.
  2. Gap = 22% − 12% = 10 percentage points. The unchanged-ratio plan supports 12% asset growth; the additional 10% requires about 75 × 0.10 = GH₵7.5M of financing beyond that plan. This could come from issuing equity, increasing leverage, retaining more, or improving margin or asset turnover; it is not the total borrowing in the forecast.
  3. From DuPont, ROE = margin × asset turnover × leverage, and SGR = ROE × retention. Asset turnover appears as a direct multiplicative term in ROE, so raising it raises ROE proportionally and therefore raises SGR proportionally, holding margin, leverage and retention fixed. For Adom, cutting the collection period reduces receivables, which reduces total assets, which raises asset turnover — so the same action that releases trapped cash today also raises the rate at which the company can grow without issuing new equity or changing leverage.

3.6

  1. First, seven significant figures imply a precision the model cannot possibly have, given that its inputs are estimates. Second, a single point tells the reader nothing about which assumptions the figure depends on or how much it would move if one were wrong. Rewrite: "The project is worth roughly GH₵4.5 to GH₵5 million, and the estimate is most sensitive to the revenue growth assumption."
  2. Moving from the top-left cell (70-day collections, 60 days of inventory) to the bottom-right (40-day collections, 40 days of inventory) the gap falls steadily from +GH₵8.88M to −GH₵4.00M — a swing of GH₵12.88M driven entirely by working-capital discipline, with the growth rate held at 25% in every cell. I would target 50-day collections with inventory left at 50 days, giving a gap of +GH₵1.07M: it recovers half the drift in collections, is achievable within a year through credit control alone, and does not require cutting stock in a perishable-goods business where a shortage costs a customer permanently.
  3. Gross margin would dominate any question about profitability or value rather than funding — for example, "is Adom worth buying?" or "what is this company's operating income in three years?". A one-point change in gross margin is GH₵1M of operating income on GH₵100M of revenue, which is roughly a third of net income and would move a valuation substantially. It barely touches the financing gap because the gap is driven by the size of the asset side, and margin reaches the balance sheet only through retained earnings, which is a small and heavily taxed channel.