Maths/ formula Potential required stock based on daily footfall and current store stock forecasted percentages

S

Sjw185

New Member
Joined
Feb 14, 2018
Messages
5
Hello,

I apologise if I've done anything incorrect in the posting of this thread. I've lost all sense after trying to do this:

I need to know that based on a footfall percentage (that will increase/ decrease with our forecasted estimates) and current cases in branches (based on what the footfall will consume, percentage also increasing/ decreasing per day), how much stock I would need to get in, by day to accommodate sales. I need to keep it tight and not over or understock.

We have forecasted 6 cases a week sales, so it cannot go over this. So the end table needs to equal 6 cases total by week end.

The footfall is calculated by multiplying the forecast % by the number of cases.

The stock in branch is calculated by subtracting the previous day's stock and multiplying the current day's forecast % and multiplying it by the 6 cases.

There would be a delivery available every day bar Sunday, so Saturday stock would need to cover it. Is this something you could please help with?

Thank you in advance!

I have created the table per the forum instructions but I think... I can't think anymore.
[TABLE="width: 1218"]
<colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" span="7"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="18" style="width: 14pt; mso-width-source: userset; mso-width-alt: 658;"> <col width="64" style="width: 48pt;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="64" style="width: 48pt;" span="4"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody>[TR]
[TD="class: xl70, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl77, width: 448, bgcolor: transparent, colspan: 7"]Anticipated Customer Footfall by day [/TD]
[TD="class: xl77, width: 603, bgcolor: transparent, colspan: 7"]Stock by Day[/TD]
[TD="class: xl65, width: 18, bgcolor: transparent"] [/TD]
[TD="class: xl77, width: 484, bgcolor: transparent, colspan: 7"]Order Volumes[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]12.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.5%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.5%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]16.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]19.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]12.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.5%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.5%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]16.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]19.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]12.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.5%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.5%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]16.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]19.0%[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]13.0%[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 67, bgcolor: transparent"]SBO forecast in cases[/TD]
[TD="class: xl69, bgcolor: transparent"]Monday[/TD]
[TD="class: xl69, bgcolor: transparent"]Tuesday[/TD]
[TD="class: xl69, bgcolor: transparent"]Wednesday[/TD]
[TD="class: xl69, bgcolor: transparent"]Thursday[/TD]
[TD="class: xl69, bgcolor: transparent"]Friday[/TD]
[TD="class: xl69, bgcolor: transparent"]Saturday[/TD]
[TD="class: xl69, bgcolor: transparent"]Sunday[/TD]
[TD="class: xl69, bgcolor: transparent"]Monday[/TD]
[TD="class: xl69, bgcolor: transparent"]Tuesday[/TD]
[TD="class: xl69, bgcolor: transparent"]Wednesday[/TD]
[TD="class: xl69, bgcolor: transparent"]Thursday[/TD]
[TD="class: xl69, bgcolor: transparent"]Friday[/TD]
[TD="class: xl69, bgcolor: transparent"]Saturday[/TD]
[TD="class: xl69, bgcolor: transparent"]Sunday[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]Monday[/TD]
[TD="class: xl69, bgcolor: transparent"]Tuesday[/TD]
[TD="class: xl69, bgcolor: transparent"]Wednesday[/TD]
[TD="class: xl69, bgcolor: transparent"]Thursday[/TD]
[TD="class: xl69, bgcolor: transparent"]Friday[/TD]
[TD="class: xl69, bgcolor: transparent"]Saturday[/TD]
[TD="class: xl69, bgcolor: transparent"]Sunday[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.78[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.72[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.81[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.81[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.96[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.14[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.78[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5.28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4.47[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3.66[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2.7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1.56[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.78[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.13[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.12[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.135[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.135[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.19[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.13[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
I don't know that I've got your requirements 100% right but I think you're looking to order minimum stock to cater for forecast sales? Would this work?


Book1
ABCDEFGHIJKLMNOPQRSTUVW
1ACFSBDOV
213.00%12.00%13.50%13.50%16.00%19.00%13.00%13.00%12.00%13.50%13.50%16.00%19.00%13.00%13.00%12.00%13.50%13.50%16.00%19.00%13.00%
3SBO forecastMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
460.780.720.810.810.961.140.7865.224.53.692.881.920.781111110
510.130.120.1350.1350.160.190.1310.870.750.6150.480.320.131000000
6101.31.21.351.351.61.91.3108.77.56.154.83.21.32112130
Sheet1
Cell Formulas
RangeFormula
B4=$A4*B$2
C4=$A4*C$2
D4=$A4*D$2
E4=$A4*E$2
F4=$A4*F$2
G4=$A4*G$2
H4=$A4*H$2
I4=$A4
J4=I4-B4
K4=J4-C4
L4=K4-D4
M4=L4-E4
N4=M4-F4
O4=N4-G4
Q4=ROUNDUP(SUM($B4:B4),0)
R4=ROUNDUP(SUM($B4:C4),0)-SUM($Q4:Q4)
S4=ROUNDUP(SUM($B4:D4),0)-SUM($Q4:R4)
T4=ROUNDUP(SUM($B4:E4),0)-SUM($Q4:S4)
U4=ROUNDUP(SUM($B4:F4),0)-SUM($Q4:T4)
V4=$A4-SUM($Q4:U4)


WBD
 
Upvote 0
Thank you ever so much for replying.

I am currently at work and unable to test that out right now.

If it makes any difference/ more sense:

the last table in my table (order volumes) needs to provide the amount of stock required based on the footfall and the stock left in branch.

The amount we have forecasted to be sold in total for the week is 6.

So as the week goes on the footfall changes and the stock in store will naturally change with it.

I need to know, based on the footfall and current stock fluctuations how much stock is needed up to the value of 6/ to fulfil the 6 case weekly demand and not overstock the store.


I'll test your current formula as soon as possible, I'm on a deadline to get this sorted :(
 
Upvote 0
Thank you for your help. I managed to cobble something together in time :)

I'm now embarking on VBA to see if I can get a better solution with that. Thank you again for helping :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Time Percentages
Replies
1
Views
909
James006
James006
S
  • Question Question
NETWORKDAYS formula
Replies
3
Views
944
Silverscreen
S
A
  • Question Question
Find Week start and End Date from any sepecific date of week required in Excel and MS Access.
Replies
2
Views
2K
ashfaqahmedr
A
D
  • Question Question
Source Selection based on multiple criteria in a Cycle Time calculation
Replies
0
Views
705
DLAMurph
D
R
  • Question Question
Require formula
Replies
2
Views
591
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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