Learn financial modelling by building a transparent forecast whose results change when its assumptions change. Start with units, prices, variable costs, and fixed costs, then distinguish profit from cash timing. This roadmap uses a fictional workshop business and intentionally excludes tax, financing, depreciation, and investment valuation. The output is a practice spreadsheet with checked formulas and scenarios, not financial advice or a prediction of an actual business’s performance.
Separate inputs, calculations, and outputs
Create an Inputs sheet, a monthly calculation sheet, and a summary. Record every assumption with its unit and explanation. Use one place for the ticket price so a later change updates the model consistently. ICAEW’s spreadsheet principles address design, build, and control practices Source 1. Apply that discipline to a small workbook before attempting a complex financial statement model.
Set the fictional base case at four workshops per month, ten paid attendees per workshop, and ₹500 per attendee. Variable materials cost ₹100 per attendee; fixed monthly costs are ₹8,000. Label these invented assumptions prominently. They are not researched market prices, and the model should not imply that a real venue or supplier has quoted them.
Build the operating calculation and check it
Paid attendance is four multiplied by ten, or forty attendees. Revenue is forty multiplied by ₹500, giving ₹20,000. Variable cost is forty multiplied by ₹100, or ₹4,000. Contribution is ₹16,000, and the simplified operating surplus after fixed costs is ₹8,000. Keep the calculation labels precise because the exercise omits several items that a complete set of accounts might contain.
Contribution per attendee is ₹400. Dividing ₹8,000 fixed costs by ₹400 gives twenty attendees as the simplified break-even volume under these assumptions. This is an arithmetic threshold, not a guarantee of business viability. It assumes the stated price, cost behaviour, and capacity remain valid. Test the model with zero attendees and verify that it shows an ₹8,000 operating shortfall rather than a formula error.
The SEC’s beginner guide distinguishes the roles of financial statements Source 2. Use that conceptual distinction to avoid calling the operating-surplus line a cash balance. A business can recognise revenue and expenses at different times from the related receipts and payments.
Add cash timing and sensitivity
Assume the business starts the month with ₹5,000 cash. All ₹20,000 ticket revenue is collected during the month, and the ₹4,000 variable cost and ₹8,000 fixed cost are paid during it. Closing cash is therefore ₹13,000. Now delay collection of ₹5,000 until the following month: the operating calculation is unchanged under the exercise’s accrual assumption, but closing cash falls to ₹8,000.
OpenStax explains accounting assumptions and the relationship of recognised activity to statements Source 3. Keep this exercise educational and confirm the appropriate accounting treatment for any real organisation with qualified support. Do not import simplified practice rules into tax reporting or statutory accounts.
Create a downside scenario with twenty-four attendees at the same price and variable cost. Revenue becomes ₹12,000, variable cost ₹2,400, contribution ₹9,600, and operating surplus ₹1,600. A separate scenario with sixteen attendees produces ₹8,000 revenue, ₹1,600 variable cost, and a ₹1,600 operating shortfall. Change assumptions through inputs, not by overwriting output formulas.
Review the model as a decision tool
Add checks that attendee totals are non-negative, formulas cover every month, and cash roll-forwards reconcile. Use formatting to identify inputs, but do not rely on colour alone; label the sections. Protect or clearly mark formula cells if that helps prevent accidental edits. Inspect the model after inserting a month or changing an attendance assumption.
Write a short interpretation explaining which assumptions most affect the result and what information a real decision would require. Capacity, refunds, staffing, taxes, and payment timing are obvious gaps in this simplified model. Do not present a smooth twelve-month chart as evidence that the forecast is accurate.
Submit the workbook, assumptions log, base and downside scenarios, expected calculations, and limitations. Ask a reviewer to change ticket price and trace its effect on revenue, contribution, and break-even volume. A model is useful when its logic can be inspected and challenged, not merely when its final number looks encouraging.
- AssumeState units, prices, costs, and timing.
- CalculateConnect inputs through transparent formulas.
- Stress-testChange volume and receipt timing.
- InterpretExplain the result and missing factors.
| Checkpoint | What to produce | How to review it |
|---|---|---|
| Base revenue | 40 × ₹500 = ₹20,000 | Attendance and price are explicit inputs |
| Contribution | ₹20,000 − ₹4,000 = ₹16,000 | Variable costs follow attendees |
| Break-even | ₹8,000 / ₹400 = 20 attendees | Simplified assumptions only |
| Cash timing | ₹13,000 becomes ₹8,000 | Delay ₹5,000 collection without changing earned revenue |
- Operating view
- Revenue minus the included operating costs.
- Cash view
- Opening cash plus receipts minus payments.
- Decision limit
- A practice model is not a verified commercial forecast.
Common questions
Do I need advanced Excel before modelling?
You need reliable basic formulas, references, and checking habits more than advanced features. Build the small model manually and verify its arithmetic first. Learn additional functions when the question requires them. Complex formulas can hide weak assumptions, while a simple transparent workbook is easier for a reviewer to inspect and challenge.
Why keep assumptions on a separate sheet?
It makes the model’s drivers visible and reduces inconsistent hard-coded values. A ticket-price change should flow through every relevant calculation from one input. Include units and explanations so another person understands the value. A separate sheet is helpful only if formulas actually reference it rather than containing hidden alternative assumptions.
Is operating surplus the same as cash?
No. The practice operating calculation records the included revenue and costs, while cash depends on when money is received and paid. Delaying ₹5,000 collection changes closing cash without changing the exercise’s earned-revenue assumption. A complete real model may require additional accounting treatment and statements beyond this simplified learning example.
Can I call the twenty-attendee threshold a guarantee?
No. It is the break-even arithmetic under the stated price and cost assumptions. Real costs may change with volume, capacity may constrain delivery, and other expenses are excluded. Explain the conditions beside the result. A threshold helps explore a model; it does not establish that the business will attract that attendance.
How many scenarios should I build?
Start with a base case and a few meaningful changes tied to uncertainty, such as lower attendance or slower collections. Explain why each scenario matters. A large collection of arbitrary percentage changes adds little insight. Keep the model easy to compare so the reader can see which assumption causes which result.
Should I forecast every month with steady growth?
Only if a stated assumption and evidence justify that pattern. For practice, you can test a growth scenario, but label it invented and compare it with alternatives. Do not smooth the chart merely to make the business look attractive. Seasonal demand, capacity, and timing may matter more than a neat upward line.
How do I check a financial model?
Recalculate a small case independently, test zero and low-volume inputs, inspect copied formulas, and reconcile cash opening and closing balances. Ask another person to change an assumption and trace the effect. Checks should target plausible errors, such as hard-coded outputs or incomplete ranges, rather than simply repeating the same formula in another cell.
What belongs in the portfolio handoff?
Include the workbook, assumptions log, scenario definitions, expected numbers, and scope limitations. State that the business and prices are fictional. Explain the difference between operating results and cash timing. A reviewer should be able to change an input and understand both the new result and why it is not a real-world guarantee.
Continue your learning
Spreadsheet Skills Roadmap: Clean, Analyse and Present a Small Dataset. Bookkeeping Skills Roadmap: Record and Reconcile a Fictional Business Month.
Sources and further reading
- ICAEW: spreadsheet principles — Spreadsheet design, control and review practices.
- SEC: financial statements — Different roles of income and cash-flow statements.
- OpenStax: accounting concepts — Accounting assumptions and statement relationships.
Skills & Learning Desk
MP Job Hub’s organisational byline for sequenced practice, course evaluation and useful learning habits.
Skill roadmaps · Learning resources · Practice projects
View profile →Read our research process or request a correction.
