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.05.04.03.02.01.0

Any help would be greatly appreciated.

Thanks

Patrick

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.05.04.03.02.01.0

Any help would be greatly appreciated.

Thanks

Patrick

- Formula for Calculating Forward Weeks Cover....
- Calculate weeks cover
- HELP! Single cell formula to calculate weeks cover of stock on forward sales.
- Week Number For Date Relative To Date Range
- Get week number for month from date
- Return work week number for a date
- Is there a weekly timesheet for excel somewhere?
- Months Cover (Coverage)
- Is there a weekly timesheet for excel somewhere?
- Running total formula for inventory
- I need a formula to project inventory cover
- Excel for Inventory Cycle Counting
- Week Number for Given date.
- Simulation Model For Inventory
- Stock Turnover
- Dynamic SUM For Inventory
- Function for assigning customised week number in XL2007
- Calculation for Inventory Runout???
- Repetitive Summation for Weekly summary
- Program for inventory
- User-defined range for graph
- Trying to find out a solution for a complex formula
- Linking two spreadsheets for inventory
- A calculator for inventory safety stock

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.

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

logic.

Can anyone help me with the correct function to calculated this.

expression.

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...

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.

Cheers

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.

----------------

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.

http://www.microsoft.com/office/comm...lic.excel.misc

Hello

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

Hi

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.

----------------

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.

http://www.microsoft.com/office/comm...lic.excel.misc

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.

Thanks!

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

Thanks

Vinny

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.

Question

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

Biz

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.

Thanks,

Ex.

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.

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.

Thanks,

Mark

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?

Thanks

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

them.

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

graph.

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.

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:

Code:

--------------------

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

------------------------------------------------------------------------

Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206

View this thread: http://www.excelforum.com/showthread...hreadid=525297

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

Ralph

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...excel/200512/1

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.

http://www.microsoft.com/office/comm...lic.excel.misc