Monthly Projections formulas?

aceee

Board Regular
Joined
Feb 21, 2008
Messages
239
hi all.
More of a math problem than an excel problem.

Hypothetical..

Company A is in sales.
Everyday they log sales, and pending sales
So lets say.. at the 5th of the month, they have sold $50,000 worth of product, and have another $10,000 'as good as sold'
i'm trying to work out how to predict how much they will end up selling in total..

so far i have this (not sure if its even correct??)


A1 - 50,000 - B1 - Sold
A2 - 10,000 - B2 - Pending sales
A3 - 5 - B3 - Days into month
A4 - 10,000 - B4 - Sold per day
A5 - 20 - B5 - Days left in month
A6 - =(A4*A3)+A1+A2


would this seem like the correct way to do this?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Here are some considerations:

1. Have a planned sales rate to use as a starting point. It can either be linear over the month (if they are going to sell 100 widgets in a month, and you allow for 20 workdays, then they should sell 5 widgets a day), or you can be more exotic and show anticipated patterns across the time frame (for example, if most sales occur in the last week of the month, your model can show that). The plan data will be static.

2. Your forecast (dynamic) will be an adjustment against the plan (static).

3. You need to categorize sales as either expected, or unique "one-offs". Expected sales above or below the plan will modify the slope of the forecast line, while the one-offs will raise the y-intercept of the forecast line.

4. Consider if you want to "throttle" your forecasting algorithm, until you have enough data. Using the above example of 5 widgets a day, if on the first day of the month you sell 20 widgets, are you really confident forecasting that you will come in at 4 times the plan at the end of the month? So, you may want to use the assumption for the first 10 days that the forecast is the plan (independent of what the actual data tells you), days 11-20, the slope of the forecast is constrained by preset limits (so that the first 10 days of actual data indicates that you will be above or below plan), and then the last 10 days of the month, you have an unconstrained forecast, since you will have 20 or more days of data to base it on.

Confused yet?
 
Upvote 0
wow thats confusing hehe

1. Have a planned sales rate to use as a starting point. It can either be linear over the month (if they are going to sell 100 widgets in a month, and you allow for 20 workdays, then they should sell 5 widgets a day), or you can be more exotic and show anticipated patterns across the time frame (for example, if most sales occur in the last week of the month, your model can show that). The plan data will be static.
Having a planned rate of sale is a seporate sheets.. IE we have a "you should have sold X by X day of the month" and want to run graphed beside it as a "You are on track to sell Y"

3. You need to categorize sales as either expected, or unique "one-offs". Expected sales above or below the plan will modify the slope of the forecast line, while the one-offs will raise the y-intercept of the forecast line.
In this instance, there are no 'One-offs'

4. Consider if you want to "throttle" your forecasting algorithm, until you have enough data. Using the above example of 5 widgets a day, if on the first day of the month you sell 20 widgets, are you really confident forecasting that you will come in at 4 times the plan at the end of the month? So, you may want to use the assumption for the first 10 days that the forecast is the plan (independent of what the actual data tells you), days 11-20, the slope of the forecast is constrained by preset limits (so that the first 10 days of actual data indicates that you will be above or below plan), and then the last 10 days of the month, you have an unconstrained forecast, since you will have 20 or more days of data to base it on.

Yup very confused hehe
 
Upvote 0

Forum statistics

Threads
1,224,614
Messages
6,179,906
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top