Financial Modeling in Excel Building an Integrated Financial Statements Model
Building an Integrated Financial Statements Model
statements consist of three reports:
Some financial models might include only a profit-and-loss statement, some might include simply a cash flow statement, but many include all three. If a financial model does contain all three, you might hear it referred as a three-way financial model.
What you really need to know about financial statements from a financial model- ing perspective is that they should hang together and interrelate with each other with links. This is called an integrated financial statement.
Every output in a financial model is driven by inputs, and financial statements are no exception. So, it’s very important that when you change one of the inputs or assumptions in your model, the outputs change, too. For example, if you’ve fin- ished building a financial model and your balance sheet balances, if you change one of the inputs (sales price, for example) the balance sheet should still balance. Of course, you’ll know right away if it doesn’t balance, because you’ve built in error checks (see Chapter 6).
I’ve seen many examples of financial statements that do not link to each other. This is simply poor modeling practice because if one of the inputs changes, all the financial statements will be out of sync with each other.
The best way for me to explain how financial statements link and fit together is to build one, of course, but before we do that, if you’d like a detailed description of each of the three statements and how each of them can help you to build, manage, and understand a business, turn to the sections later in this chapter: “Building the Income Statement,” “Building the Cash Flow Statement,” and “Building the Balance Sheet.”
Getting to Know the Case Study
In this case study, you’re opening a small but what you hope to be busy cafe and applying for a business loan. The bank has asked you to put together a three-way financial model forecasting the business’s income, balance sheet, and cash flow. You’ll use all the tools and techniques covered in this book to build this financial model.
The bank has provided you with a general model template, but you have to create the model and project how your business will do in its first year. If you follow the steps as shown in this chapter, you’ll end up with a fully integrated financial model. You can download the blank model template called File 1001.xlsx at www.
dummies.com/go/financialmodelinginexcelfd.
Start by opening the file and selecting the Balance Sheet tab.
In order to open and operate the cafe, you expect the business will need a $10,000 coffee machine, $35,000 in furniture and fixtures, $5,000 in miscellaneous inven- tory (such as cups and coffee), and $5,000 in cash. The total amount of $55,000 is how much money you need to open the business, also called your uses of funds.
Enter these numbers into your model, as shown in Figure 10-1.
FIGURE 10-1:
Sources and uses
of funds.
Now that you’ve determined how much money your business needs in order to start, you have to determine how you’ll fund this amount. This is called your sources of funds. Basically, you need to explain what you’re planning to do with the money, and where you’re planning to get it. In order to raise the required $55,000, you invest $25,000 in the form of equity (thanks, Ma and Pa) and borrow $30,000 as a bank loan.
Your total sources of funds and uses of funds must always be equal. In other words, you have to raise enough money to fund what you purchase for the business.
Entering Assumptions
Now that you’ve figured out how to fund your business, you need to make future assumptions and project how the business will perform. You need to build a sepa- rate Assumptions worksheet with your business projections that will drive the rest of the financial model.
The Assumptions worksheet already has places for your key business drivers — it’s up to you to input accurate and reasonable assumptions for the business.
In modeling, all input variables should be formatted consistently (usually with blue font or using Input Style, which can be found on the Home tab in the Styles section of the Ribbon). Then anyone using this model knows that she can make changes to any of the cells formatted in that way.
Based on your study of other cafes in the area (you’ve been drinking a lot of coffee the past few weeks, haven’t you?), you expect the following assumptions for your business’s revenue:
» You’ll sell an average of 120 cups of coff per day throughout the year.
» Forty percent of coff sold will be in large cups; 60 percent will be in small
cups.
» You’ll charge $4 for a large cup of coff and $3.50 for a small cup of coff
These are your expectations for the business’s sales; they represent your base case revenue assumptions. You aren’t really sure whether you have the daily number of sales right — you’re just estimating — so you’ll adjust this number when you run the scenarios. You’ll address the best- and worst-case assumptions later on, after the base case is complete, so you can leave the cells in rows 3 and 4 blank for now.
Go to the Assumptions page, and enter the business sales, as shown in Figure 10-2.
FIGURE 10-2:
Sales
assumptions.
The model has already been formatted for you, but when you make your own mod- els, be careful to format correctly, expressing percentage values as percentages and using dollar signs to show dollar values. One of the most common errors in finan- cial modeling is confusing units, such as treating a number of units as a price.
Proper formatting makes any report or model clearer and easier to read for the user. Pay particular attention to formatting when using percentages. If a cell is formatted as a percentage, any figure entered will automatically be converted to a fraction. For example, if you enter the number 5 in any normal cell, the value is 5. But if the cell is formatted as a percentage, the number 5 will automatically be converted to a value of 0.05 (or 5 percent). This could potentially lead to incorrect calculations.
In your analysis, you’ve also researched the operating costs of running a cafe, which are the following:
» You think the rent expense will most likely be $1,200 per month. This is just an estimate, though — you’ll enter some potential fl into the scenario analysis later on.
» Consumables — including coff beans, cups, fi and so on — will cost you
$0.45 per cup. This amount has been averaged over both large and small cups,
so you won’t need to distinguish between size for the purpose of this model.
» The barista’s salary is $50,000 per year, plus 25% in other staff costs and benefi
» Monthly utilities, such as electricity, heat, and water, will cost $100 per month.
» The company income tax rate is 30 percent.
These are your expectations for the business’s costs; they represent your base case expense assumptions.
Scroll down to the Expense Assumptions section on the Assumptions worksheet, and enter the assumptions as shown in Figure 10-3.
Finally, you also have some other assumptions regarding the number of business days you’ll be open per month and how busy your cafe will be throughout the year, so you need to apply some seasonality because this will affect your cash flow. Fol- low these steps:
- Scroll down to the Other Assumptions section on the Assumptions worksheet, and based on next year’s calendar, complete row 32 for the number of business days per month, as shown in Figure 10-4.
- Based on historical seasonal and weather patterns, complete row 33 for the seasonality variance, as shown in Figure 10-4.
FIGURE 10-3:
Expense assumptions.
FIGURE 10-4:
Other
assumptions.
Models must be well documented and assumptions must be clearly stated. A prop- erly documented model will not only help you and others follow it, but also help users if you’re no longer around when a change needs to be made to the model. Including sources is handy for when you or someone else wants to go back and
check the validity of your assumptions. Document assumptions and sources as you build the model. Documenting as you go is much easier than having to go back and do it at the end. After all, a model is only as good as its assumptions!
