Daily projection based on historical percentages

MDL1323

New Member
Joined
Oct 6, 2015
Messages
1
I'm looking to get a formula for end of day projection for order lines based on historical percentages for each of our hourly order pulls. Below is an example:

6am pull - 100 lines (historically 50% of daily volume)
7am pull - 50 lines (historically 25% of daily volume)
8am pull - ?? (historically 15% of daily volume)
9am pull - ?? (historically 10% of daily volume)
What is the end of day line projection?

Assuming the percentages are stored in cells A1:D4

Would anyone please be able to help me out with this?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi there. I assumed you had a table set up with Hour of day in A1, Historical % in B1, Actual Lines in C1, and Estimated lines in D1 as headers. Actual lines you can type in each time after your pull, the hour of day plus historical percentages are set to your defined amounts.

In the estimated lines column, cell d3 - use an IF statement: IF(C3 = "", $D$7 * B3, "") and send it down.

Below the table, in Cells C7 sum the actuals. In D7 this your end of day estimate and where another if statement was used looking to see if actuals was blank for the hours and if not create an estimate
Here is the formula for D7: If(AND(C5="", C4="", c3=""), C2/B2, IF(AND(C5="", C4=""), (C2+C3)/(B2+B3), IF(C5="", (C2+C3+C4)/(B2+B3+B4), "")))

The formula set up this way will allow it to still work if the historical percentages in your table change or you make them dynamic.
Reach out if you have any questions or it is not clear. This is my first attempt helping out.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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