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.
Revenue assumptions
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 coffee per day throughout the year.
- Forty percent of coffees sold will be in large cups; 60 percent will be in small cups.
- You’ll charge $4 for a large cup of coffee and $3.50 for a small cup of coffee.
Go to the Assumptions page, and enter the business sales.
The model has already been formatted for you, but when you make your own models, 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 financial 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.Expense assumptions
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 fluctuations into the scenario analysis later on.
- Consumables — including coffee beans, cups, filters, 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 benefits.
- Monthly utilities, such as electricity, heat, and water, will cost $100 per month.
- The company income tax rate is 30 percent.
Scroll down to the Expense Assumptions section on the Assumptions worksheet, and enter the assumptions.
Other assumptions
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. Follow 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.
- Based on historical seasonal and weather patterns, complete row 33 for the seasonality variance.
Models must be well documented and assumptions must be clearly stated. A properly 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!