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

[Sjw185]

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.

	Anticipated Customer Footfall by day	Stock by Day		Order Volumes
	13.0%	12.0%	13.5%	13.5%	16.0%	19.0%	13.0%	13.0%	12.0%	13.5%	13.5%	16.0%	19.0%	13.0%		13.0%	12.0%	13.5%	13.5%	16.0%	19.0%	13.0%
SBO forecast in cases	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
6	0.78	0.72	0.81	0.81	0.96	1.14	0.78	6	5.28	4.47	3.66	2.7	1.56	0.78
1	0.13	0.12	0.135	0.135	0.16	0.19	0.13

<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> </tbody>

 

[wideboydixon]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		ACF							SBD								OV
2		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%		13.00%	12.00%	13.50%	13.50%	16.00%	19.00%	13.00%
3	SBO forecast	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
4	6	0.78	0.72	0.81	0.81	0.96	1.14	0.78	6	5.22	4.5	3.69	2.88	1.92	0.78		1	1	1	1	1	1	0
5	1	0.13	0.12	0.135	0.135	0.16	0.19	0.13	1	0.87	0.75	0.615	0.48	0.32	0.13		1	0	0	0	0	0	0
6	10	1.3	1.2	1.35	1.35	1.6	1.9	1.3	10	8.7	7.5	6.15	4.8	3.2	1.3		2	1	1	2	1	3	0
Sheet1

Cell Formulas
Range	Formula
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

 

[Sjw185]

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

 

[Sjw185]

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