Excel 2007 : Determine when to order parts based on parts used last year at the same time of the year.

I am attempting to create a spreadsheet that will tell me when to order a part in a manufacturing operation based on current inventory and usage one year ago. I have a list of the number of bottles used in each week of last year. My bottle usage is consistent year to year, but it changes at different times of the year. What I am looking for is for example; let's say that the date is July 1 and I have 5000 bottles in inventory and it takes 10 weeks to get more bottles - I need the spreadsheet to look at the listed data for weekly usage one year ago on July 1 and for the next 10 weeks. It will then look at that total number of bottles used one year ago to determine if I need to order yet. As today's date changes, It would look at the same starting date one year ago. I have numerous parts that I am looking at and they will all have different inventories and different lead times to get more stock.

Thank you for helping! This is my first post, so I apologize if I have made any errors.

How do I insert an example workbook?

I think that I got it uploaded?? Let's just look at line 7 for this discussion; I am looking for something similar to this except that instead of using Unit Sales (D6) and the number of months in sales (E2), I would like it to look elsewhere where I would list the number of bottles used in each week of last year.



Gordon's Body Care Inventory Management.xlsm

Sorry, I meant to say Unit Sales (D7).

I am trying to create a new formula. I think that the newly attached will better indicate what I am looking for. The cells in red are what I am having problems with.

Thanks again, I really appreciate it!