Yonemitsu offers these six steps for creating and using financial models.
1. Create financial projections
The first step to doing any financial modelling is to be sure you have an updated set of financial projections for your business. These are estimates of your future sales, expenses, financial obligations and commitments. Using spreadsheets makes it easy to do sensitivity analysis, which involves adjusting variables for different potential scenarios (more on this below). Projections are usually made for a company’s income statement, balance sheet and working capital for a coming period—for example, the next 12months.
Many entrepreneurs neglect to create or update their financial projections, but these can be essential to helping you manage your company’s finances. This is especially so if your business has uneven cash flow from month to month due to seasonal fluctuations, lumpy receipts or rapid growth.
2. Develop appropriate models
You can now use the projections to create financial models. The idea is to change inputs in your projections to see the impacts of a business decision. Modelling is useful for:
- Big deals
- New products
- Acquisitions
- Expansions
- An efficiency project or changes in operational processes
- Investments in new machinery, technology or hiring
- Raising funds
- Economic or seasonal slowdowns
It’s good practice to link the data in all your projections in one connected spreadsheet. This is to ensure data is consistent and automatically applied across your income statement, balance sheet and working capital documents. It’s also useful to break out your assumptions onto a separate worksheet so you can readily see and vary them. You can use tables and charts to summarize results and visualize them more easily.
3. Use models to allocate funds
Use modelling to weigh the impacts of a decision on working capital, revenue, financing needs, profitability and valuation. You can also compare the returns and payback periods of potential investments. “You don’t want to go in blind,” Yonemitsusays. “The best surprise is no surprise.”
Modelling often leads to eye-opening insights. Yonemitsu gives the example of a company that prepared 10-year models for three potential growth projects—a new product and two different expansion possibilities. Modelling showed a clear winner. The two expansions would have likely led to good returns, but the new product would have yielded still better results, with less investment and risk and a superior payback period.
Models can also help a business allocate scarce resources in times of uncertainty or financial stress. Yonemitsu was asked to advise a company experiencing cash flow problems and lacking money to cover payroll and pay suppliers. A cash budget, updated weekly, was critical for helping the CEO plan the timing and amounts of cash outlays to turn the business around.
Modelling the impacts of a new deal can also help you avoid sudden cash shortfalls. “You may have to pay workers and buy raw materials, but you might not get payment until six months after your initial outlays,” Yonemitsusays.
“Modelling will show where your company will be stressed, how much financing you’ll need and the impacts on your gross margin.”
4. Do sensitivity analysis
Assumptions about the future are often wildly wrong. This is why it’s a good idea for your models to include sensitivity analysis—a variety of scenarios reflecting different possible outcomes. For example, you could include optimistic, pessimistic and most likely scenarios for your project.
This can be done by changing key inputs such as revenues, variable and fixed expenses, marketing, inventory, the number of employees, sales per customer and accounts receivable and payable days.
5. Hire the right help
You may need to bring in special expertise to help you create models. Your bookkeeper may not have the knowledge, especially as your business grows. “The complexity goes up immensely as your company gets bigger, and the stakes are higher,” Yonemitsusays.
6. Update regularly
Modelling shouldn’t be a once-a-year exercise, or done once and forgotten. You should update a model if conditions change and input actual figures as you go, in order to see the impacts on projections. And modelling is useful any time a new decision or project is being considered. “You want to model the future all the time,” Yonemitsusays.
