How to Create Projections for a Multi-Unit Coffee Shop Business or Coffee Shop Franchise.

Our single unit coffee shop financial plan excel template is our best-selling template. That’s no surprise to us! Coffee shops have some of the lowest barriers to entry of any of any brick-and-mortar businesses; with low cost of goods sold, widely available inputs, and cultural relevance, coffee shops are a solid bet for any entrepreneur hoping to gamble on a new business.

For the mature entrepreneur, or one hoping to earn more than just a living wage from a labor of love, the idea of a multi-unit coffee shop empire is appealing. Whether by investing in a pre-existing coffee-shop franchise or by starting an entirely new brand, an entrepreneur can multiply their earnings by using the first store’s earning to pay for the start-up costs of another.

We’ve created a new Multi-Unit Coffee Shop Template with this entrepreneur in mind. In this blog post, we want to walk you through the template and offer tips and tricks to getting accurate, helpful financial projections, whether you need them for a bank, a potential partner or investor, or just for your own planning.

Step 1. Purchase and Download the Template.

You can purchase and download the template here. If you’re not ready to purchase yet, you can view a sample version of the file here. Even if you don’t choose to use our template, this guide should help you put together your own projections.

Step 2. Fill Out the General Assumptions.

When putting together financial projections, you have to start with the basics. You’ll want to gather some information and answer a few questions:

What will you name your business?
When do you plan to start operating?
Will you need funds from an investor or a loan from a bank?

If you’re using our template, you can input this information on the “Input- General Assumptions” tab. Helpful Hint: if you’re not sure how much money you need from loans or investors yet, continue to put together your projections. At the end, predicted cash flows will show you if you’ve estimated your starting cash balance correctly or need to adjust.

Step 3. Predict Your Corporate Financials

If you want to start a coffee shop chain, you will eventually have some shared expenses that aren’t specific to any one unit. You might outsource bookkeeping to a bookkeeper who will consolidate financials for every unit, and you might invest in some internet or local advertising that could benefit many shops at once.

In addition to those shared, eventual expenses, you might pay for some items up front that could benefit the entire corporation and not just one unit. In our template, we consider three start-up expenses related to the corporate entity: legal, consulting, and graphic design. If you need images for your logo, you can easily find them on Freepik, a free stock images website. This is not an exhaustive list, but it’s important to consider that once you’re a corporation, you won’t have to re-incorporate for each new unit. In the same way, once you have a logo, you can use it for every shop.

In our template, you can fill out the expenses as guided on the General Assumptions tab. To separate start-up expenses, we allow you to individually input expenses for the first three months, and then set them each year to save you from re-entering every expense for 60 months. Helpful Hint: remember to enter monthly, not annual, amounts across the entire table.

Spending on legal, consulting, and graphic design is booked to the income statement as expenses, but some types of spending are actually purchases of assets. If you purchase a warehouse, that asset will benefit you for several years, so it is initially booked to the income statement as an asset and slowly expensed through depreciation on the income statement. Corporate assets are assets that don’t benefit any one unit specifically. A few examples might be a warehouse or a coffee roaster that is used to roast coffee for multiple units (even if it’s located in one unit specifically).

For assets, fill out the assets table on the General Assumptions tab. Even if you don’t plan to purchase assets right away, keep in mind after a few years you may find a need for a corporate office or warehouse. You can change the month the asset is purchased to show assets purchased later in the projection period. Helpful Hint: the life expectancy and salvage cost ae used to calculate depreciation expense.

In some cases, your corporate entity may earn some revenue independent of any physical shop. Perhaps you sell merchandise or bagged coffee online or license your brand or logo to another entity. You can enter corporate income on the “Input – Revenue” tab of the template, right at the top under “Corporate Sales”. Corporate income follows a simple growth revenue model as it likely will be a very small percentage of your total income.

On the “Input- General” assumptions tab you can adjust the accounts receivable and inventory assumptions for the corporate entity. AR and inventory are balance sheet asset accounts related to revenue.

If your corporate entity earns revenue, you will likely have direct expenses associated with the earning of that revenue. If you sell merchandise or coffee beans, the cost of the t-shirts, coffee cups, and coffee beans is cost of goods sold, or COGS. The best way to calculate COGS is to predict COGS as a percentage of revenue for your model, and then calculate COGS each month by taking monthly revenue times that percentage.

You can calculate your average COGS % by dividing the cost (to you) of any goods sold by their sales price. For example, if your supplier charges you $7.50 for a coffee cup with your logo, and you plan to sell it for $15, your COGS % is 50%.

On our template, you can enter COGS % on the “Input – Direct Expenses” tab and the dollar value of corporate COGS will be calculated for you each month on the financial statements. Our template also allows you to enter a small monthly change rate. This change rate can be used to show how COGS decreases over time. For many businesses, COGS will decrease over time due to economies of scale or more efficient use of resources. For example, the company putting logos on your coffee cup merchandise might give you discounts for larger orders.

Step 4. Predict Your Store Openings.

Once you have corporate items squared away, it’s time to turn your attention to the actual coffee shops. In our template, we allow you to create three base models for shops. We call these “Average”, “High-Performing” and “Low-Performing”. Keep in mind, the titles for these units aren’t passing any judgement on the units themselves. A low-performing unit could simply have fewer square feet, or perhaps it could be in a strategic area but have limited hours. In our template, you can select the start month and type for up to five units.

The template produces projections for 5 years. Five shops in 5 years is an impressive pace, and if you plan to grow a little slower you can delete the starting dates and model types from the last few units and they will be left out of the projections.

Helpful Hint: If you’re not settled on dates for future shop openings yet, you can always adjust dates when you see the final financial statements. You may choose to start shops when you hit a certain dollar value of cash in the bank, which can be determined on the cash flows statement.

Step 5. Create Some Unit-Specific Assumptions.

Once you’ve settled on start dates, you can begin to build the models for the three different types of units. Each unit will have its own assumptions related to asset purchases and new loans. In our model, you can enter these assumptions on the “Input – Unit Specific Assumptions” tab.

Unit-specific assets are assets that will serve only one unit. Examples of assets needed for a coffee shop might include:

Real Estate Improvements (renovations to the building)
Building (if you plan to purchase a space rather than rent)
Furniture and fixtures
Glassware, silverware, and dishes
Espresso / Cappuccino machines
Signage

Assets and asset cost may be the exact same for all three types of units, or they may differ. If you’ve decided your “High-Performing” units will be high-performing because they will be larger, you may want to consider increasing the cost of furniture and fixtures, real estate improvements, etc. Helpful Hint: There are a few assets pre-filled in out template, but you can choose to keep, delete, or change that data as you see fit. The pre-filled data is for example purposes. For more information on the equipment you need to start a coffee shop, check out this article.

When building individual unit models, you should also consider loans. Do you plan to take out a new loan to open each unit, or do you hope to generate enough cash from your other units to open a new unit without debt? If you plan to purchase your buildings, you may plan to have a mortgage on each unit.

In our template, you can enter unit-specific loans on the Unit Specific Assumptions tab. The loan amounts and terms (or whether you take out a loan at all) can be different for the three types of units. The model will automatically add the corresponding loan to the balance sheet each time you open new unit, if that unit type has a loan associated with it.

Just like the corporate entity, your individual units can carry inventory or have accounts receivable. These items are usually calculated as a percentage of revenue. In our model, AR is calculated as percentage of the current month’s sales, and inventory is predicted based on a percentage of the sales in the next month of the model. The same percentages are used for all units, but AR and inventory balances will differ as revenue will be different for the three types of units.

Step 6. Revenue Models

Once you’ve set all your basic assumptions, it’s time to move onto the most important part of any financial projection: the revenue model.

In our template, the revenue model is on the “Input – Revenue” tab. Our template begins with the revenue model for the average units, and then uses that model to predict revenue for high-performing and low-performing units.

Customer Visits per Month

The first step in our revenue model to determine the number of visitors that will come to the coffee shop in any given month. Visitors (users) are acquired through paid advertising, or organically through word of mouth or internet searches. In our model, the light blue inputs can be changed to reflect your business model. Helpful Hint: Advertising spending is pulled from the “Input – Operating Expenses” tab and can be changed on that tab.

The model calculates new visitors from advertising based on the cost to acquire a new visitor. This cost can vary depending on the type of advertising you do and its effectiveness, but for coffee shops we typically see a low cost to acquire a new visitor (between $10-$20).

The model then adds in new visitors from organic sources, and then calculates how many new visitors are likely to become regulars. If your coffee shops are going to be on college campuses or in small towns with few other “third place” type establishments, you might see a higher percentage of first-time visitors convert to regulars than coffee shops in a touristy areas or busy shopping centers. Some regular customers will inevitably leave each month, and we calculate this with a churn rate. A typical churn rate is for a coffee shop is around 10%.

Once the monthly customer base has been calculated, we need to consider how many customer visits occur each month, and what each customer is likely to spend per visit.

In our model, we assume that each regular will visit on average 3.1 times in the first month. This could change significantly depending on the demographics of your regulars! Some coffee shops may have only a few regulars, but they might visit several times a week, or even every day. In addition to visits from regulars, we also must factor our new visitors. The model builds in one visit for every new visitor. Some new visitors will become regulars during the month and will therefore be considered to visit as a new visitor and a regular during the month.

The model will automatically calculate customer visits throughout the month. Use this as an opportunity to gut-check your model so far. If you’re planning for 10,000 customer visits per month but are located on a college campus and plan to have outlets at all 20 of your tables, you might be overshooting how many customers you can actually serve. Are you in a place where people will stay a while, or do you expect the morning rush of commuters to be heavy? Divide your monthly visits by 30 to see how many people you’ll have to serve per day, and again by your hours to see what your hourly turnover will be.

For example: Using the numbers pre-filled in our model, we top out at about 26,000 monthly customer visits in year 5. Assuming we’re open on average 15 hours per day, that’s about 870 visits per day, or 58 visits per hour. Is it likely that this coffee shop can serve about one person per minute? Unless we have three or four cash registers and three or four cappuccino machines, perhaps we should decrease our visitor to regular conversion rate to 5%, or increase the cost to acquire a new visitor over time so that our hourly visits in year 5 are more reasonable.

Menu & Customer Spending

Once you’re settled on your customer visits per month, it’s time to set the menu and determine the average spend per customer.

Our template allows you to enter allows you to enter up to 10 menu items, but if your menu is larger, don’t worry. One of the keys to creating good financial projections is learning how to summarize with averages. We can’t really predict the future, so it’s ok to group items or make some generalizations in order to get the big picture down in a model. Too many details can make for inaccurate or unhelpful projections.

In the menu box, we recommend summarizing by general item type. Some possible categories:

Regular Coffee or Tea
Espresso Drinks
Frozen Drinks
Specialty Non-Coffee Drinks
Pastry Case Snacks
Bagged Snacks
Hot Sandwiches
Etc.

For each category, assign an average price. Then, estimate the number of times transaction with a single customer each item is purchased. Keep in mind, more than one customer could be included on the same bill, but we count them individually for revenue. Helpful Hint: most items will be purchased less than one time per customer per transaction. To gut-check your menu predictions, add up the numbers in the far-right column. In our model, the far-right column sums to 1.5, which means each customer on average purchases 1.5 items per transaction, which seems reasonable.

Directly below the menu table we calculate the average spend per visitor per visit, which is calculated based on data in the menu table.

Once we’ve calculated the average spend per visitor per visit, we can multiply by number of customer visits to get coffee and food revenue. Our template also offers a mini revenue model for merchandise sales.

Predicting High- and Low-Performing Unit Revenue

Now that we’ve predicted revenue for the average unit, it’s time to predict revenue for the high-performing and low-performing units. The best way to predict these values is to simply multiply the average unit revenue by a percentage over 100% for the high-performing unit, and below 100% for the low performing unit. In the image below, we predicted high-performing units will have 110% of the revenue of an average unit, and low-performing units will have 90% of the revenue of an average unit.

Step 7. Predict Direct Expenses

Coffee shops have two types of direct expenses: materials (generally coffee beans, milk, syrups and flavors, water, sweeteners, and food) and labor (the baristas who take orders and make coffee).

Direct materials for coffee shops is easy to predict with a little research. Commonly, direct materials cost about 25% of revenue, but you can make your own predictions based on your knowledge of your suppliers and prices. We recommend this blog post for a more in-depth discussion of direct materials.

Direct labor is a little harder to predict. In our model, we consider a few factors when predicting direct labor costs, as you can see in the image below:

Note that in our model, we also include a manager or two for the stores (“Input Salaries & Dividends” tab), so at any given time there will be three or more employees in the coffee shop. Our model pulls data from the input revenue tab to calculate transactions per hour and then predict the average number of employees needed per hour per month. Helpful Hint: transactions per hour is the number of transactions per month (total customer visits during the month) divided by open hours per month.

The three types of store differ in their number of employees per hour because they all handle a different number of transactions per month. In our model, the high-performing store has 10% more transactions per month and the low-performing store has 10% less than the average store.

Once we predict employees per hour, we just have to multiply by the number of hours per month and the rate per hour to calculate direct labor cost for each store.

Step 8. Other Expenses

In addition to direct expenses, your coffee shops will have indirect operating expenses, or overhead. Some examples of overhead expenses include:

Advertising
Computer Services / POS System
Insurance
Rent
Supplies
Utilities / Internet
Legal, Licenses, and other Start-Up Costs

Some of these expenses will occur every month, but others are start-up costs that you only pay once. Helpful Hint: be careful not to include assets in your start-up costs. Assets are items that will provide you value over time, whether tangible or not. A good example of an intangible asset is a 10-year business license or a security deposit on your rental.

On our model, you should input expenses in monthly amounts. We’ve broken out the first three months individually to help you account for start-up costs that don’t occur every month. The remainder of the months in the model are grouped by years, and the expenses will be the same every month for an entire year.

Like the revenue models, here we have you set expenses for the average unit and then predict how the high- and low-performing units will differ for each expense.

Step 9. Salaries

The last piece of our individual unit predictions is salaries. Direct labor has already been calculated, so the salaries we calculate here are only for indirect employees like managers, bookkeepers, or CEOs. When calculating salaries, be sure to include benefits and employer taxes in your budget. In our model, we allow a few different ways to customize salaries to the three types of units and the corporate entity.

The first way to customize salaries units is to change the number of employees for the unit. In the above example, the first line represents managers at both the average and high-performing unit. Mangers at both types of stores get paid $45,000 per year, but for the average store there is only one manager, whereas the high-performing store supports two.

The next line down represents the manager at the low-performing store, who earns $35,000 annually. Note there are “0s” in the columns for the average and high-performing units and the corporate entity, as this type of manager only exists at the low-performing store.

In the same way, the CEO and bookkeeper are employees of the corporate unit and their salaries will be represented on the corporate income statement.

Step 10. Dividends

If your coffee shop is a corporation, you may pay your shareholders (or yourself, if you are a shareholder) dividends. There are tax implications of paying dividends, and we recommend you discuss these with a tax accountant in your area. Dividend payments can act like a type of salary or loan payment to various shareholders. If you choose to pay dividends, you can enter them on the “Input- Salaries & Dividends” tab. Paying dividends reduces the cash balance of the business but is not considered an expense on the income statement.

Step 11. Putting it all together

Once we’ve predicted all the inputs of our financial model, it’s time to put it all together. Here are the steps we followed to put together our template:

Create the month-to-month financial statements for each type of unit plus the corporate entity.
Layer the month-to-month financials for each unit together based on unit start dates and types of units.

We chose to show the financial impact of each unit on our consolidated financials, except for a few key accounts: