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
I have a spreadsheet which uses MS Query to import data from our MRP System. This Spreadsheet has all new orders and qtys required per sales order etc. Each Part number may have several sales orders and different qtys for each. I am looking for help to write a fomula which looks the required qty`s for each sales order & part and check another sheet which has all the avaliable stock to sell on it and populate a column next to the qty req`d with what if anything is available.
Both the orders sheet and the stock sheet are MS Queries which refresh at a given interval so cells may change position.
Cutdown Example of Orders Sheet.
Sales Order No. ¦ Part ¦ Qty Req`d ¦
--------------------------------------
1234 ¦ Apple ¦ 12 ¦
6777 ¦ Apple ¦ 16 ¦
7989 ¦ Pear ¦ 4 ¦
cutdown Example of Stock Sheet.
Part ¦Qty in Stock ¦
----------------------
Apple ¦ 12 ¦
Pear ¦ 3 ¦
Now the fomula should bring back the following
Sales Order No. ¦ Part ¦ Qty Req`d ¦ Qty Avaliable ¦
----------------------------------------------------
1234 ¦ Apple ¦ 12 ¦ 12 ¦
6777 ¦ Apple ¦ 16 ¦ 0 ¦
7989 ¦ Pear ¦ 4 ¦ 3 ¦
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?
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.
I need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)
Please refer to attached sheet....
I need help to calculate inventory value using FIFO method...
View 9 Replies View RelatedCalculate 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
I need to be able to calculate age in months, and round whatever remainder to the middle of the month (.5). I am calculating z-scores and percentiles for in a childhood obesity project. The DoB and Date of measurements must be converted to read anything from 142.0 to 142.9 as 142.5.
This value will be used to look up the the appropriate LMS (Box Cox, Median, and Standard Deviation) in another excel table. I have to be able to compute age in months so 142.5, 143.5, 144.5, 144.5 etc. It is just not rounding off to the nearest half month.
I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.
=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"
I am building a template to auto populate budget value by straight line based on task start/end date criteria. some tasks could give me correct duration distribution, but some don't. I am looking for If function, which could give me correct monthly allocation budget. Here is an example of what I am trying to achieve. task 0110 duration is from 09/15/07 to 05/24/08, duration month is 8. budget $34,465supposed to be allocated to 8 month starting from Sept, 07. but my if logic return 9 month instead.
View 8 Replies View RelatedHow 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????
I have a row containing:
Date entered
Name
Date of Birth (DOB)
Occurrences of Name + DOB this Month
Occurrences of Name + DOB within last 3 Months
Occurrences of Name + DOB within last 6 Months
I can manage the occurrences this month but haven't a clue how to calculate the 3 and 6 months ones. I have attached a sheet.
I'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:
{=AVERAGE(IF($B$5:$B$53=B$2,OFFSET($C$5,(COUNT(C5:$C$53)-1),0,12,1)))}
I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/b]of the Offset function was setup simply to get the 12 months which preceeded the reference date.
Start date: 12/04/2004
End date: 12/04/2006
The formula should give the answer to 24 months
Example 2
Start date: 12/04/2004
End date: 13/04/2006
The formula should give the answer to 25 months
When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not
show 25 months for "Example 2" as it is still within the same month "April"
I need to automatically calculate the number of months a deal runs through 2012 dependant on the start and end date.
I have attached a basic spreadsheet. Column C shows the results I would like the formula to calculate.
Is there a way to add # of months to a date and then find out what would be the exact date?
For example if cell A1 = 04/30/07 and Cell B1 = 14 what is the statement that would give me 06/30/08 in Cell C1?
Also, how can I modify the statement if Cell B1 = 14.5 to get 07/15/08?
Is there a formula which would show the number of months between two dates eg.
15-Jan-06 to 30-Nov-07 = 23 months
I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.
However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.
I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').
I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?
Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.
I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.
How to average a particular month over all years like so:
{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}
But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.
I wrote code that works just fine, however, I realized there were parts of the data I was using that had 0's in it for holiday.
Bottom line, this is code that calculated the weighted average of prices for 12 months (12 rows across) and Drows across. I'm trying to use the "do while is empty" approach but can't get the syntax to work. Here's the code
Code:
For r = 1 To drows
Totwavg = 0
Tothours = 0
priceavg = 0
For n = 1 To ncontracts
[Code] .....
I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"
C7 3/24/04
D7 1/4/08
What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?
I am trying to calculate the number of months in a specific year between two dates.
For example.
Start date 01/06/2012
End Date 01/02/2013
Number of months in 2012 = 6
Number of months in 2013 = 2
How can I write a formula to give me the answer of 6 & 2 from the start and finish dates?
I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter.
It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.
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.
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.
In one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.
View 9 Replies View RelatedI am looking for something to calculate values based on the past three months. I already have written a macro to retrieve all of the data and just need this as an add-on.
for example, lets say it is 5/31/2012. I have a row with dates going like
10/31/2010 11/31/2010 .........
I want the formula to calculate SUM(march+april+may row 68)/SUM(march+april+may row 59)
I have an input tab where I can select the month, say 5, and get the periods.
3/31/2012, 4/31/2012, 5/31/2012
if I changed the 5 to an 8 it would get 6/31/2012 7/31/2012 8/31/2012
I don't know if i need VBA for this but I am trying to have something to calculate this depending on the period selected in the input tab.
I am using excel 2010. I am thinking something with an offset function and maybe a loop? but still not sure.
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View 2 Replies View RelatedI have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
View 2 Replies View RelatedI want to display the percentage of occupancy for each month for condo rentals.
I have columns with dates that represent bookings throughout the year. check in and check out dates to be more precise. (A1 and B1 for example) and I have columns with months Jan, Feb, etc. (C1, D1, etc. for example)
The problem I run in to is that I can calculate the number of days between the dates and the fraction of the year (*12 to get a monthly value, but I fail to understand how I can break that up so that I can populate each month with a correct percentage of the total term.
E.g. A1=1-jan-2014 and B1=20-jan-2014 is an easy one because it only covers 1 month:
formula: =(YEARFRAC(A1,B1,3))*12 gives me 62,47% which I could just use as is for that month.
So far so good, but when the period covers more than one month or when the months overlaps, I don't know how to break the outcome up into the appropriate months to display the correct percentage for each month in its own column.
e.g. A1=1-jan-2014 and B1=20-Mar-2014 gives me a result of 253% with the same formula.
100% for November, 100% for December and 63% for January, which I could somehow formulate to break up over different Months.
But then it gets more tricky. What if the booking starts on a day other than the 1st of the Month?
e.g. A1=5-jan-2014 and B1=20-Mar-2014
The result of the formula (243%) is correct, but isn't sufficient to put the correct percentages for each month in their respective column.
What formula(s) should I use to break down the percentages to match the correct fraction of each individual month?
I attached an example of what I have so far for your review : test.xlsx‎