skip to Main Content

Free S&OP Excel Template Series: Forecast Consumption Template

Best Practices  
05/13/2015

Here at DemandCaster, we are excited to announce our new blog series focused on different Microsoft Excel templates we will be giving away for free. The new blog series is inspired by the launch of our new custom reporting feature in DemandCaster supply chain planning software.

Below you will find a short introduction of the Custom reporting feature followed by our first Microsoft Excel template download.

Introducing DemandCaster’s New Custom Report Capability

When it comes to the reporting side of Sales and Operations Planning (S&OP), many of us struggle with presenting just the right amount of information in a chart without making it overly complex and hard to understand. There are many useful dashboards and reports that are important for companies in the S&OP process, however, some may not be useful for every company.

But first, a little history.

Over the last number of years, we have tried to understand how best to help our users address the multitude of reporting needs they have. At first, we wanted to pursue the route of services such as Tableau which provide impressive data visualization options. Upon evaluation, it became clear that services such as those provided by Tableau are a category in and of themselves and it did not make sense to replicate what others provide as their core capabilities. After talking to many of our users, we realized the best solution is to leverage a tool they already use every day – Microsoft Excel! Excel is remarkably powerful and ubiquitous tool that nearly everyone has on their desktop. With this realization, we have spent nearly a year developing what we call Custom Reporting built into the DemandCaster platform.

DemandCaster Custom Reporting is a report engine that leverages 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.

We are really excited about this new capability and will be providing a series of blog posts and webinars that will help introduce the new capability with real-life examples. Our custom reporting is just another way we are continuing to provide valuable demand and supply planning tools and services to ensure we exceed our client’s expectations.

Introduction to the Free Excel Templates

Before we get into the first Free Excel template, it’s important to note a few key qualities in the Excel templates that truly make them powerful tools.

Microsoft Excel Template

1. One of the most important qualities is the overall ease of use, which directly ties to the core values of DemandCaster, focusing on user-friendly software and resources. Each spreadsheet contains an introduction and directions tab which simply guides you through dropping the data in the designated spot, the image to the right is a clip of the directions tab. You can use the template even without a DemandCaster subscription.

2. With regards to building Excel dashboards and templates, we have a very strict policy to build the templates to automatically update with time without making technical adjustments.

-a simple example of this would be the Forecast Consumption Excel template below, which is based on the current month. In the first data input tab is a drop-down menu for selecting the month and year, once the user sets the month and year, the entire spreadsheet will update to reflect the user-defined time.

The philosophy we use when building our templates is to put in a little extra work in the front end to keep the templates automated and more importantly take away as much possibility of compromising the overall integrity of the template with tedious updates that must occur to keep up with time. This philosophy has become even more important and valuable to the DemandCaster team and our clients as we launched our custom reporting feature (mentioned above), meaning once a fully automated template is built and loaded into the custom reporting feature and the data points have been mapped in the template, no additional maintenance is required. The template will always be updated with the most recent information at the click of a button.

With the above in mind, below is the first of our free Excel templates available to DemandCaster users and non-users alike.

Forecast Consumption Template

S&OP Excel TemplateThis Excel template was designed to help you track your forecast inside the month. The template contains TWO REPORTS; a Product Consumption and an Overall Forecast Consumption report. The purpose of these reports is to keep a close eye on how you are tracking to forecast inside your planning horizon. While there maybe little that can be done concerning inventory, these reports can help companies quickly notice if sales are not coming in as planned, which can quickly alert the executive team to implement some type of last minute promotion to avoid excess inventory.

You see two types of comparisons to forecast in the reports:

1. Total Demand: Total Demand = (Open Orders + Shipments)

- This is where the sales team usually wants to track performance, which is based off the total demand, which is the sales function.

2. Actual's: Actual's= (Shipments)

- This is usually where finance is tracking to, as it is looking at the reality of what has actually occurred and is considered recognized revenue.

Forecast Consumption Report #1: Product Consumption Report

S&OP Excel Template

-The yellow highlighted cell is a drop down menu of every product.

-Once you select a product from the drop down menu, multiple charts and notes will be populated with information regarding the product.

- The charts will give you units and dollars for total demand and units and dollars for actual's.

- In the note section you will be able to view how the product ranks against the other products as well as how much demand the product represents in total. The projected sales price is also given. A comparison in both Total Demand and Actual's is done showing the variance of ranking, % of total revenue, and ASP.

Forecast Consumption Report #2: Overall Consumption Report

S&OP Excel Template

- The charts will give you units and dollars for total demand as well as units and dollars for actual's.

- In the notes section you will be able to see the Total Demand and Actual's Weighted Variance, the Weighted Variance is taking into account the product mix and how much from each product is still outstanding

Required data:

  • Product List
  • Current month’s forecast (Units)
  • Current month’s forecast (Dollars)
  • Open orders due in month (Product, units, and dollars) Note: Include late orders if needed
  • Current month’s shipments (Product, units, and dollars)

The directions can be found in the second tab of the Spreadsheet titled "Directions."

To request a copy of this Microsoft Excel Template, please complete the Contact Us form and indicate your interest in the Forecast Consumption Template.

Final Thoughts

We hope you find this Microsoft Excel template useful. 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.

Related Articles

Metric of the Month: Raw Materials Inventory Turns

Delivering goods and products begins with raw materials. Those materials vary widely by industry and manufacturing process, but all must be sourced and managed.

Master the Basics of Cross-functional Alignment

Have you ever produced a forecast but weren't told about a last-minute campaign or marketing activity that went on to drive sales far higher than anticipated? If you ended up taking the blame for the company not having enough inventory to meet [...]

Are millennials taking over the supply chain?

The way you get a cup of coffee, cook a meal at home and even purchase clothing is changing. What once was a $3 cup of coffee is now $15 with Starbucks Reserve. Meals are planned ahead of time with pre-packaged [...]

The 7 essential Excel tricks every Excel user needs to know

Here at DemandCaster, we use Excel every day. In fact, the original version of DemandCaster was built using Excel and a number of advanced Excel tricks. All DemandCaster reports are defaulted to output as Excel and Excel can be used to easily prepare [...]

Free S&OP Excel Template Series: Budget vs. Plan

The next free S&OP Excel Template is here!!! The fourth release in the Free S&OP Excel Template series is an important report that helps evaluate Budget vs. Plan. One of the main questions the executive team will always ask in [...]

Free S&OP Excel Template Series: Sales Forecast Financial Overview

It's that time again! The third Free S&OP Excel Template in the series is here! Everyone here at DemandCaster is thrilled with all  the positive feedback we have been receiving and an unprecedented amount of followers to this series. I am humbled to [...]

Free S&OP Excel Template Series: Demand vs. Supply

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

In Excel We Trust! It Depends…

There is no question that Microsoft Excel is one of the most popular business tools used throughout the world. The power of Excel is extraordinary and while many skilled users are able to make it perform complex data management tricks, [...]

Four Strategies to Help You Meet the New Normal, Head-On

Back in January we posted our Supply Chain Resolutions for 2011. In our March newsletter, we shared our four improvement strategies for the remainder of 2011. We are posting them here for those readers that do not receive our monthly [...]

Policy vs. Common Sense

My good friend Guillermo Fernandez, the retired General Manager of Colgate-Palmolive Mexico, would always say: "Common sense, the most uncommon of all the senses." Here is a true story to exemplify this very point. Way back when, I worked for [...]
Subscribe to our Blog
Back To Top