Inventory caculator

[coryh83]

I know this is a longshot but I could really use some help here

I have been requested to create a caculator that will take weekly usage, lead time, order QTY and show visually the stock movements

Economic-Order-Quantity-Template.xlsx
	B	C	D	E	F	G
1	Order QTY	20
2	Safety Stock	50%
3	Lead Time	4
4	Weekly Usage	10
5	Min	60
6	Max	80
7
8
9
10
11
12
13		Inventory	On Order	Usage Cycle	On Order	Usage Cycle
14	Starting	80
15	Week1	70
16	Week2	60	20	80
17	Week3	50		70
18	Week4	40		60	20	80
19	Week5	30		50		70
20	Week6	20		40		60
21	Week7	10		30		50
22	Week8	0		20		40
23	Week9			10		30
24	Week10			0		20
25	Week11
26	Week12
27	Week13
28	Week14
29	Week15
30	Week16
31	Week17
Sheet1

This is a very rough example of kinda what i am talking about but it needs to be all caculeted using formulas. I am completly lost on how to get this started but i know there are much greated minds here then mine. This shows week to week how the weekly usage affects inventory and when it goes below the min it will kick out an order based on the Order quantity. Our system will then show how many we have on hand and on order and it wont kick out another order till that combined number falls below the min again. I am sorry if i am having a hard time explaining myself
But I would like to show one column that will show what the actual inventory is and keep falling untill the order is recieved(based on the lead time). The next colomn would show what the sytem thinks there is based on actual inventory and whats on order.

Thank you for anyhelp that i can get with this

 

[RayFrye]

I would suggest that you chop your requirements up into tiny pieces. This will allow you to think thru your requirements and someone here to get a handle on where you start, what happens in the middle (multiple steps) and how you might end the effort before the cycle starts over. I think most folks here want to help but many of us are programmers and we think in terms of formulas, forms, key strokes, subroutines and error processing. When we think about Excel apps our heads explode until we get the app to subroutines, then we can do our thing. I could, and mostly will, make this comment for a number pf posts here. Just a thought.