Inventory caculator

coryh83

New Member
Joined
Jul 5, 2016
Messages
10
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
BCDEFG
1Order QTY20
2Safety Stock50%
3Lead Time4
4Weekly Usage10
5Min 60
6Max80
7
8
9
10
11
12
13Inventory On OrderUsage CycleOn OrderUsage Cycle
14Starting 80
15Week170
16Week2602080
17Week35070
18Week440602080
19Week5305070
20Week6204060
21Week7103050
22Week802040
23Week91030
24Week10020
25Week11
26Week12
27Week13
28Week14
29Week15
30Week16
31Week17
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 2019
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.
 

Forum statistics

Threads
1,176,168
Messages
5,901,732
Members
434,914
Latest member
mabek

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
Top