Formula: Calculate The Weeks Cover Of Current Inventory
Jun 10, 2008
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
View 9 Replies
ADVERTISEMENT
Jan 13, 2014
find the formula to calculate how many weeks between two dates for example. I how many weeks between
December 23, 2013
January 5, 2014 = 2w
January 6, 2014
January 19, 2014 =2w
January 20, 2014
February 2, 2014
View 2 Replies
View Related
Dec 27, 2013
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
View 3 Replies
View Related
Nov 17, 2007
I have drawn up a simple summary to show what I am trying to achieve: ...
View 9 Replies
View Related
Aug 6, 2014
this is normally a very simple thing to calculate and I know how to calculate for speed, distance and time or SoDiT as we learnt at school..
I have a period of time where a distance in feet is covered. Lets say 1 hour 25 minutes to cover 12,600 feet, just over two miles.
What I need to solve for in this case is not speed exactly, but how long it takes to cover 10 feet in this scenario.
I start to do the math in excel and then the formatting jumps when I set to hh:mm:ss and I get confused whether I'm multiplying or dividing by 60 and it all becomes a mess.
View 4 Replies
View Related
Jul 24, 2009
I've got these inventory numbers that I've been working with.
Last years Usage and this years usage combined per warehouse for each item.
Plus another sheet that has On-Hand counts per warehouse for each item.
What I'm trying to do is pull my usage count per item per warehouse and place it all on different sheets so that I can compare numbers and do a few different things.
On my data sheet I have the warehouse number, in my example, "TOT.", 0100, and 0101. In the next column is the item # and on the end is the total used for that item by that warehouse.
On my the individual warehouse sheets I have the warehouse number, and the item # and some other things.
What I need is someone to look for the warehouse name "TOT.", 0100, or 0101 and then find the Item # and get the total used from the total column. If it wasn't used or does exist then I need 0 to appear.
View 14 Replies
View Related
Jan 6, 2013
I have series of data values like below. I have to find Maximum, Minimum values for each of these values.
9430
9822
10070
[Code].....
View 2 Replies
View Related
May 15, 2012
I have a calendar. All months across the top, formatted as date mmm with content 1/2 2/1 3/1 4/1, etc. so the cells display Jan Feb Mar Apr, etc. All days down the side formatted as number displaying 1, 2, 3, 4, etc. All the days of the year are formatted as time h:mm.
I want to create a cell that shows me the total time for the last 14 days.
View 1 Replies
View Related
Mar 12, 2014
Trying to think of a formula that will tell me the number weeks stock I have,would like to put the formula in h6 and copy across giving me the weeks stock
Green line is sales, yellow balance stock
EX1.JPG
View 6 Replies
View Related
Dec 3, 2012
In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
View 2 Replies
View Related
Sep 21, 2007
On some Military computers the Add-ins were not installed, therefore the WEEKNUM function cannot be used.
The task is to calculate the time interval (in whole weeks) between two dates.
(Every start date is to be calculated as a whole week – same for the End date).
I tried but the most but came up with the incorrect formula in the attached workbook.
View 9 Replies
View Related
Apr 11, 2014
1/A
B
C
D
E
F
G
H
I
[Code]...
I am trying to calculate the inventory coverage. In C3, I have the inventory amount as of january-end, which is 47,6 M. I want to know how many days of inventory I am carrying, according to the later months cogs figures. In this example, I calculated it as 110 days of inventory. is there a single formula I can use, to calculate the inventory coverage for january, and copy it accross the all columns?
View 5 Replies
View Related
Jul 6, 2014
I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.
It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.
I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.
I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.
View 9 Replies
View Related
Dec 14, 2008
I need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)
Please refer to attached sheet....
View 7 Replies
View Related
Sep 16, 2008
I need help to calculate inventory value using FIFO method...
View 9 Replies
View Related
Mar 26, 2009
I am trying to calculate Inventory Months Supply.
I can calculate the months suppply as shown below in multiple cells, but I am looking for a formula to do in one cell..
The correct result in this example is: 2.26048
View 9 Replies
View Related
Jul 20, 2013
I have a list A2...A11
In cell B1 is the following formula: =SUM(IF(FREQUENCY(IF($A$2:$A$11"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),1))
Rvec is defined in NAME MANAGER as: =ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1
In cells B2...B11 is this formula: =IF(ROWS($B$2:B2)
View 6 Replies
View Related
Apr 24, 2008
How would I write an if statement that checks to see if the date entered is two weeks past?
View 9 Replies
View Related
Sep 10, 2012
How do I print multiple inventory labels based on our inventory levels?
For example:
Item No 1000
Descr Window
Customer Taylor
PO 9001
Quantity 10
Item No 1010
Descr Door
Customer Jones
PO 9011
Quantity 35
I want to print 10 labels with the info from Item No 1000 and 35 labels from Item No 1010????
View 8 Replies
View Related
Jun 26, 2007
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?
View 5 Replies
View Related
Aug 18, 2009
I had been trying really hard to get some solution on COGS valuation & inventory valuation on FIFO basis. my daily transactions are typical sales & purchases.
Attached is the inventory in/out movement from Quick Books. in the column "Num" type bill is the entry from purchase bill whihc always has a reference as P/O####. This is how i will capture the landed cost against a PO., another type "Inv Adj" is inter warehouse transfer. Name is cusotmer, Inventory is my item number. in some cases it is like "2000", in some cases it is 10000:10121, and in some cases it is 10000:10200:10201 that is why they fall in different columns when i export them.
What I would lilke to do: 1) Run a report by month, by customer showing cost of goods sold on FIFO basis, I can capture sales amount by running another report.
View 11 Replies
View Related
Aug 8, 2006
I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...
I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.
View 6 Replies
View Related
Sep 7, 2009
i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
View 7 Replies
View Related
Nov 20, 2006
It's a formula I'm looking for. The best way to describe it is an example.
I have some numerical data on a sheet from cells A2 to A80. This data represents the amount of days each person has spent on holiday. The data will be shown on a graph, but instead of the graph having "1", "2", "3", "4", along the bottom, I'd like it to have "1 week", "2 weeks", "3 weeks", "1 months", "2 months", "3 months".
I -suspect- the formula will involve the " countif" function (or an equivalent) (eg. if the number is less than or equal to 7, add 1 to the "1 week" box, etc.)
View 5 Replies
View Related
Aug 13, 2008
I'm having an issue converting months into weeks. My example is I have people who must provide leaving notice (could be months or weeks). For instance column B details months, and column C details weeks. I'd like to convert months (column B) into weeks in column D, but am not sure of the components of such a formula so I won't list or guess as I'm just not sure.
View 5 Replies
View Related
Dec 17, 2009
I use the following formula: =1-B6/B7. This formula calculates the difference of 2 values. This percentage is returned. Using the formula with INDIRECT do I get data from another worksheet from another workbook. I use this formula: =INDIRECT("'[Workbook.xls]" & Sheet1!E72 & "'!C2"). This formula gives just the value of C2 of the other worksheet of Workbook.xls
I want a formula that calculates between C2 of the external worksheet of Workbook.xls and B2 that is the current value of the current Worksheet that is not external. I want to return the percentage this way like: =1-C2 other workbook/B6. I want to call the other external workbook the same way as I do with the formule above I have given with INDIRECT.
View 3 Replies
View Related
Jun 12, 2014
1) In column A, I have different effective dates.
2) Based on Current system date, I want B column to be populated as "Overdue Date, or Due today or, Due tomorrow or Future dated" in front of that date.
View 5 Replies
View Related
Jul 13, 2009
Is it possible to have my lease rents be automatically adjusted on the anniversary of their commencement date using the current system date? Maybe, this would work using the excel TODAY function. Excel would need to compare the current date with the commencement date and calculate how many years have since passed. It would need to use a compound interest formula to calculate what the present rate is.
In the document attached, you will find (lease commenced, original rent, escalation rate and present rent. There are also ammendments which follow the present rent which show how rent has increased due to new lease agreements or amendments. The most recent rent to use is present rent, but it would be great if excel could show how the rent increased or changed since the lease start.
The document is the following: Basically, these are multi-year property rentals which on the anniversary of the contract signing increase by 4% or 5% annually (or whatever the escalation is). That means, if I rent it out on March 1st 2007 for 1000$, it will be $1050 from March 1st 2008 and then 5% increase on every anniversary. It works just like compound interest over given years.
What I want to know is it possible to have Excel update my spreadsheet depending on the date automatically. Is there any kind of Macro program? I have over 600 rents, that is why I want the process automated. I would want the rents to increase on the anniversary of the contract signing every year and so basically the Excel would update itself, or it would update once I turn it on and then refresh it.
Next question I have is, say I scroll my mouse over the rent column. I look at building A and look at the rent for this year. Is it possible that once I pop my mouse over it a small chart can pop up and describe the rents for the previous years. Meaning a chart that would quickly pop up and show the 5% decrease every year for the past say 5 years.
View 9 Replies
View Related
Jan 13, 2014
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
View 2 Replies
View Related
Nov 5, 2009
I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.
What I want to do is have three columns that interact with eachother.
1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status
What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".
So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".
View 6 Replies
View Related
