S&OP Excel Template Series: Demand vs. Supply

------------

Thank you for your interest in DemandCaster.

The Excel template referenced in this blog is no longer available but click here to learn how the DemandCaster software system can be used to support your S&OP process. And to learn more about S&OP, refer to these articles What is S&OP? and Implementing Sales and Operations Planning.

Contact Us if you would like to speak to a DemandCaster representative.

------------

Here at DemandCaster we are truly humbled by the incredible amount of positive feedback we received after the first post in our S&OP Excel Template Series. If you missed our first template, Forecast Consumption click on the link to check it out.

I am really excited about releasing this next template, Demand vs. Supply (sign up form for download is located at the top of the side-bar). While I have said in the past, not all templates will be useful to every company, this one I would consider the exception. I can easily say that the reports in this template would be in the top 3 of the most important reports to the S&OP Process. Demand and Supply is something we start familiarizing ourselves with in grade school, it's usually the first thing people say when you mention taking an economics class "Oh yeah, Supply and Demand."

This template provides a simple cross-functional view of one of the key themes of sales and operations planning, your demand and its relation to your inventory position as well as what you still need to spend on more inventory. This template is a very powerful source for the executive team to quickly evaluate many factors of the business. This chart becomes even more useful for companies trying to deal with inventory issues. Before we get into the details of the Demand vs. Supply template I wanted to take a moment to mention DemandCasters new Custom Reporting feature, this new feature is what inspired the DemandCaster team to introduce the S&OP Excel template series.

S&OP Excel Template series brought to you by DemandCaster

DemandCaster Custom Reporting is a report engine that leverages Microsoft Excel. It allows users to pull all the data that exists in DemandCaster and use that data to create custom reports and dashboards using user-designed templates that can produce an array of outputs, from graphs to pivot tables and allows for the inclusion of macros. The result is your Excel dashboards can be downloaded out of the system at any time with the most up to date data without the fear of any calculation or link breaking. In addition, the data can be posted to other data tables on a scheduled basis allowing users to use services such a Tableau or other reporting services. The report may also be emailed to dedicated users on a scheduled basis. You can find out more information on custom reporting by Clicking Here. Below are a few slides to show you how quick and simple this feature is to use and set up with your Microsoft Excel templates.







Demand vs. Supply Template

This template was designed to be user-friendly and contains a detailed set of instructions, preview below:















The purpose of the this Spreadsheet is to give a visual perspective to the S&OP process concerning the next 12 months demand plan compared against starting inventory for the month along with the projected build requirement. The build requirement is based off the current inventory position, demand plan, and set safety stock. The end result provides the business with a high level view of the company’s inventory position monthly over the next 12 months. This report can serve as a high level view to evaluate if there are major issues concerned with inventory not remaining at an ideal level with increased demand. This Template contains FOUR REPORTS; two of which are based off the overall number, one in units and one in dollars, and two are based off a specific item that can be selected from a drop down menu with one showing units and the other showing dollars.

Supply vs. Demand Report #1: Overall Demand vs. Supply Units Report

Once all of your inputs are in, the chart will show a 12 month rolling view in units of your monthly forecast, the beginning inventory for the month, and what your projected build requirement is.

Supply vs. Demand Report #2: Overall Demand vs. Supply Dollars Report

Once all of your inputs are in, the chart will show a 12 month rolling view in dollars of your monthly forecast, the beginning inventory for the month, and what your projected build requirement is.

Supply vs. Demand Report #3: Demand vs. Supply Units by item

Once all of your inputs are in, select the item from the yellow highlighted cell ( Drop-Down Menu) the chart will show a 12 month rolling view in units of your monthly forecast, the beginning inventory for the month, and what your projected build requirement is.

Supply vs. Demand Report #4: Demand vs. Supply Dollars by item

Once all of your inputs are in, select the item from the yellow highlighted cell ( Drop-Down Menu) the chart will show a 12 month rolling view in dollars of your monthly forecast, the beginning inventory for the month, and what your projected build requirement is.

Required Data:

List of all products

Unit forecast by product

Current Inventory by product

List of cost for each product

Sale price for each product

The set stocking level for each product if it has one

The directions can be found in the second tab of the Spreadsheet titled “Directions” to download this Microsoft Excel Template please fill out the sign up form below and the file will be emailed to you.

Final Thoughts

We hope you find this Microsoft Excel template useful. To receive a copy of this and our other 3 S&OP templates, please complete the subscription form at the top right-hand sidebar of this page.

If you have any questions or would like more information on what we have to offer at DemandCaster, please feel free to contact us, we are always happy to hear from our readers.