Build a small financial model from a blank workbook, then check whether you can explain its results. This assignment tests the statement links rather than your ability to copy a finished template.
If you need the method first, complete the worked Excel walkthrough. The practice workbook (.xlsx) contains the solution and a Practice sheet. Close the Model sheet while attempting the assignment.
The case brief
A fictional product business needs a two-year annual forecast. Management wants to see profit, closing cash and how a lower-sales case affects funding. Use one currency unit throughout. There is no valuation or transaction financing requirement.
Opening assets are cash 20,000, receivables 5,000, inventory 6,000 and net equipment 20,000. Opening payables are 4,500, debt is 10,000 and equity is 36,500. Opening assets and liabilities plus equity both equal 51,000.
The forecast inputs
| Assumption | Year 1 | Year 2 |
|---|---|---|
| Units sold | 1,000 | 1,100 |
| Price per unit | 50 | 52 |
| Variable cost per unit | 30 | 31 |
| Fixed operating expenses | 15,000 | 16,000 |
| Receivables / revenue | 10% | 10% |
| Inventory / variable costs | 20% | 20% |
| Payables / variable costs | 15% | 15% |
| Capital expenditure | 4,000 | 4,500 |
| Depreciation | 3,000 | 3,500 |
| Interest on opening debt | 6% | 6% |
| Tax on positive pretax profit | 25% | 25% |
| New borrowing | 0 | 0 |
| Principal repayment | 1,000 | 1,000 |
| Dividends | 0 | 0 |
The working-capital percentages determine year-end balances. They are not collection or payment days. Depreciation is a simplified annual assumption. Ignore deferred tax, tax-loss carryforwards, disposals, loan fees and other transactions. Do not invent a funding plug to make cash or the balance sheet meet a target.
Build the model in a clear order
- Enter and reconcile the opening balance sheet.
- Create a labeled assumption area with aligned forecast periods.
- Calculate revenue, operating profit, interest, tax and net income.
- Forecast receivables, inventory and payables.
- Roll forward net equipment, debt and equity.
- Calculate operating, investing and financing cash flows.
- Use the net cash change to calculate closing cash.
- Complete the balance sheet and add reconciliation differences.
Keep each calculation in one place and link related schedules to it. The next year’s opening balance should come from the prior year’s closing balance. Do not type an answer into a formula cell because the result looks wrong.
Check your base case
Show the base-case answer figures
| Output | Year 1 | Year 2 |
|---|---|---|
| Revenue | 50,000 | 57,200 |
| Net income | 1,050 | 2,295 |
| Operating cash flow | 4,050 | 4,870 |
| Net cash change | −950 | −630 |
| Closing cash | 19,050 | 18,420 |
| Total assets | 51,050 | 52,960 |
| Balance sheet difference | 0.00 | 0.00 |
If the answers differ, find the first schedule where they diverge. Inspect signs, opening balances, copied references and the distinction between a balance and a change in that balance.
Run the downside and explain it
Change Year 1 volume to 800 units. Keep all other assumptions unchanged. Work out the revenue effect, then trace income, receivables, inventory, payables and cash.
Show the Year 1 downside answers
Revenue is 40,000, net income is −2,600 and operating cash flow is 1,700. After capital expenditure of 4,000 and debt repayment of 1,000, closing cash is 16,700. The balance sheet difference remains zero.
Explain why the fall in net income does not equal the fall in closing cash. Explain how changing Year 1 also affects the opening balances used by Year 2.
Find an error deliberately
Make a separate copy. Reverse one working-capital cash-flow sign or stop the second year’s debt balance from linking to the first. See whether your reconciliations catch the error, then trace and correct it.
A balance-sheet check will not catch every error. A wrong sales assumption can still produce a balanced model. Review both the accounting mechanics and the business reasoning.
Write a short handover
Summarize the question, base-case result, downside result and main limitations. Identify the assumptions you would need to replace with real evidence before using the model for a business decision.
You have completed the exercise when you can rebuild the model, reconcile it, explain a changed assumption and find an intentional error. Recording the time can help plan further practice, but there is no universal pass time for this teaching case.
Choose the next exercise
Return to Excel skills if references or copying caused errors. Use the skills checklist for accounting and review tasks. Once the foundations are clear, try a different application, such as the worked property model.
