Module 1: Modeling Foundations

What Makes a Good Model

A financial model is a structured spreadsheet that turns assumptions about a business into numbers you can act on: forecast profit, cash needs, loan capacity or the value of a company. Many spreadsheets look impressive and still fail, because nobody can follow them, they break when an assumption changes, or they answer the wrong question. Before you build anything, you need a clear picture of what "good" looks like, so that every later skill in this course has a target.

Start with the question the model must answer

A good model is built for a decision. "Should we open a second branch in Kisumu?", "How much working capital loan do we need next year?" and "What is this business worth to a buyer?" are three very different questions, and each needs a different level of detail. Write the decision at the top of your planning notes before you open Excel. Then list the outputs that decision needs, such as monthly cash balance, payback period or net present value. Everything in the model should exist to produce those outputs. If a calculation does not feed an output, question why it is there.

Decide the time frame and the time step at the same stage. A cash planning model for a retailer may need monthly or even weekly columns for 12 to 18 months. A valuation model usually needs annual columns for five to ten years. Mixing these needs in one sheet is a common source of confusion.

The five qualities of a good model

• Accurate: the arithmetic is right, the accounting logic holds (for example, the balance sheet balances), and the units are consistent.
• Transparent: another person can trace any number back to its inputs within a few minutes. Formulas are short, labels are clear, and assumptions are written down.
• Flexible: when an assumption changes, the whole model updates correctly. You never have to retype numbers deep inside a calculation.
• Appropriately simple: it has as much detail as the decision needs and no more. Extra detail adds error risk and makes review slower.
• Robust: it includes checks that warn you when something has gone wrong, such as a balance sheet that does not balance or a cash balance that turns negative.

A worked example: two ways to model the same shop

Imagine Wanjiru runs a hardware shop in Thika and wants to know whether she can afford a KES 1,500,000 delivery van next year. Her nephew builds a model where sales growth of 12% is typed directly into thirty different formulas, costs are hard-coded totals, and the cash figure is calculated on a separate sheet with no link to the profit figures. When the bank asks "what if growth is only 6%?", he has to edit thirty cells and hope he finds them all.

A better version has one input cell labelled "Annual sales growth" holding 12%. Monthly sales formulas refer to that cell. Cost of goods sold is calculated as a percentage of sales, also from an input cell. Operating costs, the van purchase and loan repayments are all listed as inputs with their timing. A cash flow block pulls from the profit calculations, and a summary at the top shows the lowest cash balance in the year. Changing growth to 6% takes one keystroke, and Wanjiru instantly sees whether her cash stays above her safety buffer. Both models might show the same answer at 12%, but only the second one is useful for the decision.

Who uses the model, and how

Think about the reader. A business owner wants a clear summary and the ability to test a few assumptions. A lender wants to see repayment capacity and the assumptions behind it. An investor wants to see growth, margins and valuation, and will try to break your assumptions. Design the output pages for the reader, and keep the detailed workings available for anyone who wants to check them.

Also think about the life of the model. A one-off model for a single decision can be lighter. A model used every month for budgeting needs stronger structure, version control and documentation because several people will touch it over time.

Common mistakes to avoid

• Starting to type numbers before deciding what question the model answers.
• Hard-coding assumptions inside formulas, such as =B5*1.12, instead of referring to a labelled input cell.
• Building far more detail than the decision needs, for example forecasting every product line when the decision only needs total sales.
• Having no checks, so errors go unnoticed until someone outside the business finds them.
• Treating the model's output as a fact. A model is only as good as its assumptions, and you should always state them.

Keep these qualities in mind as you move through the course. In the next lessons you will learn the layout standards and structure that make accuracy, transparency and flexibility easier to achieve.