Case 003Forecasting and modellingHard

Model test: build one year of income statement, balance sheet and cash flow for Ombrano Packaging from a handful of drivers, and make the balance sheet balance.

The situation

Ombrano Packaging makes corrugated boxes. Opening balance sheet: plant Rs 600 crore, working capital Rs 240 crore, cash Rs 80 crore; debt Rs 400 crore and equity Rs 520 crore.

Drivers for next year: revenue of Rs 1,200 crore grows 10%; EBITDA margin 18%; depreciation Rs 60 crore; interest at 8% on opening debt; tax 25%; working capital stays at 20% of revenue; capex Rs 90 crore; Rs 50 crore of debt is repaid; a dividend of Rs 20 crore is paid. You have forty minutes and a blank sheet.

Your task

Build the three statements for the year, show that the balance sheet balances, and explain why cash falls in a profitable year.

Quick check

Which statement should produce the closing cash balance?

Worked solution

Try it on paper, then open one step at a time.

30-second answerThe answer to give first

Net income is Rs 109.2 crore, cash falls by Rs 14.8 crore to Rs 65.2 crore, and both sides of the balance sheet close at Rs 959.2 crore. Operations generate Rs 145.2 crore after a Rs 24 crore working capital build, but capex, debt repayment and the dividend take Rs 160 crore. Cash comes from the cash flow statement, not as a plug, which is what makes the balance a real check.

Step 1Where do you start when you have forty minutes?

Start with the income statement, because it feeds both the others. Revenue is Rs 1,320 crore, EBITDA at 18% is Rs 237.6 crore, less depreciation of 60 and interest of 32 on the opening Rs 400 crore of debt, gives pre-tax profit of Rs 145.6 crore and, after 25% tax, net income of Rs 109.2 crore. Charge interest on the opening balance, and say so, because interest on the closing balance makes the model circular. Circularity is a real feature of full models, but in a timed test it costs time and earns nothing.

Step 2How do the three statements lock together?

Think of a household's month: the salary slip says what you earned, the bank statement says where the money went, and the list of what you own and owe at month end must agree with both. Every change in a balance sheet line has to appear somewhere on the cash flow statement, and the cash line is where the two meet. Net income goes to the top of the cash flow and into equity. Depreciation is added back and taken off plant. The Rs 24 crore working capital build, 20% of the Rs 120 crore revenue increase, is subtracted from cash and added to working capital. Capex adds to plant, the repayment cuts debt, and the dividend cuts equity.

Every balance sheet line moves for a reason the other two statements recordIncome statementRevenue1,320.0EBITDA, 18%237.6Depreciation(60.0)Interest, 8% x 400(32.0)Tax at 25%(36.4)Net income109.2Cash flow statementNet income109.2Add back depreciation60.0Working capital build(24.0)Capex(90.0)Debt repaid(50.0)Dividend(20.0)Change in cash(14.8)Balance sheet, year endPlant, 600 + 90 - 60630.0Working capital264.0Cash, 80 - 14.865.2Debt, 400 - 50350.0Equity, +NI - div609.2Assets 959.2Debt + equity 959.2Net income also lands in equity, less the dividendChange in cash closes the loop to the cash line
Ombrano's net income of Rs 109.2 crore feeds both the cash flow statement and equity; after depreciation, the working capital build, capex, repayment and the dividend, cash falls by Rs 14.8 crore, and closing cash of Rs 65.2 crore makes assets equal debt plus equity at Rs 959.2 crore.
Balance sheet, Rs croreOpeningMovementClosing
Plant600.0+90 capex, -60 depreciation630.0
Working capital, 20% of revenue240.0+24.0264.0
Cash80.0-14.8 from the cash flow65.2
Total assets920.0959.2
Debt400.0-50 repaid350.0
Equity520.0+109.2 profit, -20 dividend609.2
Debt plus equity920.0959.2
Every movement on Ombrano's balance sheet is traced to the income statement or the cash flow statement, and total assets of Rs 959.2 crore equal debt plus equity of Rs 959.2 crore without any plug.
Step 3Why does cash fall when the company made Rs 109 crore?

Profit is not cash, and cash is not only spent on running the business. Operations produce Rs 145.2 crore, but capex of 90, repayment of 50 and a dividend of 20 take Rs 160 crore, so cash falls Rs 14.8 crore. That is a choice, not a problem: Ombrano is reinvesting and deleveraging at the same time. The follow-up is usually how long it can keep doing both, and the answer is about five years at this pace before cash runs out, unless growth slows the working capital build.

Why cash falls in a year that makes Rs 109 crore of profit, Rs crore80Opening+145.2Operations-90Capex-50Debt repaid-20Dividend65.2Closing
Ombrano opens with Rs 80 crore of cash, adds Rs 145.2 crore from operations, then spends Rs 90 crore on capex, Rs 50 crore on debt repayment and Rs 20 crore on the dividend, closing at Rs 65.2 crore.
Step 4What if your balance sheet does not balance?

Read the size of the gap before hunting for it. An imbalance equal to one number in the model usually points straight at the missed link. Out by 60 means depreciation was added back on the cash flow but not taken off plant. Out by 24 means working capital moved on one statement only. Out by 20 is the dividend. Saying this out loud when your sheet is off is worth more to the interviewer than a sheet that balanced by a plug.

Where candidates lose it

The fastest way to fail a model test is to make cash the balancing figure. The sheet then balances whatever is wrong, and the interviewer checks the cash flow statement first because they know this trick.

The second is charging interest on closing debt, which creates a circular reference, and then losing ten minutes to a spreadsheet that will not settle. Use opening balances and state it.

What the interviewer asks next

  • Ombrano also pays Rs 10 crore of interest income on its cash. Where does it go on each statement?
  • How would a Rs 100 crore asset write-down change each statement?
  • If working capital rose to 22% of revenue, what would closing cash be?
  • How would you add a revolving credit line that draws when cash falls below Rs 50 crore?

Company names and figures are illustrative.