Free Microsoft Excel 2013 Quick Reference

Weeks Cover for Inventory

Hope some can help - My problem is I wish to write a custom function which I can Use to calculate the weeks cover of current inventory based on a sales forecast. the forecast is in weekly buckets so I was thinking using some form of loop statement within the function which basically starts with the inventory figure then subtracts the forecast week by week until the inventory figure is less than the weekly forecast. I should then be able to divide the inventory by forecast and add back the number of loops to give me the weeks stock figure.

I have done this using if statements but it will only allow me to use 10 statements in the function and I need to do this for a full years worth of forecast.

Does anyone have a suggestion as This problem is becoming quite frustrating.

Here's an example of how the spreadsheet looks

Forecast103050205040Inventory 20019016011090400Weeks Cover6.

Any help would be greatly appreciated.


Post your answer or comment

comments powered by Disqus
I need to calculate inventory forward weeks cover for the coming months. Based off of recent forecasts, I have the month end inventory positions and the Cost of Goods (cogs) by week also.

Can someone guide me on how to use a formula to calculate the forward weeks cover (i.e. how many forward weeks cogs can fit into the month end inventory position).

Hopefully I've explained myself correctly.

Thanks in advance.

I'm trying to calculate the number of weeks cover an inventory value
represents. My worksheet is laid out as follows:
Each Row represents a week
Col A Col B Col C
W/ending Inv Demand Qty Weeks Cover
1095 44 15.1
1046 64 14.3
984 62 13.3
922 62
863 59
806 57
1019 57
962 57
1034 72
939 95
844 95
750 94
656 94
593 63
514 79
435 79
357 78
265 92

The values in the weeks cover (Col C) need to be a calculation based on the
value in column A using the demand from column B to work out how many weeks
the on hand inventory will last.
The first 3 values are what I expect the function to return in these cells.
I assume this will require an array and to be honest I'm struggling with the
Can anyone help me with the correct function to calculated this.

Can anyone help? I can't think of a way to do this in a single cell

I have a column of week ending dates next to weekly sales next to end
of week stock holding and I need to add a column that shows how mnay
weeks the stock will last for providing I buy no more stock. Rough
example below.

Week Sales Stock Cover
1 150 500 3.2
2 150 500 2.8
3 150 700 3.6
4 150 800 5.0
5 250 800 5.?
6 200 700 ?
7 150 600 ?
8 100 500 ?
9 100 500 ?
10 100 500 ?

I hope that helps. It need to take forward sales off until it get to a
part week and then work out the fractions like. 500-(150-150-150)/250
= 3.2 weeks cover. I hope this is clear... I just need a while
statement but alas Excel does have that and I can't figure a complex
way to use If conditions...

Hi Guys

Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year.

eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.


I want to calculate week number for a month based on a date and the weekday will start from Friday.
Suppose the date is 19/08/08 then the week will be 3
If date is 22/08/08 then week shall be 4
If date is 04/09/2008 then week shall be 1
If date is 11/09/2008 then week shall be 2

I tried to get the result by googling but not found any appropriate result.

Can some expert provided the formula ?
Thanks in advance.

Is there a formula i can use to return the work week for a date - starting at week 1 for the first week of january and so on?

Is there a weekly timesheet for excel somewhere?

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

I reference above my original post and the great assistance I received. The trouble is that despite that assistance being appreciated very much, because of my poor definition of my problem, I still have an issue. I would be really really grateful if someone could help. I hope that I have outlined my requirement more clearly below.

Thanks very much

Looking for some more help from Squiggler47.

Hello Squiggler
Thanks for your reply on my last thread. You wrote a custom function for me (I attach the workbook). The trouble is that I didn't clearly explain my problem so the solution doesn't quite meet my needs (although I am very grateful for it).

The function is definitely along the lines of what I was looking for but my issue is as follows (using my workbook as reference)
In I5 I need the cover for inventory in I4 (29156) to be based on non sequential cells.
So I want the coverage formula in I5 to calculate the inventory figure of 29156 over cells K4, M4, O4, Q4, S4 etc.

Using the actual data in my file then would see the following result in I5

29156 start
less 8222 = 20934
less 6451 = 14483
less 5990 = 8493
less 5719 = 2774
less 2000 = 774
774/6000 = .129
Result 5.129

Thanks again.

Is there a weekly timesheet for excel somewhere?

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.

Column F, Row 7 (This will be a new/different number entered every day)
Column G, Row 7 (Begins with a starting total of 2545)

I need Column G to add to it whatever number is entered into Column F, Row 7 and to keep the new total (ex. enter 10 into Column F/Row 7 & the total of Column G/Row 7 becomes 2555; if I then enter 20 into Column F/Row 7, Column G/Row 7 adds 20 to the original 2545, not the new total of 2555).

Column G is not keeping the new total, it reverts back to the original number of 2545.

Can anyone help with this? I don't even know if it's possible to work out this way.


I plan every week MRP type spreadsheet that plans out 52 weeks into
the future. My problem is that I have a closing inventory figure at the
end of every week and I need to express that was weeks inventory cover.
The planning grid below will illustrate this

Forecast Sales 100 80 90 110 120
Planned purchases 50 100 50 150
Inventory 160 110 130 90 130 10
Weeks cover 1.75 1.33 1.36 0.82 infinite

I would be grateful if someone can point me in the right direction on this one



I have an inventory list of about 750 items broken down into 7 categories. Essentially I would like to have excel pick 15 random items per week for us to count. The important thing is it only picks each item number one time so they don't get counted twice.

What is the best and simplest way to go about doing this? Is there a way to show which items have already been selected.

All suggestions are greatly appreciated.


When the "Date" is given and find out all the dates of a week for that given date. I want to find out how many days in that week falls in which month.
Eg: jan 30th, Jan 31st, Feb 1st, feb 2nd, feb 3rd are the dates.
How can i find that 2 days comes in Jan and 3 days comes in Feb?

I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Thank you very much for any help you can provide, as it is greatly appreciated.

Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).

Component MTBF(months)
1 15
2 24

Below are Company B's sales records for the past 36 months.

Month Sales Month Sales Month Sales
1 10 13 9 25 7
2 15 14 13 26 10
3 17 15 14 28 18
4 22 16 20 28 18
5 25 17 23 29 21
6 26 18 24 30 22
7 20 19 19 31 17
8 19 20 17 32 15
9 19 21 16 33 15
10 17 22 13 34 12
11 14 23 12 35 10
12 11 24 9 36 8

The parts distribution center manager would like to know for month 37 what should be the monthly inventory level of each replacement component to assure fill rates of 98% for component 1 and a 95% fill rate for component 2.
Develop a simulation model using Excel and VBA to determine the inventory needed for each component. Your grade will be based on how well the model answers the question and how easy it is for a user to run the model, change the parameters and interpret the results. Answers should be based on 10,000 replications of the model.


Is there a formula that calculates my desired result which is row 5? I tried creating a formula in row 6 but it fails.
What I am trying to calculate is in week, if I have inventory then how many weeks does it cover?
For Instance, Week 1 I have inventory of $39,120 which covers until 4 weeks and then calculate total of wk1 to 4 and subtract it from inventory value 39,120.
The resultant from this calculation is divided by week 5 and expected production in order to derive the proportion covered in week 5
Please refer to my workbook


Need help with a formula please....

I am creating an inventory spreadsheet.
I have a column with the total amount for each item in it. I also want to create a column for adding more quantity to the items and a column for removing quantity to the items. But I would like it so that when I add and remove, it changes the total amount.

I know how to make it either add or delete, but not how to make it add and delete.


I want to create a function which will provide me week number for a date. The formula weeknum() doesn't solve my problem. The excel week number is defined from january while I want week number to start from a different month. The fiscal year of the company starts from 1st week of feb.

The company's year starts from 4th Feb 2008.
if I use the formula weeknum() for the above date, i get the value as 6. But as per my requirements this should be week number 1. I would prefer a function instead of running a macro. I want to call this function from the excel file. see sample data below

DateExcel Week NumberMy week numberMy Quarter4-Feb61115-Feb7213-Mar105112-Mar116118-Mar127115-Apr161119-May19142

In addition to this, I also want to find the quarter number. Each fiscal year has 4 quarter and one quarter is equal to 13 weeks. So quarter 1 for my data is = (4th feb + 13 weeks). Once first 13 weeks are completed, quarter 2 starts and this goes on till the last quarter (Q4) is reached.
I am weak in writing functions hence need help.

Hi All, I'd like to ask for an equation that provides a month in which inventory is is an example of the set of data...

January 200
Feb... 300
March 450
December 500

Total Inventory 600

Month in which inventory is equal to zero... equation???

Essentially, I would like to subtract the months from the total inventory...

Hopefully one of you can provide an equation to this.



I am running a weekly report for some data which is collected on a daily basis. Col A contains the date (every calendar day) and Col B has data corresponding to it.

Col E is the week for summry and col F is the summation of the data in col B. When I use Sum formula and stretch it on rows below, it does not sum the next seven rows. So now I am manually selecting the data for every week sum.

Is there a better and easier way to do this?


I have a Wasp pen barcode reader (WWR2900 PS2) I am looking for like an
Inventory Control program software package under £200.00. or is it possible
to use Excel 2003 or Access 2003 that will help to make a tracking inventory
using my barcode reader to make a database for the home. Thank you.

I have a table containing weekly data about a company's revenue and
hours, dependant on department:
Week 1 Week 2 Week
3 Week 4 ... Week 52
DepartmentName $$ hours $$ hours $$
hours $$ hours $$ hours
My Department 1200 15 1800 22 1600
12 2000 15 2200 23

Every week has a column for dollars earned and hours spent earning
Now I want to create a report worksheet that would generate some
graphs automatically depending on two variables: week and period. The
week tells me the current week, and the period may be 8 weeks - which
will be used to draw a graph covering the last 8 weeks.

Is it possible to make this user-defined?
I want to be able to have a cell in Excel where I may write the
current week, and one cell to decide the period I need covered for my

I probably have to do some VB, although I hope to avoid in order to
make the usage of it as easy as possible.
Looking forward to your help.

I've working with two different sheets in the same workbook. The first
sheet is called "main flash" and the second is called "actuals". The
"main flash sheet is basically just a cover page the has totals for a
day, week, month, a year. The "actuals" sheet has all of the numbers.
My "actuals" sheet looks like this:


Column A

Date Sales
3/11 10
3/12 15
3/13 10
3/14 20
3/15 30
3/16 20
3/17 30
Total 135
3/18 20
3/19 10
3/20 25
3/21 20
3/22 15
3/23 20
3/24 30
Total 140


The Total's listed are for each week. In my "main flash" sheet I've got
the date I want information from as well as the last day of that week.
So for example today I have 3/22/06 listed in cell A1 and 3/24/06
listed in cell B1.

What I'm hoping to do is have a cell in my "main flash" sheet display
the total sales for the week from the start of the week up until the
date listed in A1. So for example since I have 3/22/06 listed in the
daily date and 3/24/06 listed in the weekly end date the formula would
need to display the value of 3/18 (the start of the week) threw
3/22/06. So that would be 20+10+25+20+15 for a value of 90. Another
example would be if I changed the daily date to 3/13/06 and the weekly
end date to 3/17/06 the value would be 35 (10+15+10).

If anyone can help me out with this I'd greatly appreciate it.

Weasel's Profile:
View this thread:

I'm creating an inventory based master list for computer deployment, that I
will be linking cells that will be running a formula that will calculate
based on current product received and then the second part of it will link to
a spreadsheet on a shared drive(this spreadsheet is where we indicate what
type of computer desktop or latop and who it was deployed to) so we have a
running figure on how much should be on hand. I do know how to create a
formula that can link to the shared XLS file, but the cells in the shared
have X's to indicate which type of computer was deployed, each Row is going
to have at least on marked, what is the best way to write the formula to
indicate that if the cell is X'd then it subtracts one from the master
inventory list. Thanks


Message posted via

I need a template that calculates all aspects of inventory, particularly
safety stock.

This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

No luck finding an answer? You could always try Google.