To achieve sustainable financial growth for your business, a good first step is sales growth. While sales growth is only part of true financial growth, as there are other factors, most businesses believe that the sales growth problems is ultimately what hinders future business and financial growth. This article is about building the right sales plan to enable financial growth, and doing it in the right way, using Excel. It is also important to improve your sales forecasting efficiency in a way to allows you to predict the future success of the business, and choose between different possible outcomes (forecasts). While sales planning is just one part of financial planning (expenses planning being left out), for most businesses, expenses are relatively predictable, although as always, there are always places to improve also in expenses.
We’ve all been using Excel for years for everything in our business: from Sales Planning and Sales Forecasting, to Supply Chain Planning, Inventory Planning, Workforce Planning, and more.
But using Excel for everything in a simple fashion is not the same as fully capturing all the value that Excel provides us using its complex mechanisms. These allow us to increase efficiency and reduce mistakes by working in a proper way that is easy to formulate and explain to co-workers, and justify the way it helps financial growth.
In this tutorial, focused on Sales Planning, we will show you how to integrate all the data you have on your products, such as Sales Forecast, Sales Actuals, Pricing, Promotions, distributors and resellers and more, into one organized Excel file. We will see how to create it in a way that makes it easy to make calculations, such as Forecast Errors, Forecast Formulas, Profit and Revenue metrics and more, in a way that is easy to understand and reduces human errors.
This can serve to greatly increase efficiency in sales planning because by having all data in one secure and reliable place, it is easier to find what we are looking for and to easily coordinate between business units (such as the ones that provide us pricing and promo data that is used in our sales forecasts). As noted, this is just one part of financial planning, and similar methods an be used to address other parts of financial planning, such as expenses planning/reporting.
Step 1: Understand Why you Need a Sales Plan and What It Is
A sales plan lays out your objectives, high-level tactics, and potential obstacles. It also describes the target audience for your product or service. It’s like a business plan but it focuses on the execution side of sales. While a business plan lays out goals, a good sales plan should describe exactly how and what you intend to achieve, in terms of actual sales. Here, we will focus on the specifics of outlining a detailed sales plan for all products, in Excel, and less on the high-level tactics. a truly detailed sales plan that focuses on financial growth can drive real growth for a business. We will show how to do proper sales forecasting as well.
Step 2: Learn from this Example Excel File of Apple’s Products
Before we begin, please note that the full Excel examples provided here and in the other articles in our blog can be downloaded from the resources section.
Now, let’s get going: the sales plan is based on the product and trade partner (retailer) hierarchies as in this example for Apple’s products, sold at T-Mobile, Apple Store and others as in the Excel file.
And a cell is defined for each week in the sales plan as in the Excel file.
Step 3: Define the Requirements from a Sales Planning Excel Sheet
It is easy to make an Excel sheet that is too simple and does not hold enough information, and also it is easy to over-clutter the Excel sheet which will make it less usable and error-prone.
While walking on this fine line, we’ve devised these basic requirements from an Excel sheet for Sales Planning:
- It should be easy to type in the forecast for the periods defined (Daily/Weekly or Monthly). It should be clear to managers and planners what each number means.
- It should be easy to import POS files from different systems that were prepared with different levels of aggregations (daily, weekly or monthly according to the export we’re receiving)
- There should be clear separation between formulas of different kinds, with the preference for having as few types of formulas as possible.
- It should be easy to rebuild a formula for a specific cell if it gets deleted or modified by mistake, as each sheet should hold only one type of formula in all cells (more on this below)
- It should be easy for managers to calculate measures such as forecast errors, revenues and profits, without having to rearrange formulas and recalculate everything every time new data arrives or new forecasts are made. It should be easy to see that no errors were made in the formula calculation.
- It should be easy to add to products or distributors to the sheets without much fuss about replicating formulas and with high confidence that you’re doing the right thing and not adding mistakes.
Step 4: Learn the Full Structure of the Recommended Excel Sheet for your Sales Plan
We start with the premise of having one Excel file for all planning activities of a specific division in the company. For this example, for sales planning, we have one Excel file with one Tab for each property that is of interest to us.
Each “property” pertains to one product (usually SKU), sold at one retailer or distributor.
So the lower level of our Forecast and Actual data will be the SKU-Retailer-Property-Week level. It sounds complicated, but it simply means that we have one number for a forecast (for example), for a specific SKU, sold at a specific retailer/distributor, per week.
Here are some examples of properties:
- Total Sales (Sales Actual)
- Sales Forecast
- Price
- Original Price (before discount)
- Forecast Error (calculated as the Total Sales minus the Sales Forecast), and can be aggregated over any category level.
- Manufacturing cost of our product
- Price of the product to the retailer/distributor
- Profit per Unit (calculated as a formula of properties above)
- Revenue per Unit (also a formula)
As it can be seen, some properties should be imported from other systems (like Sales Actual), some properties need to be input manually (or semi-manually, with some help, like the Sales Forecast), and some properties can be calculated, like Forecast Error and Revenue.
Step 5: Choosing a Time-scale for the Planning Sheets
When building from scratch a new planning system in Excel, the decision on time-scale is an important one. It is possible to create plans on a daily, weekly, monthly, quarterly or on a yearly level.
Obviously, the monthly level provides a comfortable trade-off between the load of data to the granularity needed to plan across different periods of the year.
However, sometimes, especially when planning for the holidays, it is needed that we plan on a weekly or even daily basis. Especially when planning pricing – it is important to set the right price for Black Friday and other important holidays, and have the best retail price only on the specific day of the holiday (4th of July, Black Friday, Memorial Day).
On the other hand, by planning on a weekly or daily basis, we’re making our work much more difficult, as our Excel sheet becomes super-cluttered with so many dates or weeks that are of no special importance, yet require planning.
There is no bullet-proof solution here, and each company should choose to plan by the time period that is appropriate to it. Sometimes it may be worthwhile to plan for the whole year on one scale (e.g. Weekly), and still plan on a daily level for specific high-impact holidays.
In this example, we will stick with the Weekly planning process, as we find it as providing the best balance between making the dataset small to providing enough granularity to plan pricing and sales properly.
Step 6: Step-by-Step: Build your Excel File
The Excel file contains all the retailer/trade partner and product hierarchies, with numbers allocated for each week in the forecast.
To the right of each line item, we have the forecasted weeks.
By having separate sheets for the Forecast and the Actual numbers, this makes it easy to calculate formulas such as forecast error.
Step 7: Integrate Formulas Like Forecast Errors
The method described below can be used to consistently calculate any formula whose result arises from a calculation from one, two or more properties, provided for the same time period (e.g. Day, Week or Month). Assuming we are using Excel for sales forecasting, we will now show how to calculate forecast errors.
The forecast error is defined:
Forecast Error = (Sales Forecast) – (Sales Actual)
Note the definition in the formula field, that uses the ! (exclamation mark) operator to cross-reference other worksheets in the Excel file. Here is how it is defined in our Excel file.
The absolute forecast error is defined as the absolute value of the above, see in the file.
Here is the sheet for this forecast error, outlining the same product and retailer hierarchy, but with the formula defined for all cells corresponding to the numbers in the other sheets.
We’ve seen time and again, how organizations struggle to implement this simple formula in a consistent form across of product and distributors. We’re here to help with this template, that will ensure consistency across the organization, and allow all data to reside in one place, where it is easy to check and analyze.
Step 8: Import POS and Other Input Files in an Easy Way
It is very common for large manufacturers and distributors, to have as past input the POS (Point-of-Sale) files of many retailers that sell their products. These POS files should serve as input for analysis of pricing and past sales, to draw important insights. However, some retailers may provide their POS files in a Weekly format, others in Daily, and some may provide a Monthly aggregate (less useful!). The file formats may also be somewhat different.
We have devised a special Excel technique for importing these POS files into the Excel file. While this is beyond the scope of this tutorial, we plan on writing another tutorial on this very important issue (it involves VLOOPUP and XLOOPUP. Let us know if this is important to you).
Step 9: Integrate Automatic and Semi-Automatic Forecasts, if Possible
While forecasting algorithms are outside the scope of this tutorial, in some cases a simple formula can serve as a forecast. For example, if you can find a reliable connection between pricing and total sales for a product, you can model it as a simple analytical formula. This makes sales forecasting an easy task in this case.
For example, let’s say we find that for each 10% discount in price, the sales quantity rises by 20%. And the discount can be calculated from the Final Price and Original Price (MAP Price).
Then we have:
Sales Total = Sales Without Discount * (1 + Discount_Percentage)
In this case we have a rough estimate of sales using the formula, and can create a Property in another Sheet that will calculate it for the future, for all Weeks or Months that are considered for the forecast. This can be used for a rough revenue and profit analysis of the future pricing that were set.
For example, we can use the forecast estimate to forecast Revenue:
Forecasted Revenue = (Sales Total) * Price
and Profit:
Forecasted Profit = (Sales Total) * (Price – Manufacturing Cost)
As explained, by ensuring a consistent methodology for calculating these properties, each in its our Sheet, we are drastically reducing our chances of errors, and allowing for an easier and more reliable analysis process for the finance analysts.
Obviously, this type of forecasting is very rough and inaccurate, but it can serve as a place to start. After calculating this kind of simplified forecast, it may be necessary to fine-tune it for specific holidays and/or specific products where it may be less accurate.
More Formula Examples
It is also possible to define more sheets to include data like Manufacturing Cost and Price to Retailer on each SKU. This allows us to define formulas like Revenue and Profit. Here for our example we show how we did it.
Here’s the sheet for manufacturing cost. See in the Excel file.
As it can be seen, the manufacturing cost usually does not vary between the weeks. However, for consistency in our Excel sheets, the number is duplicated across all weeks.
Here is how we defined the product sales price for each retailer in the Excel file.
Using this, we defined the revenue sheet to hold the product of Sales Actual and Sales Price to Retailer.
This is how the formula is defined in Excel, note the cross-referencing of other sheets:
Finally, we have another sheet to calculate the profit.
The profit is defined as the revenue minus the manufacturing cost for an item.
This is how the formula is defined in Excel.
Step 10: Embrace Collaborative Work: Excel Desktop vs Excel Online
One important decision that needs to be made is whether we’re going to use Excel in its Desktop Version or the online version provided at Microsoft’s websites (outlook.com, live.com).
The online version of Excel has the advantage of being accessible from anywhere, any computer, regardless of operating system (e.g. Linux, Mac, Windows) or software installed (you just need a web browser). The offline version requires a Windows computer with Office Installed, with the latest version of Excel.
Although the cloud-based solution is appealing, from our experience the features provided by Excel online are insufficient. Our recommendation here is to stick with the installed Excel software, working on a shared hard-drive (e.g. using Microsoft OneDrive).
This will allow the financial analysts to use the rich feature-base of Excel, such as:
- Pivot-tables
- Pivot-charts
- Advanced Macros (if needed, not in scope here)
Creating Management Dashboards
To create management dashboards, there are several options:
- create a separate sheet as a dashboard, and provide there plots and summaries of the data from the other sheets
- connect the Excel sheet to a separate BI (Business Intelligence) suite that will pull the data regularly from t he Excel sheet, and build a dashboard using the BI suite.
While it is straightforward to create a separate worksheet and populate it with plots of data from the other worksheets, this method is somewhat technical and required to be done repetitively as new data arrives. The second method is much better in terms of data analysis capabilities, but may break down if data integrity is compromised.
Our suggestion is to stick with a uniform structure of all Excel sheets to minimize the possibility of compromising data integrity, and to use a BI system that is flexible and can regularly refresh from input provided by an Excel sheet (feel free to consult with us for some options here).
Never mind the way to ultimtately choose to build your management dashboards, they should focus on the financial growth of the business, its ingredients, and lay out a way to produce the right financial plan for business expansion.
Downloading the Excel Resources
If you’re interested in the example described above, feel free to download a full Excel sheet with all the examples described in this article. Click the resources section to download.
Conclusions
We have provided a full framework for sales planning and sales forecasting in Excel for organizations ranging from medium to large, that is very consistent and predictable, and allows for better reliability of the numbers. This is achieved by using very few formulas overall, and repeating the same formulas in different cells in each sheet, in such a way that it’s more easier to understand and more difficult to make mistakes. This should enable a better visibility into the business to allow for easier and fast financial growth.
Our methodology is great especially for large teams working on large shared Excel sheets, where consistency is paramount.
While the system provided cannot ensure that no underlying mistakes in formulas are made in the Excel sheet, if everyone keeps faithfully adhering to it there is a good chance it will form a process and reduce overall mistakes and time spend on organizing Excel sheets.
If you need a helping hand in implementing this process in your company, feel free to contact our team of experts. We also appreciate any feedback and questions in the comments below. Challenge us 🙂
We’ve been helping businesses achieve great, quantifiable and measureable financial growth for many years, talk to us.
Appendix 1: who could use our Sales Planning Sheets?
This document is aimed at any company – where sales planning is necessary, selling any of these:
- Physical Products – from consumer products to industrial machinery
- Cloud-based (SaaS) business software
- Service-based companies, where the service has a fixed-cost structure (perhaps over several tiers)
- Almost any company that employs salespeople
