Entering Assumptions in Your Financial Model - dummies

# Entering Assumptions in Your Financial Model

Now that you’ve figured out how to fund your business, you need to make future assumptions in your financial model and project how the business will perform. You need to build a separate 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.

## 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.

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.

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.