Quantity Demanded Vs. Actual: A Variance Model

This is a great tool for any excel user to use in order to show the historical amount of something that has been ordered by week, apply a growth % to it, and then see how that figure compares to your current figures over time as a % variance by total weekly quantity demanded and by product id.




Pricing
You will be taken to a download page for this template after purchase.

I have tried to make the input data and function as simplistic as possible so it will fit as many different scenarios without much tweaking. Many more product id's could be added if needed or you may not have any and that would be fine.

The main user inputs will be last year's amount of items sold, date of the sales, and product id. You may be manually putting this information in or using a data dump. Either way the functionality easily supports both.

Based on those historical figures, the data auto summarizes into a nice 52 week schedule that shows total items sold per week and total items sold per week by product id.

You then have the same data input functionality for current quantity demanded (basically sales count by product id).

As the data bases for last year / this year get populated, you will see a variance % schedule that shows how you are doing in terms of the projected figure. The forecast is based on last years count after a % increase or decrease is applied to the year.

This is a bit like budget vs. actual except the only thing we are dealing with is the amount/count of items sold vs. what is projected to be sold on a weekly level.

There is also a visual to show the weekly total actual vs. weekly total projected.

One final tab is the query tab, which gives the user a nice snap shot of the data. You can pick from a drop-down the week #, product id, and if you want to see the projected figure or actual figure. Based on that the data point will populate accordingly for total week and individual product id.

No comments:

Post a Comment