Supply Chain Planning, compared to sales planning, is much more difficult on several levels. When planning for the supply chain, we must account for the manufacturing constraints of our manufacturing plants or outsourced manufacturers. We must also account for the locations in which our factories and storehouses reside, and intelligently plan in a way that will minimize delivery costs and mitigate issues in storehouse availability and limited storage space.<\/p>\n\n\n\n
The goal of supply chain planning is to optimize the processes around the manufacturing, storage and delivery of goods in a way that will improve our supply chain metrics such as availability at different locations, preventing issues such as not enough goods to deliver to customers, and also prevent ordering or manufacturing too much goods.<\/p>\n\n\n\n
The exact metrics and their formulas will be discussed in a separate article. The goal of this article is to show how the whole supply chain can be managed in Excel in a way that is simple, understandable and easy to use, yet also provides insights.<\/p>\n\n\n\n
Step 2: Define the Requirements from the Excel File<\/strong><\/h2>\n\n\n\nIt is easy to make an Excel file that is too simple and does not hold enough information, and also it is easy to over-clutter the Excel file which will make it less usable and more error-prone.<\/p>\n\n\n\n
While walking on this fine line, we’ve devised these basic requirements from an Excel file for Supply Chain Planning:<\/p>\n\n\n\n
\n- It is important that it is easy to type in the forecast for the periods defined (Weekly or Monthly). It should be clear to managers and planners what a number means.<\/li>\n\n\n\n
- It should be easy to import Orders files from different systems that were prepared with different levels of aggregations (weekly or monthly according to the export we’re receiving).<\/li>\n\n\n\n
- There should be a clear separation between formulas of different kinds, with the preference for having as few types of formulas as possible (we think one formula per sheet is how it should work).<\/li>\n\n\n\n
- It should be easy to rebuild a formula for a specific cell if it gets deleted, as each sheet should hold only one type of formula in all cells (more on this below).<\/li>\n\n\n\n
- It should be easy for managers and planners to calculate measures such as forecast errors, revenues and profits, without having to rearrange formulas and recalculate everything every time new data is brought in or new forecasts are made. It should be easy to understand and ensure that no errors are made.<\/li>\n\n\n\n
- It should be easy to add new items to products list or distributors (rade customers) list to the sheets without much difficulty and to do it in a way that all formulas will continue to function properly.<\/li>\n<\/ul>\n\n\n\n
Step 3: Build the Excel File in This Recommended Structure<\/strong><\/h2>\n\n\n\nWhile the basic premise for planning in Excel is similar across all sales functions, the specific properties that we plan for and the metrics and formulas we use are different. Here we lay out our recommendation for basic supply chain planning.<\/p>\n\n\n\n
We assume that all planning is done in one Excel file that holds several Tabs for the -different properties that we plan for or calculate.<\/p>\n\n\n\n
What is a Property?<\/strong><\/h5>\n\n\n\nEach “property” pertains to one product (SKU), sold at one retailer or distributor (trade customer).<\/p>\n\n\n\n
Here are some examples of properties:<\/p>\n\n\n\n
\n- Total Order (Orders Actual)<\/li>\n\n\n\n
- Orders Forecast<\/li>\n\n\n\n
- Forecast Error (calculated as the Total Orders minus the Orders Forecast)<\/li>\n\n\n\n
- Manufacturing cost of product<\/li>\n\n\n\n
- Price of the product sold to the retailer\/distributor<\/li>\n\n\n\n
- Revenue (also a formula)<\/li>\n\n\n\n
- Profit (formula)<\/li>\n<\/ul>\n\n\n\n
Step 4: Choose a Time-scale for the Planning Sheets<\/strong><\/h2>\n\n\n\nWhen 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 weekly or monthly level, or select any different level if you see it useful (such as quarterly plans).<\/p>\n\n\n\n
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. It also saves time by not having to plan for each week.planning.<\/p>\n\n\n\n
In this example, we will stick with the Monthly planning process, as we find it as providing the best balance between making the dataset small to providing enough granularity to plan orders properly.<\/p>\n\n\n\n
Step 5: Learn from this Full Example Excel File for Apple\u2019s Products<\/strong><\/h2>\n\n\n\nBefore 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.<\/p>\n\n\n\n
Now, let’s start: we will use this list of Apple\u2019s products, given by SKU, Name and some properties such as Type and Memory Slots (as levels of hierarchy):<\/p>\n\n\n\n
Here are all the attributes for a given product\/SKU.<\/p>\n\n\n\n
In this list you can see the list of Apple\u2019s products such as the iPhone<\/strong>, iPad,<\/strong> Apple Watch<\/strong> with the different model numbers (SKU<\/strong>s), as well as some properties such as Memory Size, Product Type (iPhone \/ iPad \/ \u2026), Manufacturing Year, etc. These properties are useful as it makes it easier to filter and produce reports aggregated over certain properties.<\/p>\n\n\n\nNote the Retailer name on the left side. We will duplicate this list for each retailer. For this example, we have retailers such as Recharge Electronics<\/strong>, Apple Store<\/strong>, BrandsMart<\/strong>, etc. We duplicate the entire catalog over each Retailer, so that planning can be done separately for each retailer (it will later be possible to aggregate over the retailers as needed).<\/p>\n\n\n\nFor each product-retailer combination in the list, we will produce a list of months:<\/p>\n\n\n\n
These months range from January 2019 to December 2022. We allow planning separately for each month in this range. As needed, more months\/years can be added to this. This is also replicated across each product-retailer combination. <\/p>\n\n\n\n
This example sheet<\/strong> is for the Forecast<\/strong>, and it can be used across many sheets.<\/p>\n\n\n\nWe duplicate this scheme across several sheets, as in this list:<\/p>\n\n\n\n
We create several sheets. While some are numbers that are actually typed in (e.g. Manufacturing Cost<\/strong>, Orders Forecast<\/strong>) other sheets contains only formulas. These sheets can calculate a certain property, such as Forecast Error<\/strong>, from the values given by other sheets. By taking the same cell location in other sheets, it is easy to make formulas that calculate values across several sheets, for all product-retailer-month combinations.<\/p>\n\n\n\nHere is an example of how the Tab for Orders Forecast<\/strong> looks like:<\/p>\n\n\n\nHere we can see the numbers that the user typed in for the orders forecast. This can be a manual or semi-manual process, by using advanced calculations in excel to extrapolate future orders from past orders.<\/p>\n\n\n\n
Here is the Orders Actual<\/strong>.<\/p>\n\n\n\nIn this sheet, Actual numbers need to be put. These numbers can come from a separate ERP system, such as SAP ERP<\/strong> or Microsoft Dynamics ERP<\/strong>, or for some businesses, it can be copied from the orders or invoicing system.<\/p>\n\n\n\nStep 6: Incorporate Formulas into the Sheet in a Controlled Way<\/strong><\/h2>\n\n\n\nThe method described above can be used to consistently calculate any formula whose result arises from a calculation from two or more properties, provided for the same time period (monthly in our example).<\/p>\n\n\n\n
The forecast error is defined as:<\/p>\n\n\n\n
Forecast Error<\/strong> = (Orders Forecast<\/strong>) – (Orders Actual<\/strong>)<\/p>\n\n\n\nand the absolute forecast error is defined as the absolute value of the above. It is easy to create another Tab that will hold the forecast error, like this (note the formula above):<\/p>\n\n\n\n
As it can be seen, each cell holds the same formula and can easily be replicated across the whole sheet. Excel will automatically adapt the cell locations when copy-pasting the formula across all cells.<\/p>\n\n\n\n
The forecast error is defined in all cells for all product-retailer combinations over all months.<\/p>\n\n\n\n
Note that this is the Signed Forecast Error. This forecast error is useful also for calculating aggregated forecast error across different categories of products. This can show us how good\/bad our forecast is regarding overall total number of products, regardless of specific product SKU. For example, we can see if our forecast matched the Actual in the total number of iPhones ordered.<\/p>\n\n\n\n
It is also useful to define the Absolute Forecast Error<\/strong>, as here:<\/p>\n\n\n\nThe absolute forecast error is a different indicator that considers positive errors to be as important as negative errors. Using this, the importance of out-of-stock<\/strong> is the same as the importance of over-supply<\/strong>. All errors are calculated and the absolute number is taken. This can be aggregated to calculate the Total Absolute Error of the forecast. <\/p>\n\n\n\nStep 7: Incorporate Complex Formulas in a Simple Way<\/strong><\/h2>\n\n\n\nBy Creating more Tabs for [Manufacturing Cost] and [Price to Retailer], it is now possible to create formulas to calculate the Revenue and Profit.<\/p>\n\n\n\n
For the revenue see spreadsheet.<\/p>\n\n\n\n
Note the formula for the revenue. The revenue is defined simply as cost of product sold multiplied by the amount sold. <\/p>\n\n\n\n
For the profit, see spreadsheet.<\/p>\n\n\n\n
Note the formula for the profit. The profit is defined simply as cost of product sold minus the manufacturing cost, all this multiplied by the amount sold.<\/p>\n\n\n\n
The profit and revenue metrics can hold different information that helps in decision-making across the organization. By breaking down products according to revenue, it is important to see which SKUs bring in the most revenue. However, it is even more important to see which SKUs bring in the most profit.<\/p>\n\n\n\n
By having this structure duplicated across all sheets it is easy to calculate any formula that draws numbers from any of the sheets. It is easy and consistent to replicate it across all product-retailer-month combinations.<\/p>\n\n\n\n
It is also easy now, by arranging the data in an Excel table, to filter by any property value. <\/p>\n\n\n\n
For example here, we filter only the iPhone 11, so we can see how well it is doing across all Retailers.<\/p>\n\n\n\n
Step 8: Automate Forecasting, but Don’t Over-Automate if Not Possible<\/strong><\/h2>\n\n\n\nSupply chain planning is difficult. There are many variables that need to be accounted for in the planning process. Due to this, the planning process is in many times manual or semi-manual. It is difficult to create an algorithm to account for the different effects such as special promotions, competitive landscape, etc. Therefore, our goal here is to simplify the manual planning process as much as possible, while not automating it completely. We will write about specific methods for forecasting within Excel in a different article (feel free to write to us if it\u2019s important to you, we\u2019re looking for new ideas for articles).<\/p>\n\n\n\n
For now, please note that forecasting can<\/strong> be automated, but only where it is indeed predictable. For example, if a change in price consistently shows a change in sales, we can formulate this and put the formula in the planning Excel sheets.<\/p>\n\n\n\nStep 9: Embrace Collaborative Planning<\/strong><\/h2>\n\n\n\nFor a comparison of using Excel Desktop vs Excel Online please look here. Overall, our recommendation is to use Excel Desktop on a shared file in a shared drive. We believe this will allow for easier analysis by the financial analysts, that can use advanced features such as pivot-tables, pivot-charts, etc.<\/p>\n\n\n\n
Downloading the Example Excel File<\/strong><\/h5>\n\n\n\nAll the results and examples presented in this article can be downloaded here<\/a>.<\/p>\n\n\n\nFeel free to contact us with any suggestions, issues, or requests for any help.<\/p>\n\n\n\n
Step 10: Find Other Processes that Can Be Automated<\/strong><\/h2>\n\n\n\nThis article is a part of a series of articles that explain other financial processes that may be automated at your company.<\/p>\n\n\n\n
\n- Sales Planning in Excel – here we’re focused on planning at the Weekly level, as is needed when doing sales planning for physical products sold at retailer\/resellers. It may be useful for other types of sales planning as well. We provide a method to put a list of retailers and list of products, and plan sales separately for each combination. We also provide formulas for things like forecast errors and other important metrics for Sales Planning.<\/li>\n\n\n\n
- Supply Chain Planning in Excel – in this article we provide a template for sales at the Monthly level, for supply chain forecasts and actuals, which include Orders and Orders Forecasts, Inventory and Inventory Forecasts, and provide formulas relevant to the supply chain.<\/li>\n\n\n\n
- Multi-Site and Multi-Currency Budget Planning in Excel – this article focuses on budget planning and analysis and is focused mostly on expenses reporting for the budget, explaining complex issues such as multi-currency and multi-site reporting, and shows how we recommend to build an Excel file for this use-case.<\/li>\n<\/ol>\n\n\n\n
Appendix: Is this Method Good for Your Company? <\/strong><\/h2>\n\n\n\nThis document is aimed at any company where supply chain planning is necessary, and therefore is geared more towards physical products. So if you are manufacturing any kind of physical goods, this document may serve you well, and help you organize your Excel sheets and plan properly for your supply chain.<\/p>\n\n\n\n
This document is also useful if your company is not a manufacturer, but a distributor, reseller or importer of goods manufactured by other companies. In that case, the factories are not owned by your company, yet all the important issues of supply chain, such as planning for different locations and different products, and calculating all the availability metrics, are still relevant. <\/p>\n\n\n\n
Comparison to sales planning: as said, there is a separate article as well as an example of an Excel file for Sales Planning. While Sales Planning and Supply Chain Planning carry many similar characteristics, it is important to note that we are not talking about the same process. In some companies, especially those selling to a small number of customers, and selling amounts that do not change much, there may be almost no need for sales planning. In other companies, sales planning may be a very important complement to supply chain planning. Also, in companies that do not sell physical goods, such as SaaS (Software-as-a-Service) software companies, or service companies, there may be no need to supply chain planning, but there is still a need for sales planning. So read the right document as it fits your company.<\/p>\n\n\n\n
Our suggestion is as follows:<\/p>\n\n\n\n
If you are selling physical products:<\/p>\n\n\n\n
\n- if you already have a tool for supply chain planning (such as an ERP Planning Module), you may keep using the same tool and not move your supply chain planning to Excel. If you need sales planning, read this<\/a>.<\/li>\n\n\n\n
- If you do not have a tool for supply chain planning, start with this document to organize your supply chain planning in Excel.<\/li>\n<\/ul>\n\n\n\n
If you are selling non-physical products, such as software or services:<\/p>\n\n\n\n
\n- You don\u2019t need supply chain planning<\/li>\n\n\n\n
- Start with this document to do sales planning properly – Sales Planning in Excel article.<\/li>\n<\/ul>\n\n\n\n
Also, please note that in our sales planning example we build everything around planning on a weekly basis, whereas here we plan monthly. Use what fits your organizations and processes in a better way.<\/p>\n\n\n\n
Conclusions<\/strong><\/h2>\n\n\n\nWhile the system provided cannot ensure that no underlying mistakes in formulas are made in the Excel file, if everyone keeps faithfully adhering to it there is a good chance it will form a process and reduce overall mistakes and time spent on organizing Excel sheets. It will also increase the \u201cbelief in the numbers\u201d, as it\u2019s very important for the various stakeholders to be sure that what they are seeing is correct.<\/p>\n\n\n\n
If you need a helping hand in implementing this process in your company, or want us to send you an example Excel file, feel free to contact our team of experts. We also appreciate any feedback and questions in the comments below.<\/p>\n\n\n\n
\n\n\nWould you like to learn about hot new disruptive startups, and how they challenge the incumbents?\n<\/h1>\n\n\n\nRead our free newsletter with in-depth analysis of disruptive startups, and how they ramp up against the competition.\n<\/h2>\n\n
\n
Step 3: Build the Excel File in This Recommended Structure<\/strong><\/h2>\n\n\n\nWhile the basic premise for planning in Excel is similar across all sales functions, the specific properties that we plan for and the metrics and formulas we use are different. Here we lay out our recommendation for basic supply chain planning.<\/p>\n\n\n\n
We assume that all planning is done in one Excel file that holds several Tabs for the -different properties that we plan for or calculate.<\/p>\n\n\n\n
What is a Property?<\/strong><\/h5>\n\n\n\nEach “property” pertains to one product (SKU), sold at one retailer or distributor (trade customer).<\/p>\n\n\n\n
Here are some examples of properties:<\/p>\n\n\n\n
\n- Total Order (Orders Actual)<\/li>\n\n\n\n
- Orders Forecast<\/li>\n\n\n\n
- Forecast Error (calculated as the Total Orders minus the Orders Forecast)<\/li>\n\n\n\n
- Manufacturing cost of product<\/li>\n\n\n\n
- Price of the product sold to the retailer\/distributor<\/li>\n\n\n\n
- Revenue (also a formula)<\/li>\n\n\n\n
- Profit (formula)<\/li>\n<\/ul>\n\n\n\n
Step 4: Choose a Time-scale for the Planning Sheets<\/strong><\/h2>\n\n\n\nWhen 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 weekly or monthly level, or select any different level if you see it useful (such as quarterly plans).<\/p>\n\n\n\n
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. It also saves time by not having to plan for each week.planning.<\/p>\n\n\n\n
In this example, we will stick with the Monthly planning process, as we find it as providing the best balance between making the dataset small to providing enough granularity to plan orders properly.<\/p>\n\n\n\n
Step 5: Learn from this Full Example Excel File for Apple\u2019s Products<\/strong><\/h2>\n\n\n\nBefore 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.<\/p>\n\n\n\n
Now, let’s start: we will use this list of Apple\u2019s products, given by SKU, Name and some properties such as Type and Memory Slots (as levels of hierarchy):<\/p>\n\n\n\n
Here are all the attributes for a given product\/SKU.<\/p>\n\n\n\n
In this list you can see the list of Apple\u2019s products such as the iPhone<\/strong>, iPad,<\/strong> Apple Watch<\/strong> with the different model numbers (SKU<\/strong>s), as well as some properties such as Memory Size, Product Type (iPhone \/ iPad \/ \u2026), Manufacturing Year, etc. These properties are useful as it makes it easier to filter and produce reports aggregated over certain properties.<\/p>\n\n\n\nNote the Retailer name on the left side. We will duplicate this list for each retailer. For this example, we have retailers such as Recharge Electronics<\/strong>, Apple Store<\/strong>, BrandsMart<\/strong>, etc. We duplicate the entire catalog over each Retailer, so that planning can be done separately for each retailer (it will later be possible to aggregate over the retailers as needed).<\/p>\n\n\n\nFor each product-retailer combination in the list, we will produce a list of months:<\/p>\n\n\n\n
These months range from January 2019 to December 2022. We allow planning separately for each month in this range. As needed, more months\/years can be added to this. This is also replicated across each product-retailer combination. <\/p>\n\n\n\n
This example sheet<\/strong> is for the Forecast<\/strong>, and it can be used across many sheets.<\/p>\n\n\n\nWe duplicate this scheme across several sheets, as in this list:<\/p>\n\n\n\n
We create several sheets. While some are numbers that are actually typed in (e.g. Manufacturing Cost<\/strong>, Orders Forecast<\/strong>) other sheets contains only formulas. These sheets can calculate a certain property, such as Forecast Error<\/strong>, from the values given by other sheets. By taking the same cell location in other sheets, it is easy to make formulas that calculate values across several sheets, for all product-retailer-month combinations.<\/p>\n\n\n\nHere is an example of how the Tab for Orders Forecast<\/strong> looks like:<\/p>\n\n\n\nHere we can see the numbers that the user typed in for the orders forecast. This can be a manual or semi-manual process, by using advanced calculations in excel to extrapolate future orders from past orders.<\/p>\n\n\n\n
Here is the Orders Actual<\/strong>.<\/p>\n\n\n\nIn this sheet, Actual numbers need to be put. These numbers can come from a separate ERP system, such as SAP ERP<\/strong> or Microsoft Dynamics ERP<\/strong>, or for some businesses, it can be copied from the orders or invoicing system.<\/p>\n\n\n\nStep 6: Incorporate Formulas into the Sheet in a Controlled Way<\/strong><\/h2>\n\n\n\nThe method described above can be used to consistently calculate any formula whose result arises from a calculation from two or more properties, provided for the same time period (monthly in our example).<\/p>\n\n\n\n
The forecast error is defined as:<\/p>\n\n\n\n
Forecast Error<\/strong> = (Orders Forecast<\/strong>) – (Orders Actual<\/strong>)<\/p>\n\n\n\nand the absolute forecast error is defined as the absolute value of the above. It is easy to create another Tab that will hold the forecast error, like this (note the formula above):<\/p>\n\n\n\n
As it can be seen, each cell holds the same formula and can easily be replicated across the whole sheet. Excel will automatically adapt the cell locations when copy-pasting the formula across all cells.<\/p>\n\n\n\n
The forecast error is defined in all cells for all product-retailer combinations over all months.<\/p>\n\n\n\n
Note that this is the Signed Forecast Error. This forecast error is useful also for calculating aggregated forecast error across different categories of products. This can show us how good\/bad our forecast is regarding overall total number of products, regardless of specific product SKU. For example, we can see if our forecast matched the Actual in the total number of iPhones ordered.<\/p>\n\n\n\n
It is also useful to define the Absolute Forecast Error<\/strong>, as here:<\/p>\n\n\n\nThe absolute forecast error is a different indicator that considers positive errors to be as important as negative errors. Using this, the importance of out-of-stock<\/strong> is the same as the importance of over-supply<\/strong>. All errors are calculated and the absolute number is taken. This can be aggregated to calculate the Total Absolute Error of the forecast. <\/p>\n\n\n\nStep 7: Incorporate Complex Formulas in a Simple Way<\/strong><\/h2>\n\n\n\nBy Creating more Tabs for [Manufacturing Cost] and [Price to Retailer], it is now possible to create formulas to calculate the Revenue and Profit.<\/p>\n\n\n\n
For the revenue see spreadsheet.<\/p>\n\n\n\n
Note the formula for the revenue. The revenue is defined simply as cost of product sold multiplied by the amount sold. <\/p>\n\n\n\n
For the profit, see spreadsheet.<\/p>\n\n\n\n
Note the formula for the profit. The profit is defined simply as cost of product sold minus the manufacturing cost, all this multiplied by the amount sold.<\/p>\n\n\n\n
The profit and revenue metrics can hold different information that helps in decision-making across the organization. By breaking down products according to revenue, it is important to see which SKUs bring in the most revenue. However, it is even more important to see which SKUs bring in the most profit.<\/p>\n\n\n\n
By having this structure duplicated across all sheets it is easy to calculate any formula that draws numbers from any of the sheets. It is easy and consistent to replicate it across all product-retailer-month combinations.<\/p>\n\n\n\n
It is also easy now, by arranging the data in an Excel table, to filter by any property value. <\/p>\n\n\n\n
For example here, we filter only the iPhone 11, so we can see how well it is doing across all Retailers.<\/p>\n\n\n\n
Step 8: Automate Forecasting, but Don’t Over-Automate if Not Possible<\/strong><\/h2>\n\n\n\nSupply chain planning is difficult. There are many variables that need to be accounted for in the planning process. Due to this, the planning process is in many times manual or semi-manual. It is difficult to create an algorithm to account for the different effects such as special promotions, competitive landscape, etc. Therefore, our goal here is to simplify the manual planning process as much as possible, while not automating it completely. We will write about specific methods for forecasting within Excel in a different article (feel free to write to us if it\u2019s important to you, we\u2019re looking for new ideas for articles).<\/p>\n\n\n\n
For now, please note that forecasting can<\/strong> be automated, but only where it is indeed predictable. For example, if a change in price consistently shows a change in sales, we can formulate this and put the formula in the planning Excel sheets.<\/p>\n\n\n\nStep 9: Embrace Collaborative Planning<\/strong><\/h2>\n\n\n\nFor a comparison of using Excel Desktop vs Excel Online please look here. Overall, our recommendation is to use Excel Desktop on a shared file in a shared drive. We believe this will allow for easier analysis by the financial analysts, that can use advanced features such as pivot-tables, pivot-charts, etc.<\/p>\n\n\n\n
Downloading the Example Excel File<\/strong><\/h5>\n\n\n\nAll the results and examples presented in this article can be downloaded here<\/a>.<\/p>\n\n\n\nFeel free to contact us with any suggestions, issues, or requests for any help.<\/p>\n\n\n\n
Step 10: Find Other Processes that Can Be Automated<\/strong><\/h2>\n\n\n\nThis article is a part of a series of articles that explain other financial processes that may be automated at your company.<\/p>\n\n\n\n
\n- Sales Planning in Excel – here we’re focused on planning at the Weekly level, as is needed when doing sales planning for physical products sold at retailer\/resellers. It may be useful for other types of sales planning as well. We provide a method to put a list of retailers and list of products, and plan sales separately for each combination. We also provide formulas for things like forecast errors and other important metrics for Sales Planning.<\/li>\n\n\n\n
- Supply Chain Planning in Excel – in this article we provide a template for sales at the Monthly level, for supply chain forecasts and actuals, which include Orders and Orders Forecasts, Inventory and Inventory Forecasts, and provide formulas relevant to the supply chain.<\/li>\n\n\n\n
- Multi-Site and Multi-Currency Budget Planning in Excel – this article focuses on budget planning and analysis and is focused mostly on expenses reporting for the budget, explaining complex issues such as multi-currency and multi-site reporting, and shows how we recommend to build an Excel file for this use-case.<\/li>\n<\/ol>\n\n\n\n
Appendix: Is this Method Good for Your Company? <\/strong><\/h2>\n\n\n\nThis document is aimed at any company where supply chain planning is necessary, and therefore is geared more towards physical products. So if you are manufacturing any kind of physical goods, this document may serve you well, and help you organize your Excel sheets and plan properly for your supply chain.<\/p>\n\n\n\n
This document is also useful if your company is not a manufacturer, but a distributor, reseller or importer of goods manufactured by other companies. In that case, the factories are not owned by your company, yet all the important issues of supply chain, such as planning for different locations and different products, and calculating all the availability metrics, are still relevant. <\/p>\n\n\n\n
Comparison to sales planning: as said, there is a separate article as well as an example of an Excel file for Sales Planning. While Sales Planning and Supply Chain Planning carry many similar characteristics, it is important to note that we are not talking about the same process. In some companies, especially those selling to a small number of customers, and selling amounts that do not change much, there may be almost no need for sales planning. In other companies, sales planning may be a very important complement to supply chain planning. Also, in companies that do not sell physical goods, such as SaaS (Software-as-a-Service) software companies, or service companies, there may be no need to supply chain planning, but there is still a need for sales planning. So read the right document as it fits your company.<\/p>\n\n\n\n
Our suggestion is as follows:<\/p>\n\n\n\n
If you are selling physical products:<\/p>\n\n\n\n
\n- if you already have a tool for supply chain planning (such as an ERP Planning Module), you may keep using the same tool and not move your supply chain planning to Excel. If you need sales planning, read this<\/a>.<\/li>\n\n\n\n
- If you do not have a tool for supply chain planning, start with this document to organize your supply chain planning in Excel.<\/li>\n<\/ul>\n\n\n\n
If you are selling non-physical products, such as software or services:<\/p>\n\n\n\n
\n- You don\u2019t need supply chain planning<\/li>\n\n\n\n
- Start with this document to do sales planning properly – Sales Planning in Excel article.<\/li>\n<\/ul>\n\n\n\n
Also, please note that in our sales planning example we build everything around planning on a weekly basis, whereas here we plan monthly. Use what fits your organizations and processes in a better way.<\/p>\n\n\n\n
Conclusions<\/strong><\/h2>\n\n\n\nWhile the system provided cannot ensure that no underlying mistakes in formulas are made in the Excel file, if everyone keeps faithfully adhering to it there is a good chance it will form a process and reduce overall mistakes and time spent on organizing Excel sheets. It will also increase the \u201cbelief in the numbers\u201d, as it\u2019s very important for the various stakeholders to be sure that what they are seeing is correct.<\/p>\n\n\n\n
If you need a helping hand in implementing this process in your company, or want us to send you an example Excel file, feel free to contact our team of experts. We also appreciate any feedback and questions in the comments below.<\/p>\n\n\n\n
\n\n\nWould you like to learn about hot new disruptive startups, and how they challenge the incumbents?\n<\/h1>\n\n\n\nRead our free newsletter with in-depth analysis of disruptive startups, and how they ramp up against the competition.\n<\/h2>\n\n
\n
Each “property” pertains to one product (SKU), sold at one retailer or distributor (trade customer).<\/p>\n\n\n\n
Here are some examples of properties:<\/p>\n\n\n\n
- \n
- Total Order (Orders Actual)<\/li>\n\n\n\n
- Orders Forecast<\/li>\n\n\n\n
- Forecast Error (calculated as the Total Orders minus the Orders Forecast)<\/li>\n\n\n\n
- Manufacturing cost of product<\/li>\n\n\n\n
- Price of the product sold to the retailer\/distributor<\/li>\n\n\n\n
- Revenue (also a formula)<\/li>\n\n\n\n
- Profit (formula)<\/li>\n<\/ul>\n\n\n\n
Step 4: Choose a Time-scale for the Planning Sheets<\/strong><\/h2>\n\n\n\n
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 weekly or monthly level, or select any different level if you see it useful (such as quarterly plans).<\/p>\n\n\n\n
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. It also saves time by not having to plan for each week.planning.<\/p>\n\n\n\n
In this example, we will stick with the Monthly planning process, as we find it as providing the best balance between making the dataset small to providing enough granularity to plan orders properly.<\/p>\n\n\n\n
Step 5: Learn from this Full Example Excel File for Apple\u2019s Products<\/strong><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Now, let’s start: we will use this list of Apple\u2019s products, given by SKU, Name and some properties such as Type and Memory Slots (as levels of hierarchy):<\/p>\n\n\n\n
Here are all the attributes for a given product\/SKU.<\/p>\n\n\n\n
In this list you can see the list of Apple\u2019s products such as the iPhone<\/strong>, iPad,<\/strong> Apple Watch<\/strong> with the different model numbers (SKU<\/strong>s), as well as some properties such as Memory Size, Product Type (iPhone \/ iPad \/ \u2026), Manufacturing Year, etc. These properties are useful as it makes it easier to filter and produce reports aggregated over certain properties.<\/p>\n\n\n\n
Note the Retailer name on the left side. We will duplicate this list for each retailer. For this example, we have retailers such as Recharge Electronics<\/strong>, Apple Store<\/strong>, BrandsMart<\/strong>, etc. We duplicate the entire catalog over each Retailer, so that planning can be done separately for each retailer (it will later be possible to aggregate over the retailers as needed).<\/p>\n\n\n\n
For each product-retailer combination in the list, we will produce a list of months:<\/p>\n\n\n\n
These months range from January 2019 to December 2022. We allow planning separately for each month in this range. As needed, more months\/years can be added to this. This is also replicated across each product-retailer combination. <\/p>\n\n\n\n
This example sheet<\/strong> is for the Forecast<\/strong>, and it can be used across many sheets.<\/p>\n\n\n\n
We duplicate this scheme across several sheets, as in this list:<\/p>\n\n\n\n
We create several sheets. While some are numbers that are actually typed in (e.g. Manufacturing Cost<\/strong>, Orders Forecast<\/strong>) other sheets contains only formulas. These sheets can calculate a certain property, such as Forecast Error<\/strong>, from the values given by other sheets. By taking the same cell location in other sheets, it is easy to make formulas that calculate values across several sheets, for all product-retailer-month combinations.<\/p>\n\n\n\n
Here is an example of how the Tab for Orders Forecast<\/strong> looks like:<\/p>\n\n\n\n
Here we can see the numbers that the user typed in for the orders forecast. This can be a manual or semi-manual process, by using advanced calculations in excel to extrapolate future orders from past orders.<\/p>\n\n\n\n
Here is the Orders Actual<\/strong>.<\/p>\n\n\n\n
In this sheet, Actual numbers need to be put. These numbers can come from a separate ERP system, such as SAP ERP<\/strong> or Microsoft Dynamics ERP<\/strong>, or for some businesses, it can be copied from the orders or invoicing system.<\/p>\n\n\n\n
Step 6: Incorporate Formulas into the Sheet in a Controlled Way<\/strong><\/h2>\n\n\n\n
The method described above can be used to consistently calculate any formula whose result arises from a calculation from two or more properties, provided for the same time period (monthly in our example).<\/p>\n\n\n\n
The forecast error is defined as:<\/p>\n\n\n\n
Forecast Error<\/strong> = (Orders Forecast<\/strong>) – (Orders Actual<\/strong>)<\/p>\n\n\n\n
and the absolute forecast error is defined as the absolute value of the above. It is easy to create another Tab that will hold the forecast error, like this (note the formula above):<\/p>\n\n\n\n
As it can be seen, each cell holds the same formula and can easily be replicated across the whole sheet. Excel will automatically adapt the cell locations when copy-pasting the formula across all cells.<\/p>\n\n\n\n
The forecast error is defined in all cells for all product-retailer combinations over all months.<\/p>\n\n\n\n
Note that this is the Signed Forecast Error. This forecast error is useful also for calculating aggregated forecast error across different categories of products. This can show us how good\/bad our forecast is regarding overall total number of products, regardless of specific product SKU. For example, we can see if our forecast matched the Actual in the total number of iPhones ordered.<\/p>\n\n\n\n
It is also useful to define the Absolute Forecast Error<\/strong>, as here:<\/p>\n\n\n\n
The absolute forecast error is a different indicator that considers positive errors to be as important as negative errors. Using this, the importance of out-of-stock<\/strong> is the same as the importance of over-supply<\/strong>. All errors are calculated and the absolute number is taken. This can be aggregated to calculate the Total Absolute Error of the forecast. <\/p>\n\n\n\n
Step 7: Incorporate Complex Formulas in a Simple Way<\/strong><\/h2>\n\n\n\n
By Creating more Tabs for [Manufacturing Cost] and [Price to Retailer], it is now possible to create formulas to calculate the Revenue and Profit.<\/p>\n\n\n\n
For the revenue see spreadsheet.<\/p>\n\n\n\n
Note the formula for the revenue. The revenue is defined simply as cost of product sold multiplied by the amount sold. <\/p>\n\n\n\n
For the profit, see spreadsheet.<\/p>\n\n\n\n
Note the formula for the profit. The profit is defined simply as cost of product sold minus the manufacturing cost, all this multiplied by the amount sold.<\/p>\n\n\n\n
The profit and revenue metrics can hold different information that helps in decision-making across the organization. By breaking down products according to revenue, it is important to see which SKUs bring in the most revenue. However, it is even more important to see which SKUs bring in the most profit.<\/p>\n\n\n\n
By having this structure duplicated across all sheets it is easy to calculate any formula that draws numbers from any of the sheets. It is easy and consistent to replicate it across all product-retailer-month combinations.<\/p>\n\n\n\n
It is also easy now, by arranging the data in an Excel table, to filter by any property value. <\/p>\n\n\n\n
For example here, we filter only the iPhone 11, so we can see how well it is doing across all Retailers.<\/p>\n\n\n\n
Step 8: Automate Forecasting, but Don’t Over-Automate if Not Possible<\/strong><\/h2>\n\n\n\n
Supply chain planning is difficult. There are many variables that need to be accounted for in the planning process. Due to this, the planning process is in many times manual or semi-manual. It is difficult to create an algorithm to account for the different effects such as special promotions, competitive landscape, etc. Therefore, our goal here is to simplify the manual planning process as much as possible, while not automating it completely. We will write about specific methods for forecasting within Excel in a different article (feel free to write to us if it\u2019s important to you, we\u2019re looking for new ideas for articles).<\/p>\n\n\n\n
For now, please note that forecasting can<\/strong> be automated, but only where it is indeed predictable. For example, if a change in price consistently shows a change in sales, we can formulate this and put the formula in the planning Excel sheets.<\/p>\n\n\n\n
Step 9: Embrace Collaborative Planning<\/strong><\/h2>\n\n\n\n
For a comparison of using Excel Desktop vs Excel Online please look here. Overall, our recommendation is to use Excel Desktop on a shared file in a shared drive. We believe this will allow for easier analysis by the financial analysts, that can use advanced features such as pivot-tables, pivot-charts, etc.<\/p>\n\n\n\n
Downloading the Example Excel File<\/strong><\/h5>\n\n\n\n
All the results and examples presented in this article can be downloaded here<\/a>.<\/p>\n\n\n\n
Feel free to contact us with any suggestions, issues, or requests for any help.<\/p>\n\n\n\n
Step 10: Find Other Processes that Can Be Automated<\/strong><\/h2>\n\n\n\n
This article is a part of a series of articles that explain other financial processes that may be automated at your company.<\/p>\n\n\n\n
- \n
- Sales Planning in Excel – here we’re focused on planning at the Weekly level, as is needed when doing sales planning for physical products sold at retailer\/resellers. It may be useful for other types of sales planning as well. We provide a method to put a list of retailers and list of products, and plan sales separately for each combination. We also provide formulas for things like forecast errors and other important metrics for Sales Planning.<\/li>\n\n\n\n
- Supply Chain Planning in Excel – in this article we provide a template for sales at the Monthly level, for supply chain forecasts and actuals, which include Orders and Orders Forecasts, Inventory and Inventory Forecasts, and provide formulas relevant to the supply chain.<\/li>\n\n\n\n
- Multi-Site and Multi-Currency Budget Planning in Excel – this article focuses on budget planning and analysis and is focused mostly on expenses reporting for the budget, explaining complex issues such as multi-currency and multi-site reporting, and shows how we recommend to build an Excel file for this use-case.<\/li>\n<\/ol>\n\n\n\n
Appendix: Is this Method Good for Your Company? <\/strong><\/h2>\n\n\n\n
This document is aimed at any company where supply chain planning is necessary, and therefore is geared more towards physical products. So if you are manufacturing any kind of physical goods, this document may serve you well, and help you organize your Excel sheets and plan properly for your supply chain.<\/p>\n\n\n\n
This document is also useful if your company is not a manufacturer, but a distributor, reseller or importer of goods manufactured by other companies. In that case, the factories are not owned by your company, yet all the important issues of supply chain, such as planning for different locations and different products, and calculating all the availability metrics, are still relevant. <\/p>\n\n\n\n
Comparison to sales planning: as said, there is a separate article as well as an example of an Excel file for Sales Planning. While Sales Planning and Supply Chain Planning carry many similar characteristics, it is important to note that we are not talking about the same process. In some companies, especially those selling to a small number of customers, and selling amounts that do not change much, there may be almost no need for sales planning. In other companies, sales planning may be a very important complement to supply chain planning. Also, in companies that do not sell physical goods, such as SaaS (Software-as-a-Service) software companies, or service companies, there may be no need to supply chain planning, but there is still a need for sales planning. So read the right document as it fits your company.<\/p>\n\n\n\n
Our suggestion is as follows:<\/p>\n\n\n\n
If you are selling physical products:<\/p>\n\n\n\n
- \n
- if you already have a tool for supply chain planning (such as an ERP Planning Module), you may keep using the same tool and not move your supply chain planning to Excel. If you need sales planning, read this<\/a>.<\/li>\n\n\n\n
- If you do not have a tool for supply chain planning, start with this document to organize your supply chain planning in Excel.<\/li>\n<\/ul>\n\n\n\n
If you are selling non-physical products, such as software or services:<\/p>\n\n\n\n
- \n
- You don\u2019t need supply chain planning<\/li>\n\n\n\n
- Start with this document to do sales planning properly – Sales Planning in Excel article.<\/li>\n<\/ul>\n\n\n\n
Also, please note that in our sales planning example we build everything around planning on a weekly basis, whereas here we plan monthly. Use what fits your organizations and processes in a better way.<\/p>\n\n\n\n
Conclusions<\/strong><\/h2>\n\n\n\n
While the system provided cannot ensure that no underlying mistakes in formulas are made in the Excel file, if everyone keeps faithfully adhering to it there is a good chance it will form a process and reduce overall mistakes and time spent on organizing Excel sheets. It will also increase the \u201cbelief in the numbers\u201d, as it\u2019s very important for the various stakeholders to be sure that what they are seeing is correct.<\/p>\n\n\n\n
If you need a helping hand in implementing this process in your company, or want us to send you an example Excel file, feel free to contact our team of experts. We also appreciate any feedback and questions in the comments below.<\/p>\n\n\n\n
\n\n \nWould you like to learn about hot new disruptive startups, and how they challenge the incumbents?\n<\/h1>\n\n\n
\nRead our free newsletter with in-depth analysis of disruptive startups, and how they ramp up against the competition.\n<\/h2>\n\n
\n
- If you do not have a tool for supply chain planning, start with this document to organize your supply chain planning in Excel.<\/li>\n<\/ul>\n\n\n\n
- if you already have a tool for supply chain planning (such as an ERP Planning Module), you may keep using the same tool and not move your supply chain planning to Excel. If you need sales planning, read this<\/a>.<\/li>\n\n\n\n
![](\"https:\/\/disruffle.com\/wp-content\/uploads\/2023\/12\/baced9a0-8ecd-4a62-8936-d3bbee928a65.jpg\")
\n<\/a>\n<\/div>\n