0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.