Re: Questions about inventory formulas

cashchris2005 · Feb 23, 2011

Re: Questions about inventory formulas

 Hello All,

I have a inventory question that I need help with.

Lets say cell:
A1=Starting Quantity in stock (Person Enters)
B1=Quantity in stock (Formula)
C1=Quantity used per day (person enters, then calculates, then cell clears)
D1=Quantity used per week (person enters, then calculates, then clears)
E1=Reorder Level (Person enters)
F1=Status (Condition formula)

P/E=Person Enters

<table class="MsoNormalTable" style="width: 428pt; margin-left: 4.65pt; border-collapse: collapse;" width="713" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 45pt;"> <td style="width: 62pt; border: 1pt solid rgb(120, 145, 110); background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="103">

[FONT=&quot]Starting Quantity[/FONT]

</td> <td style="width: 61pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="102">

[FONT=&quot]Quantity in Stock[/FONT]

</td> <td style="width: 73pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="122">

[FONT=&quot]Used Today[/FONT]

</td> <td style="width: 70pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="117">

[FONT=&quot]Used This Week[/FONT]

</td> <td style="width: 77pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="128">

[FONT=&quot]Reorder Level[/FONT]

</td> <td style="width: 85pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: rgb(120, 145, 110) rgb(120, 145, 110) rgb(120, 145, 110) -moz-use-text-color; background: none repeat scroll 0% 0% rgb(180, 195, 175); padding: 0in 5.4pt; height: 45pt;" width="142">

[FONT=&quot]Status[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="width: 62pt; border-width: medium 1pt; border-style: none solid; border-color: -moz-use-text-color rgb(175, 180, 135); background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="103" nowrap="nowrap"> [FONT=&quot]P/E[/FONT]
</td> <td style="width: 61pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="102" nowrap="nowrap"> [FONT=&quot]Formula[/FONT]
</td> <td style="width: 73pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="122" nowrap="nowrap"> [FONT=&quot]P/E then Clear[/FONT]
</td> <td style="width: 70pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="117" nowrap="nowrap"> [FONT=&quot]P/E then clear[/FONT]
</td> <td style="width: 77pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="128" nowrap="nowrap"> [FONT=&quot]P/E[/FONT]
</td> <td style="width: 85pt; border-width: medium 1pt medium medium; border-style: none solid none none; border-color: -moz-use-text-color rgb(175, 180, 135) -moz-use-text-color -moz-use-text-color; background: none repeat scroll 0% 0% white; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="142" nowrap="nowrap">

[FONT=&quot]Condition formula[/FONT]

</td> </tr> </tbody></table>

What I need formulas for are
1. Subtract quantity used per day(b1) or quantity used per week (C1) from quantity in stock (b1). Then I want the total of this (a1-b1 or c1) to replace the quantity in b1 as the new quantity in stock.

After the calculation is completed I am looking for a formula that then clears the cells (B1 or C1) for the next entry (the next day/week).

2. A status formula that will state when b1 (quantity stock) is above the reorder level (d1) the cell (e1) will be green and say In Stock, otherwise if a1 is less than or equal to d1 then state Need to order, and turn cell e1 yellow.

If anyone can help I would really appreciate it!!! I would even pay for your time invested if I need to!!

Thanks!!

pbornemeier · Feb 24, 2011

Re: Questions about inventory formulas

I would modify your request in post 1 as follows:

Inventory Totals worksheet:
Item ID, Starting Quantity, Quantity On Hand, Reorder Level, Quantity On Order, Status
All items that you were tracking would have an Item ID and Starting Quantity. The Quantity on Hand value would be a formula updated from values entered on the Usage worksheet. The Quantity on Order would be updated from values entered on the Orders worksheet.

Journal worksheet:
3 to 6 columns:
Date, Item ID, Quantity Used (and optionally By Whom, For What, and Comment depending on what level of auditing was required.)

This would be a log that tracked usage of all items and would provide a record of what was used when. The values on this sheet would then be processed by formulas on the Inventory Totals worksheet.

Orders worksheet:
3 to 6 columns:
Date, Item ID, Quantity Ordered (and optionally By Whom, For What, and Comment depending on what level of auditing was required.)

This would be a log that tracked ordering of all items and would provide a record of what was ordered when. The values on this sheet would then be processed by formulas on the Inventory Totals worksheet.

It might be useful to track the amount of time to deliver each item as that might cause you to order items based on predicted uasge so you always had all items is stock.

Reports worksheet that was a pivot table based on the Journal and Orders worksheets would show a breakdown by whatever calendar interval was desired.

The workbook is maintained for a business cycle (Fiscal Year or Calendar Year or something else) then the current balances are copied to a new workbook and start again.

Re: Questions about inventory formulas

cashchris2005

New Member

Excel Facts

pbornemeier

Well-known Member

Similar threads

Forum statistics

Share this page

Re: Questions about inventory formulas

cashchris2005

New Member

Excel Facts

pbornemeier

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock