# excel spreadsheet to measure monthly inventory demand

I am trying to create spreadsheet to measure monthly inventory demand. Example: 2 orders received every month from Jan 2016 to Apr 2016. each order requires 5 items to be shipped per month for 3 months.

Jan 2016 - 2 Jan orders received x 5 items = 10 items needed

Feb 2016 - 2 Jan orders received x 5 items + 2 Feb orders received x 5 items = 20 items needed

Mar 2016 - 2 Jan orders received x 5 items + 2 Feb orders received x 5 items + 2 Mar orders received x 5 items = 30 items needed

Apr 2016 - 2 Feb orders received x 5 items + 2 Mar orders received x 5 items + 2 Apr orders received x 5 items = 30 items needed

May 2016 - 2 Mar orders received x 5 items + 2 Apr orders received x 5 items = 20 items needed

I can do this the long way with bunch of formulas and it takes a long time to calculate. I wonder if there is a simpler way to do this.

Thanks.

• For something that seems this constant and this repetitive of a task I would recommend using some sort of Database Application that can house all of this information. – user2676140 Jul 14 '16 at 19:16
• Thank you. I think excel is the only resource I have. I was looking to see if I missed a smarter way of doing this. – Andrew Lee Jul 14 '16 at 20:29

Assuming your order duration is always the same, and your data is listed as below :

Month   |   Order Count   |  Item Count | Monthly Inventory Demand
=====================================================
Jan     |        2        |        5    |
Feb     |        3        |       11    |
Mar     |        4        |        8    |
...

You could fill the last column with :

=SUMPRODUCT(B[n-2]:B[n],C[n-2]:C[n])

for all rows beyond three. The first two rows would need to have different sum to reflect the absence of a prior three months to reference.

• Unfortunately, order count per month can vary. Order duration (3) will always be the same. – Andrew Lee Jul 14 '16 at 20:28
• My answer assumed that the order count would be different for each month, you would simply place that number in the Order Count column for the given month. I modified the answer to give additional flexibility, in case your Item Count per Order is also changing between months. – Andrew Jul 15 '16 at 0:29