# Calculate Cost Basis Of Inventory

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

## Inventory: FIFO, LIFO And Average Cost

Oct 14, 2005

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

## Formula To Calculate Overtime On A Weekly Basis?

Apr 21, 2014

I am looking for a formula to calculate overtime on a weekly basis, entered daily, on a sheet with multiple weeks. It need it to calculate the overtime each week.

## Calculate X Percentage Of Cost

Apr 15, 2008

I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume \$100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from \$100 I get \$90, however 10% of \$90 is \$9 which equals \$99.

## Track / Calculate Overtime Hours - Daily Basis?

Sep 14, 2006

I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.

Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?

## If Then Or Else: Calculate The Cost Of A Cushion That Is Governed By It’s Thickness

Sep 21, 2009

use the “INDEX & MATCH” formula and I thank him very much for that. Now, I have another problem which I think the “IF & ELSE” statement may come into play. I am trying to calculate the cost of a cushion that is governed by it’s thickness.

Example:
ABCDEFGHI
1CushionB/FrontS/SideBorderQuantitySeat
2Feather121251£8.25
3Feather121241£7.32
4

B2 represents 12 inches
C2 represents 12 inches
D2 represents 5 inches
144 represents square foot

The calculation is,
F2=B2*C2/144*D2*£1.65*E2..............

## Auto Calculate Total Monthly Cost

Jan 26, 2014

I am trying to keep a running total of cost, here is what I am doing

A B C D E F G H I

Date Books Pencils tax Month Books Pencils Tax Total
1/2/2014 11.50 2.50 .76 January 35.85 13.50 3.44 52.79
1/5/2014 14.50 3.85 .83 February 10.95 1.50 .68 13.13
1/6/2014 9.85 7.15 1.85
2/6/2014 10.95 1.50 .68

I only used Jan and Feb as an example. So as you see on the right side I will have all 12 months for the year and I just want all the totals for each month to auto calculate. I will have between 15 and 30 entries each month.

## How To Calculate Inventory Coverage

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?

## Time Calculation - Calculate Total Cost Per Job Per Person?

Dec 22, 2013

I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.

I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?

sheets.time chart.xlsx

2014 tab- grey column is installer and white is shop employees
profit loss tab- all calculations done here
payroll tab- all cost data here

## VBA / Function To Calculate Effect On Inventory?

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.

## Calculate Inventory Value Using FIFO Method...

Sep 16, 2008

I need help to calculate inventory value using FIFO method...

## Calculate Inventory Months Supply

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

## Calculate The Future Cost Of Education Fee Assuming A Given Inflation Rate

Mar 23, 2007

I am trying to put together a spreadhseet that will calculate the future cost of education fee assuming a given inflation rate. I would like the reuslt to be highlihted in a list or a table. The user would only need input current fee, inflation rate and years where fees are payable. The spreadsheet would do the rest. I have laid this our best I can attached.

## Excel Macro Or VBA To Calculate Desired Profit Margin Depending On Cost

Sep 12, 2013

I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.

A1: landing cost
If 0

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

## How To Print Multiple Inventory Labels Based On Inventory Levels

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

## FIFO Inventory- COGS & Inventory Valuation

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.

## Eliminating Sold Inventory From The Inventory List

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.

## Second Tab Is Inventory Numbers And The First Tab Has My Inventory Items

Feb 27, 2007

I have a workbook. The second tab is inventory numbers and the first tab has my inventory items. I scan in the inventory number (unique) and it adds it to my inventory sheet. As I use inventory I scan the barcode and it inputs the serial number into my used inventory tab. What I need to do it when I scan the serial number for used inventory into the used inventory sheet to have it go to the inventory sheet and delete the line for that serial number.

## Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

## Eliminate Duplicates On A 1 For 1 Basis

Feb 20, 2009

I have a huge list of accruals and payments. Accruals (positive) are entered, and at a later date are offset by the payments (negative). Im trying to make a schedule so i can determine which are left over.

This is easy to do manually for a small amount of rows. However im dealing with 5000 rows and i do not want to manually match it would take many days to do.

Ive tried a duplicate remover. To get it to work i made an absolute value column for the negatives, and compared it to the positives column to find the duplicates. This works to a point. However, If i have three accruals for 100, and one payment for 100, all are identified as duplicates which obviously is not what im looking for.

I need to get it where one accrual is matched to one payment. if there are 3 accruals, 2 payments, 1 is not a duplicate. if there is 3 accruals, 1 payment, 2 are not duplicates.

## Evaluate Formula In VBA On Relative Basis?

Oct 27, 2013

can I put formula to cells on relative basis?

For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?

## Distribute Work On Daily Basis

Jun 16, 2014

Attached a work tracker here for your ref.

I this, We are distributing work on 6 hrs/day basis. What I need is, I need to automate the work distribution on daily basis and should show the hrs also. and the projected date of completion. (yellow highlighted cells are where the formulas are needed)

How can we do that? Excel Help-Updated.xlsx

## Average Formula On Daily Basis

Feb 5, 2010

How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE(\$D\$2:\$AH\$2)-C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE(\$E\$2:\$AH\$2)-C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?

## Hiding An Entire Row - Conditional Basis

Jun 16, 2008

how can I hide the row no 13 when I select "CLINKER" in E3 cell. I have used this code but its giving error.

## Determining Variable On The Basis Of Given Values

Feb 18, 2010

I'm working on this sheet and I can't figure out how to do it.

I'm trying to determine the variable "selling price" on the basis of given values, buying price, Net profit and Expenses.

## Require Count On Basis Of Condition

May 8, 2012

I have data like

col A col B col C col D
DLM2.2 kWDLM75 kWDLM55 kWDLM160 kWDLM11 kWDLM1.5 kWRDLM110 kWRDLM2.2 kWRDLM11 kWRDLM55 kWDLM11 kWDLM30 kWDLM45 kWDLM1.5 kWUS40 kVAUS10 kVAFD45 kVAFD45 kVAFD6.4 kVAFD25 kVAFD45 kVAFD11 kVAFD11 kVAFD150 kVAFD10 kVAFD18.5 kVAFD25 kVAFD18.5 kVA

The problem is col B having M type and col C contain their respective ratings. I want summary report.

Rating 2.2 should give me total count, but if you see there are types.

In col A -
DL
RDL
blank cells

so, 2.2 DL = the perticular count
2.2 RDL = the perticular count

Only M type having subtype DL and RDL.

Im col B, there another subtype also like US or FD. For that also particular rating show their exact count.

Is that possible? I want excel formula not vba code.

## To Rate The Performances On The Basis Of % Results

Jul 1, 2007

I have a model to develop wherein i have to rate the performances on the basis of % results.

i.e.
A rating for more than 15%
B rating for results between 10% to 15%
C rating for results between 5% to 10%
D rating for results between 0% to 5%
E rating for results less than 0%

I know its simple though but real problem is different

I have 9 different measures for which i have 5 different ratings each which i need to apply over 8 different businesses.

I dont want to manually add these conditions & also want to make it dynamic for any changes in rating %.

## Recording Info On Weekly Basis

Nov 17, 2008

I have a training matrix. The following letters represent something F, P & S. and are placed in the matrix. On a seperate sheet I calculate these using the COUNTIF(B2:E7,"F") so on and so on. This is done because the matrix is quite vast and to sit and look through would take an age and I'm already nearly retired lol.
1st question:
Also in the matrix are dates when a student has finally passed a subject is it possible to use the same formula to calculate all these .
2nd question:
I also want to be able to record on a weekly basis how many new subjects have been trained out but don't know where to start . i would like a chart indicating for instance Week 1 5 subjects, Week 2 25 subjects. What i don't want to happen though is when you add Weeks 2 data for Week 1's to increase with it.

## Automating A Macro To Run On A Daily Basis?

Dec 5, 2008

Is there any way of automating a macro to run on a daily basis?