Calculate Cost Basis Of Inventory
Dec 14, 2008I need a macro to calculate the cost basis of the inventory. ( Field F3 and F4 in Sheet1)
Please refer to attached sheet....
ADVERTISEMENT
Inventory: FIFO, LIFO And Average Cost
Oct 14, 2005I 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, 2014I 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.
View 8 Replies View RelatedCalculate X Percentage Of Cost
Apr 15, 2008I 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.
View 8 Replies View RelatedTrack / Calculate Overtime Hours - Daily Basis?
Sep 14, 2006I'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, 2009use 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, 2014I 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, 20141/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, 2013I 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, 2014I 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, 2008I need help to calculate inventory value using FIFO method...
View 9 Replies View RelatedCalculate Inventory Months Supply
Mar 26, 2009I 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, 2007I 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.
View 5 Replies View RelatedExcel Macro Or VBA To Calculate Desired Profit Margin Depending On Cost
Sep 12, 2013I 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, 2008Calculate 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, 2012How 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, 2009I 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, 2006I 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, 2007I 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.
View 9 Replies View RelatedIteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount
Mar 15, 2007In 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, 2009I 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, 2013can 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, 2014Attached 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, 2010How 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, 2008how can I hide the row no 13 when I select "CLINKER" in E3 cell. I have used this code but its giving error.
View 10 Replies View RelatedDetermining Variable On The Basis Of Given Values
Feb 18, 2010I'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, 2012I 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, 2007I 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, 2008I 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, 2008Is there any way of automating a macro to run on a daily basis?
View 9 Replies View Related