Grocery Store Financial Model
This Grocery Store Financial Model in Excel will allow you to produce financial forecasts using alternative scenarios, and assessing financial risks.
Grocery stores, or groceries, being one of the oldest types of businesses, now became an umbrella term for different types of retail facilities, including supermarkets, convenience shops, greengrocers and ethnic markets. Whether you are going to operate a small neighborhood’s grocery shop, niche shop, or supermarket, you need a financial plan to be able to predict sales and costs, raise and properly allocate capital. Our Grocery Store Financial Model Excel Template will allow you to produce financial forecasts using alternative scenarios, and assessing financial risks and benefits.With it, you will be able to:
- Produce monthly and yearly financial projections for up to 10 years, using different assumptions;
- Account for long-term marketing trends, as well as seasonal fluctuations of sales and prices;
- Evaluate capital requirements and make rationing of capital, combining debt and equity in different proportions.
It is quite simple and intuitively understandable, even for newcomers. You can use it to produce nicely and professionally looking pro forma statements and charts to present to lenders or co-investors.
Now let us have a detailed look at the Grocery Store Financial Model template.
Grocery Store Financial Model Template Features:
This Excel template allows you to calculate widely used investment metrics such as payback period, discounted payback period, Net Present Value (NPV) and Internal Return Rate (IRR). It also takes into account the effect of taxation.
You also have to understand clearly how much of your own money you will be able to put into the business, along with the loans available to cover any deficit of capital.
DASHBOARD & SETTINGS
The Dashboard section contains these general settings and assumptions for your workbook:
- Model Config section;
- A table of goods’ categories;
- A table of long-term sales trends.
- Set a length of the Projection Period. You can set if 2 to 10 years, just pick a relevant value from the drop-down list;
- Enter the Start Date of the project;
- Enter a code of the currency which all monetary amounts will be displayed in;
- Discount Rate will be applied to the calculation of Net Present Value (NPV) of the project.
- Enter Income Tax Rate that will affect net profit after taxation.
You have to enter a list of categories and related assumptions. Assumptions here have the following meanings:
- Markup percentage shows how much you are going to add to the hard cost of goods in order to get regular selling price before discounts;
- Credit Period is the number of days showing how long it is going to take on average to pay for purchased goods;
- Days of Inventory is a number of days showing how fast each category of goods can be sold, in other words how many days of sales your inventory is going to cover;
- Finally, Shrinkage & Theft percentage shows how much of your inventory is going to be lost rather than sold.
A table of sales trends allows you to set long-term trends in order to account overall sales over the years. But, if you do not expect your sales changing from year to year, you can leave this table blank.
Dashboard also contains a navigation pane allowing you to access any part of the template.
REVENUE & COST MODEL INPUTS
1. Sales & Inventory
In the Sales & Inventory section, select respective goods categories in the Sales Forecast table. For each category, enter projected Average Daily Revenue. To account for seasonal changes, use Seasonal Sales Trend and Seasonal Discounts tables.
The Seasonal Sales Trend table allows you to enter, for each category, and each month of a year, percentages of average sales (in physical units).
If you do not expect changes in sales over a year, you can leave this blank.
Similarly, the Seasonal Discounts table allows you to account for seasonal fluctuations of prices separately from fluctuating of physical quantities. Leave it blank if you do not expect any pricing seasonality:
If you expect selling prices increasing over time (for example, due to inflation), enter respective increase rate along with its frequency into Price Increase fields.
Projections of sales revenue and cost of sales will be calculated automatically. If you expect inflation-driven changes of purchase prices to happen over time, enter respective parameters into Cost Increase fields.
Forecasts of inventory at cost, at the end of each month, will be calculated by multiplying the average daily cost of sales in the next month by the number of Days of Inventory.
The difference between the opening and closing inventory, plus costs of sales, will give us the projected cost of purchased goods. For example, if you set your purchase credit period to a non-zero value, your cash payments to suppliers will lag after the purchase of goods by the respective number of days.
After you entered sales-related assumptions in the Sales & Inventory section, go to the Fixed Costs section.
2. Fixed Costs
In the Staff table, enter names of employee positions, along with their respective salaries. For each projected month, enter the expected numbers of employees.
If you expect that salaries will increase over time, enter expected increase rates and frequencies into Salary Increase fields. Time-adjusted salaries will be calculated for each month of the projected period.
In the Other Monthly Expenses table, enter names of expense items into the list, and then enter expected monthly expense amounts for each projected month.
Next, enter expected startup costs for your Grocery Financial Model.
3. Startup Costs
The usefulness of the model’s outcomes will depend on the degree of accurateness in estimating startup costs. However, as we already said, it is rather dangerous to underestimate them.
There is an Account field there, you can indicate whether respective startup expenditure will create an asset (for example, property, equipment, software or patents), or be accounted for as an expense. However, if you do not need to produce pro forma financial statements for your business plan, you can ignore this column altogether.
If your purpose is simply to get a snapshot of projected revenues and costs, you can jump right to the reports. However, if you’ll need a more comprehensive financial picture, go to the Miscellaneous section first.
Here you can add figures related to financing and fixed assets.
In the Debt & Equity Financing table, enter:
- Equity contribution – money injected to the business by owners (co-investors);
- Equity repayments – amounts of initial investments withdrawn by owners (co-investors) at par, as well as payouts to the owners (co-investors) expected to be made out of net profit;
- Debt Issuance – amounts of loans received from a bank or other lender;
- Debt Repayment – payments in order to repay the principal amount of the loan;
- Interest Paid – payments for the use of the loan.
Fixed assets are those you expect to be in use for more than one year. They include tangible assets, also known as property, plant and equipment, and intangible assets (software, patents and trademarks).
Enter amounts you are going to spend for fixed assets, other than startup costs, to the Fixed Assets table. For the sake of simplicity, we do not calculate depreciation and amortization; however, you can add them manually in this table.
Now you can go to the reports. They are available from the Dashboard section. Grocery Store Financial Model template contains the following reports:
- Monthly and yearly pro forma financial statements including Income statement, Balance sheet and Cash flow statement;
- Profit (EBITDA) report presenting breakdowns of income and operating expenses by line items;
- Revenue Details report displaying revenues for each category of goods;
- Breakeven Analysis report giving you an estimation of the payback period;
- Gross Profit Breakdown report displaying details of income, direct costs and profit margins for each category of goods;
- Gross Profit Report displaying a summary of gross margins for each category of goods for selected periods;
- NPV and IRR calculations and a chart of cumulative discounted cash flow giving you an estimation of discounted payback period;
- Account Payable report quantifying factors affecting accounts payable in each reporting quarter, broken down by categories of goods;
- Inventory Balances report displaying the cost of inventory in stock, at the end of each reporting quarter.
GROCERY STORE FINANCIAL MODEL FEATURES SUMMARY:
- Easy-to-use feasibility study for grocery shops
- Understandable accounting spreadsheet
- Works both on Mac and Windows
- No installation needed, ready-to-use
- Professional design and suitable for presentations
- On Sheet Instructions
- White Label
- Compatible with Excel 2010 and later versions