ARTICLE
How much does it cost to open a coffee shop: a step-by-step breakdown of the financial model
For many, opening a coffee shop is almost as much of a business dream as owning a bar. There's a steady demand, relatively low startup costs, and the joy of creating a cozy space. When opening a coffee shop from scratch, it's important to develop offerings and services that cater to your target audience. The calculation of a coffee shop's business plan will vary for different formats. Let's break down step by step how to calculate the number of customers and revenue for each scenario, and learn to create a dynamic financial model in Excel / Google Sheets.
Like any foodservice, the location plays a crucial role in the commercial success of a coffee shop. Choosing a location with good foot traffic is a necessary minimum requirement for launching a coffee shop. The location should be able to provide you with potential customers to fill your coffee shop to capacity. The location will strongly influence the target audience, and more precisely, the chosen target audience will determine the optimal location for the coffee shop.
The target audience drives the business model of the coffee shop, determining the assortment, prices, and even the interior design features. Calculations will vary for a small takeaway coffee stand versus a coffee shop targeting to remote workers who may spend several hours with a cup of coffee and a laptop in your place.
Typically, coffee shops employ two sales approaches: selling coffee to go and serving customers in the shop. In this article, we will explore how to build a financial model for a coffee shop business plan, taking into account the characteristics of each of these revenue streams.
The target audience drives the business model of the coffee shop, determining the assortment, prices, and even the interior design features. Calculations will vary for a small takeaway coffee stand versus a coffee shop targeting to remote workers who may spend several hours with a cup of coffee and a laptop in your place.
Typically, coffee shops employ two sales approaches: selling coffee to go and serving customers in the shop. In this article, we will explore how to build a financial model for a coffee shop business plan, taking into account the characteristics of each of these revenue streams.
Financial model of a coffee shop for a business plan
The financial model in the business plan of a coffee shop is a key tool that allows to forecast revenues, expenses, profitability, and investment needs. It provides the foundation for making strategic decisions, attracting investors, and ensuring the sustainable financial success of the coffee shop. The financial model enables the selection of an optimal business model and pricing strategy for the coffee shop with a specific target audience, while also accounting for all expenses related to its opening and operation.
The basic financial model we construct for any new business is the "3-Statements Model," which involves forecasting movements in three main financial statements: Profit and Loss (P&L), Cash Flow, and Balance Sheet.
You can find a financial model at this link, examples of which we'll dissect in this article.
First and foremost, we tackle the calculation of profit and loss, starting with revenue forecasting. The algorithm of revenue generation is a key differentiator among various business models. Therefore, in this article, we will thoroughly examine the calculation of the dynamics of customer numbers and revenue for the two main revenue streams of the coffee shop - selling takeaway coffee and serving customers in the shop. We will delve into the variables that contribute to the coffee shop's revenue and the expenses associated with them.
Our coffee shop financial model template involves breaking down calculations into several interconnected sheets in Google Sheets or Excel for ease of model management and assessment of different scenarios. We input all initial data onto the first sheet, use them to generate a detailed monthly model, then create consolidated financial statements (profit and loss, cash flow, balance), and summarize the results by calculating the breakeven point, determining the required investment amount, and visualizing key metrics on graphs.
The basic financial model we construct for any new business is the "3-Statements Model," which involves forecasting movements in three main financial statements: Profit and Loss (P&L), Cash Flow, and Balance Sheet.
You can find a financial model at this link, examples of which we'll dissect in this article.
First and foremost, we tackle the calculation of profit and loss, starting with revenue forecasting. The algorithm of revenue generation is a key differentiator among various business models. Therefore, in this article, we will thoroughly examine the calculation of the dynamics of customer numbers and revenue for the two main revenue streams of the coffee shop - selling takeaway coffee and serving customers in the shop. We will delve into the variables that contribute to the coffee shop's revenue and the expenses associated with them.
Our coffee shop financial model template involves breaking down calculations into several interconnected sheets in Google Sheets or Excel for ease of model management and assessment of different scenarios. We input all initial data onto the first sheet, use them to generate a detailed monthly model, then create consolidated financial statements (profit and loss, cash flow, balance), and summarize the results by calculating the breakeven point, determining the required investment amount, and visualizing key metrics on graphs.
The maximum throughput capacity of a coffee shop
A coffee shop is a traditional business where the number of customers is limited by production capacity. In this case, "production capacity" is defined by how many customers we can serve. This depends on factors such as the size of the premises, the time it takes to prepare orders, the number of staff, and the amount of time customers typically spend in the coffee shop at a table.
Based on this, we specify the key parameters of the coffee shop location in our input data tab: the number of seats and operating hours. The number of tables and seating will be determined by the size of the rented space. Plan the number of seats in your coffee shop and input them into the financial model.
We immediately divide the operating hours into weekdays and weekends since occupancy can vary significantly depending on the location and audience. If you're planning to open a coffee shop in a business center, the weekend traffic might be minimal. However, for a family-oriented coffee shop catering to couples with children, daytime weekends could be peak hours. Determine the optimal operating hours for weekdays and weekends and input them into the model.
Based on this, we specify the key parameters of the coffee shop location in our input data tab: the number of seats and operating hours. The number of tables and seating will be determined by the size of the rented space. Plan the number of seats in your coffee shop and input them into the financial model.
We immediately divide the operating hours into weekdays and weekends since occupancy can vary significantly depending on the location and audience. If you're planning to open a coffee shop in a business center, the weekend traffic might be minimal. However, for a family-oriented coffee shop catering to couples with children, daytime weekends could be peak hours. Determine the optimal operating hours for weekdays and weekends and input them into the model.
Capacity of a coffee shop calculation (coffee shop financial model)
To understand how many customers your coffee shop could potentially accommodate, you need to forecast the time it takes to serve one customer. It's evident that the figures will differ for various purchasing models. For the "coffee to go" model, we consider the time for order taking, payment processing, and coffee preparation. In the case of the "seating in the shop" model, we need to consider the total service time from both perspectives.
We start with the customer's perspective and answer the question of how much time an average visitor will spend in the coffee shop. Now, you need to determine whether you can serve each customer within that time frame with a fully loaded coffee shop and existing resources. If not, you either need to increase the number of staff and other resources affecting service time or extend the time per customer.
Capacity of a coffee shop calculation (coffee shop financial model)
Calculations of Coffee Shop Revenue
At this stage, it's necessary to determine the assortment and prices. For the purposes of the financial model, you can group items into categories and assign average figures. For a more detailed analysis, you can break down categories and specify prices for each item.
In our example, we plan to open a coffee shop without a kitchen. The assortment will consist of three categories: coffee, snacks and ready-to-eat food, and bottled beverages. Now, calculate the average cost for each category and set retail prices.
To determine the cost in the coffee category, calculate how much the initial ingredients for making an average cup of coffee will cost us: beans, milk, sugar, etc. For the "coffee to go" model, separately allocate the cost of disposable cups and other packaging materials. In this example, we assume that reusable dishes are used for in-store service. If you plan to use disposable cups for all sales options, you can immediately add their cost to the cost price.
In addition to coffee, in our example, we plan to sell snacks and possibly ready-to-eat food and baked goods, as well as bottled refreshing beverages. We purchase all these products ready for consumption, and the cost price is simply the purchase price. Most coffee shops do not have their own kitchen and offer a limited range of purchased pastries, sandwiches, and snacks. The same applies to Starbucks, which uses this model.
In our example, we plan to open a coffee shop without a kitchen. The assortment will consist of three categories: coffee, snacks and ready-to-eat food, and bottled beverages. Now, calculate the average cost for each category and set retail prices.
To determine the cost in the coffee category, calculate how much the initial ingredients for making an average cup of coffee will cost us: beans, milk, sugar, etc. For the "coffee to go" model, separately allocate the cost of disposable cups and other packaging materials. In this example, we assume that reusable dishes are used for in-store service. If you plan to use disposable cups for all sales options, you can immediately add their cost to the cost price.
In addition to coffee, in our example, we plan to sell snacks and possibly ready-to-eat food and baked goods, as well as bottled refreshing beverages. We purchase all these products ready for consumption, and the cost price is simply the purchase price. Most coffee shops do not have their own kitchen and offer a limited range of purchased pastries, sandwiches, and snacks. The same applies to Starbucks, which uses this model.
Coffee shop pricing (Coffee shop financial model)
For an accurate revenue forecast for each stream, we need to consider that the coffee shop won't be fully occupied during all hours and days of operation. Additionally, potential customers will need time to discover the new location and become regular visitors. Therefore, for each sales model, we assume an opening occupancy percentage, monthly growth, and maximum occupancy target.
We also need to differentiate between weekdays and weekends.
We also need to differentiate between weekdays and weekends.
Coffee shop occupancy calculation (Coffee shop financial model)
Moving on to calculating the average order value for the coffee shop. We input into the model a forecast of the number of items from each category in one order per customer. In our example, we assume that every visitor will purchase coffee, while only some customers will buy food and beverages. Based on these values and the prices we set in the previous section, we calculate the average check. As always, we input data separately for "Coffee to go" and "Seating in the shop" - the assortment for these two models can vary significantly.
Coffee shop average order value calculation (Coffee shop financial model)
We have entered all the input data for calculating the revenue of the coffee shop and are now moving on to the forecast. Our goal is to create a dynamic model that allows for calculations for different scenarios at the initial stage of the project. Therefore, we make the monthly forecast on a separate tab, setting all indicators here through formulas.
Determining the first month
To determine the month from which certain indicators should be calculated, we use the IF function.
If the date value for the column is greater than or equal to the launch date of the coffee shop, we start calculating the indicator using the formula; otherwise, the cell value is "0".
In general, such a formula in Excel or Google Sheets looks like this:
=IF(E2 (month in the forecast) >= Projections! $E$13 (launch date in the input data), formula, 0).
This approach allows the financial model to be more flexible when working with dates. If you shift the opening dates, you just need to change the date in one month, and the entire model will recalculate.
Calculating the number of weekdays and weekends
Since we consider the difference in the coffee shop's operation on weekdays and weekends in the financial model, it is necessary to calculate the number of each type of day in each month for calculations.
In Excel and Google Sheets, the NETWORKDAYS function is used for this. It calculates the number of working days in the month. To obtain the number of weekends, simply subtract this value from the total number of days in the month.
Calculating occupancy for each month
Based on the assumptions about the dynamics of the coffee shop's occupancy that we made in the input data, we calculate the occupancy for each month accordingly. We then proceed to calculate the number of customers. For this, we multiply the maximum possible occupancy by the occupancy percentage for that specific month.
Forecasting revenue
We multiply the number of customers by the average order value to obtain revenue. We perform all these calculations separately for the "Coffee to go" and "Seating in the shop" models, and then add the results for the total revenue amount.
Determining the first month
To determine the month from which certain indicators should be calculated, we use the IF function.
If the date value for the column is greater than or equal to the launch date of the coffee shop, we start calculating the indicator using the formula; otherwise, the cell value is "0".
In general, such a formula in Excel or Google Sheets looks like this:
=IF(E2 (month in the forecast) >= Projections! $E$13 (launch date in the input data), formula, 0).
This approach allows the financial model to be more flexible when working with dates. If you shift the opening dates, you just need to change the date in one month, and the entire model will recalculate.
Calculating the number of weekdays and weekends
Since we consider the difference in the coffee shop's operation on weekdays and weekends in the financial model, it is necessary to calculate the number of each type of day in each month for calculations.
In Excel and Google Sheets, the NETWORKDAYS function is used for this. It calculates the number of working days in the month. To obtain the number of weekends, simply subtract this value from the total number of days in the month.
Calculating occupancy for each month
Based on the assumptions about the dynamics of the coffee shop's occupancy that we made in the input data, we calculate the occupancy for each month accordingly. We then proceed to calculate the number of customers. For this, we multiply the maximum possible occupancy by the occupancy percentage for that specific month.
Forecasting revenue
We multiply the number of customers by the average order value to obtain revenue. We perform all these calculations separately for the "Coffee to go" and "Seating in the shop" models, and then add the results for the total revenue amount.
Coffee shop revenue forecasting (Coffee shop financial model)
Expenses for Opening and Operating a Coffee Shop
Income is not yet profit. To understand the profitability of such a business and how much it costs to open a coffee shop, let's move on to calculating and forecasting the cost side.
In this part, the calculations are simpler than when calculating income. However, we will go through all the expense categories of the coffee shop to ensure nothing is overlooked.
Capital expenditures
Let's start with the expenses for opening the coffee shop. After finding the location and negotiating the terms of the lease, you'll need to transform the space into a cozy and functional coffee shop. Prepare a budget for renovations, equipment purchases, furniture, tableware, and other necessary items, and input them into the input data tab of the financial model. Don't forget about depreciation schedules, as they are essential for subsequent profit and loss, cash flow, and balance sheet calculations.
In this part, the calculations are simpler than when calculating income. However, we will go through all the expense categories of the coffee shop to ensure nothing is overlooked.
Capital expenditures
Let's start with the expenses for opening the coffee shop. After finding the location and negotiating the terms of the lease, you'll need to transform the space into a cozy and functional coffee shop. Prepare a budget for renovations, equipment purchases, furniture, tableware, and other necessary items, and input them into the input data tab of the financial model. Don't forget about depreciation schedules, as they are essential for subsequent profit and loss, cash flow, and balance sheet calculations.
Coffee shop CAPEX (Coffee shop financial model)
Cost of goods sold
We've already entered the input data for calculating the cost of goods sold when planning the assortment and prices. In addition to expenses for purchasing goods and ingredients, we need to include costs for payment processing. We'll set it as a percentage of revenue.
We've already entered the input data for calculating the cost of goods sold when planning the assortment and prices. In addition to expenses for purchasing goods and ingredients, we need to include costs for payment processing. We'll set it as a percentage of revenue.
Employees
It's more convenient to allocate the payroll expenses to a separate tab. List all the employees necessary for the operation of the coffee shop, taking into account the need for both full-time and part-time staff. Plan for annual salary increases and don't forget about taxes and fees. You can also schedule the hiring dates for additional employees according to the forecasted growth in the coffee shop's workload.
It's more convenient to allocate the payroll expenses to a separate tab. List all the employees necessary for the operation of the coffee shop, taking into account the need for both full-time and part-time staff. Plan for annual salary increases and don't forget about taxes and fees. You can also schedule the hiring dates for additional employees according to the forecasted growth in the coffee shop's workload.
Coffee shop Payroll (Coffee shop financial model)
Operating Expenses
Next, we move on to the operating expenses of the coffee shop. Here, we need to consider expenses without which the coffee shop cannot operate. These include utilities and costs related to maintaining the premises - rent, cleaning, equipment maintenance.
Next, we move on to the operating expenses of the coffee shop. Here, we need to consider expenses without which the coffee shop cannot operate. These include utilities and costs related to maintaining the premises - rent, cleaning, equipment maintenance.
Coffee shop Operating expenses (Coffee shop financial model)
Marketing Expenses
Location plays a crucial role in attracting customers to the coffee shop, but marketing campaigns can significantly increase the flow. Allocate a budget for various promotion channels and don't forget about additional expenses such as design, copywriting, and printing of promotional materials.
Location plays a crucial role in attracting customers to the coffee shop, but marketing campaigns can significantly increase the flow. Allocate a budget for various promotion channels and don't forget about additional expenses such as design, copywriting, and printing of promotional materials.
Coffee shop Marketing expenses (Coffee shop financial model)
Administrative Expenses
Finally, we have administrative expenses, without which the operation of a legal entity is impossible - accounting, legal services, insurance, and so on. This section will also include rental expenses for the premises.
Finally, we have administrative expenses, without which the operation of a legal entity is impossible - accounting, legal services, insurance, and so on. This section will also include rental expenses for the premises.
Coffee shop Administrative expenses (Coffee shop financial model)
Now you have a tool with which you can assess how much it will cost to open your dream coffee shop and estimate the profit this business can bring. Choose benchmarks that are realistic for your case, taking into account the results of marketing research (it's important for the financial model to consider competitor analysis, market size estimation, and surveys of potential customers), and play with the numbers in the input data to achieve the optimal financial result. For convenience, you can add graphs and additional metrics.
Coffee shop Net income (Coffee shop financial model)
For more information on other aspects of financial models, you can refer to my other articles on the blog
A financial model template in Google Sheets or Excel, complete with all the formulas, can be obtained via the provided link.
A financial model template in Google Sheets or Excel, complete with all the formulas, can be obtained via the provided link.
You can purchase a financial model template for a specific business in the store or request the development of a custom financial model for your project.