Simple Job Cost Sheet

Feb 20, 2009

The job cost controller is not exactly what I need, so I have been trying to create my own. So far my progress has got me to be able to separate each laborer for each job.

Here is my query: I have a drop down menu that lists ten different tasks(i.e. carpentry, painting, demo, etc.) and I want each of the ten tasks to separate themselves. Is this a vlookup function? How do I make excel listen?

ADVERTISEMENT

Simple Form To Display Cost Based On Discount?

Jul 14, 2014

How would I create a simple user form to display a price based off the category selected.

User types in List Price, then selects 1 of 4 discount categories, it then displays that price.
An added bonus would be copying that price to another sheet, but not necessary..

View 2 Replies View Related

Transferring Cost From One Sheet To Another

Feb 24, 2009

I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?

View 6 Replies View Related

Taking Value From Another Sheet And Multiplying (cost X Hours) And Combining Days / Weeks

Jun 18, 2014

I have tried to make a sheet which has

Resource, Task, Rate (I'd like this to be on another sheet, as there is a lot of repetition) Also, right now the columns for hours are based on weeks, but i'd like to do it by days and then collapse by weeks, and months and get totals by weeks and months too. Then, I'd like to multiply the rate by the hours to find the total number of hours, however the way it is done right now is not very intelligent and very time consuming when creating the formula, which looks something like this :

=SUM(H4*$C$4,H5*$C$5,H6*$C$6,H8*$C$8,H9*$C$9,H10*$C$10,H11*$C$11,H13*$C$13,H14*$C$14,H15*$C$15,H16*$C$16)

what can be done to make this a bit better?

View 2 Replies View Related

Simple Copy Of Named Range To Another Sheet?

Feb 11, 2013

I have a named range on one sheet, and I want to show this as a reference on other sheets. I thought this would be simple, but maybe it's just not the way named ranges are used.

Attached is a sample spreadsheet. First tab shows the table defined; in practice it would be much larger. Second tab shows how I want it to appear. I see that I can do this by copying each cell reference. But what would be nice is to simply say "Put the named range block of cells right here."

View 3 Replies View Related

Simple Class Schedule For 5 Students Into One Sheet?

Jan 24, 2014

Essentially I'm in charge of managing schedule for 5 students, I would like to get them to meet during their free time (free for all 5 of them)

1. Schedule has to run from 8 am to 9 pm.
2. I have all 5 individual schedules.

I want to combine all 5 into one so everyone can see their free/occupied times, it would be extra useful if I can modify individual schedules (One student's work schedule changes bi weekly).

It has to be able to show who is occupied at which time and free time for all of them. (color coding each of them would be amazing also, granted I can show multiple clashes with gradient or something similar)

I understand it involved creating individual spreadsheets and a master sheet but how to go on about it.

View 1 Replies View Related

Simple Color Code Not Working On Specific Sheet

Jul 22, 2013

This very simple color code below worked before but now it gives "Application-defined or object-defined error". When I try it on a new workbook, it works fine.

Code:
Sub sdsdfsd()
Selection.Interior.Color = 65535
End Sub

View 1 Replies View Related

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?

View 5 Replies View Related

Simple Copy Sheet Paste In Other Book But Paste Values?

Jun 20, 2013

Attached is my code, pay attention to the bold part. I want the sourceSheet to be copied as a sheet and pasted in the targetSheet (the Sheet2 of "NewBook") but I want it pasted asvalues. Here is the specific part which needs to be looked at...and below is the full code.

VB:
Set sourceBook = Application.Workbooks.Open(sourceFilename)
Set sourceSheet = sourceBook.Sheets("Current")
Set targetSheet = NewBook.Sheets("Sheet2")

[Code].....

View 9 Replies View Related

Calculating Cost Per Second

Oct 25, 2007

I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.

Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.

To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.

View 14 Replies View Related

Calculating Cost

Apr 17, 2009

Problem - billing spreadsheet for prisoner fee.

1 - 8 hrs = $55
9 - 24 hrs = $55 + $65 or $120
Anything over 24 hrs - $65 for each additional (24 hrs) ($185)

So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.

View 5 Replies View Related

Cost Per Row Of Data

Mar 6, 2014

I have an excel table that among other fields it has a [product Charge] field.. what im trying to do is to get a cost per row based on the [Product Charge] field.. so example if I have 10,000 rows and the total for that field is 1,000,000 the cost should be 100 ( [Product Charge] / total rows).... I then want to be able to use it in a pivot table...

View 1 Replies View Related

Least Cost Routing

Aug 3, 2009

im working on a VoIP company, and having a hard time to figure out a result on a little time.

the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns:

I am including an image, sorry if the image seems to be heavy and large:

as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers.

While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate.

Red marks=are not suitable or non-profitable buy rate for the sale rate.

Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.

View 9 Replies View Related

Cost Vs Units Graph

Jan 25, 2009

I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):

Cost per unit for 0-10 units: $1
Cost per unit for 11-20 units: $3
Cost per unit for 21-30 units: $5
Cost per unit for 31-40 units: $8
Fixed cost: $100 (this is regardless of the number of units produced)

Basically I'm looking to construct a cost vs units graph.

View 11 Replies View Related

Calculating Cost Per Month?

May 8, 2014

I am trying to create vba macro that allows me to calculate cost per month.

I have created a basic table with two columns

Cost
Month

200
Jan

300
March

150
Jan

470
April

150
March

I have another sheet , that has two columns

Month - Cost
Jan
Feb
March
April

I am trying to sum up the value on sheet1 and input into sheet2 on the allocated month.

So far logic , that I have came up with is.

I create a For loop statement that looks column and sort each month into an array.

Than the second loop will use the array to find the cost with the allocated array value to sum it.

View 2 Replies View Related

Cost Based Mark Up

Sep 26, 2007

set up a cost based markup spreadsheet?

I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value.

I only know how to mark up values by a single percentage.

View 9 Replies View Related

Time And Cost Calculations ..

Aug 14, 2006

I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit.

The source data I have is;

1 - Start time and end time of the job

2 - Any breaks taken during the job

3 - The number of staff it took to do it

4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates

5 - The number of punnets packed.

With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39

View 2 Replies View Related

Cost Average Effect

Oct 19, 2006

Example number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms

Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.

How big will the difference be between example 1 and 2 ?

*** Background.
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.

Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.

View 2 Replies View Related

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.

View 8 Replies View Related

SUM Based On Cost Center Wise

Jan 14, 2014

I need a sum of amount based on the Cost center & Category from the details table , can i have a formula to get them .

Please find the example amount in the attached excel sheet highlighted in RED Colour.

View 3 Replies View Related

>>> Finding Lowest Cost/Vendor

Oct 24, 2007

I need to find the lowest price, mark it up and designate which vendor it’s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new “price” and “vendor” columns and then I can go through and mark it up.

Here’s a small sample of what the sheet looks like:

Item #Desc V1 V1 CostV2V2 CostV3V3 Cost
123456brush Advance 2.56Weiler5.65Osborn3.25

View 2 Replies View Related

Calculating Cost Based On Several Factors

Jan 7, 2009

i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.

I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.

For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.

Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.

Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.

View 10 Replies View Related

Way To Check Whether Any Cost Centre Has Been Missed Out ...

Nov 20, 2009

I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences.

So my questions are

1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out.

2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.

View 11 Replies View Related

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

View 7 Replies View Related

Margin Forumla :: How Do I Figure Out The Cost?

May 27, 2008

I have the margin and I have the sale price. How do I figure out the cost?

View 12 Replies View Related

Manufacturing To Retail Cost Calculator

Jun 15, 2009

I am trying to make an EASY Manufacturing Cost to Retail pricing calculator. This calculator would have ability to include cost of goods, labor, markup etc of components manufactured and sold as retail products, example: small bookshelves versus large bookshelves or cabinets all have different materials (wood types, stain etc) in determining the final retail product costs that would reflect time of labor hours involved in producing to determine final retail costs. Should be simple in Excel 2000 (my version). I have the basic template created and have used Data Validation Drop Down Lists and utilized LOOKUP function. While my knowledge is limited in Excel, I am frustrated as how the LOOKUP function works, I can only get it to work where in the formula, the costs per unit are input manually, whereas I would prefer to have the data input automatically from columns of calculated wholesale cost plus markup per square foot data, ie: =lookup(A1, X1:X30, Y1:Y30) but get errors when doing this. Instead this works: =lookup(A1,{"pine", "oak", "birch"},{"3.99","7.87","5.15"}) and using this cell (A1) in my calculations for including the square foot costs in the final retail calculations. While this seems to work, it is not easily modifiable as costs change rapidly and would like to easily input the cost per square foot of the different woods in their own cells rather than in the formula calculation of the lookup. Hope that makes sense..... any suggestions? perhaps lookup is not the best function for what I am attempting? - sample file is attached, light green are notes of where my data is located.

View 10 Replies View Related

Changing Cost Allocation Macro?

May 23, 2012

I am trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically $.01 - $.02)

I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.

Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.

View 9 Replies View Related

Calculating Logistics Cost Tool

May 21, 2014

I am a Supply Chain intern at a company for the summer. One project I have been given is to create a tool in excel that allows our purchasing people to type in certain inputs (origin, destination, etc.) to calculate the total cost of shipping an item from a supplier in anywhere from china, to somewhere locally. I am gathering quotes for shipping via ocean and truck. How can I get where I enter in one location, say shanghai, china, shipping to another location, say Norfolk, VA, and get the total mileage difference to show?

View 1 Replies View Related

Time And Cost Calculations Per Minute

Oct 17, 2007

I have costs per minute and call times in seconds

How do I work out the cost per call in Excel?

It's probably something very simple, but I just can't work it out. No matter what I try, I can't get it right

View 9 Replies View Related

Cost Price Vs Quantity Discount

Apr 7, 2008

I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +

View 9 Replies View Related

ISDN Call Cost Formula

Jul 3, 2008

I have an Excel workbook with 2 worksheets, labelled as "codes" and "data"

The "codes" worksheet contains three columns of data.

Column A shows Country names
Column B shows Country dialling codes
Column C shows Cost per minute to the country ...

View 9 Replies View Related

Sum The Total And Find The Average Cost

Oct 1, 2008

I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price ($) together and divide by the sum of A&B.

In other words find the average price for the total of each item..

A B C
Code Qty $
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2

View 9 Replies View Related

Increase/Mark-up Cost By X Percent

Apr 15, 2008

Formula for figuring retail price from cost. ie Cheeseburger 1.00 cost and I want a 60% markup. (2.55 Retail)

View 3 Replies View Related

Excel 2010 :: Comparing Record And Associated Cost

Apr 7, 2014

I am trying to compare values of an item one year vs the other in excel 2010. So my spreadsheet would look like:

A B C D
028042$500,000.00028042$500,000.00
028349$300,000.00028349$300,000.00
028390$300,000.00028390$300,000.00
028498$100,000.00028498$100,000.00
029477$135,140.00029477$135,140.00
030374$452,865.45030374$452,865.45
030375$452,865.45030375$452,865.45

What I am trying to do is compare A and B vs C and D to see if the cost associated with the number in A is the same as it is in C(which is obvious it is in this example but I have a spreadsheet with thousands of entries). Additionally, the AB value might be anywhere in the CD columns not just in the row next door.

View 12 Replies View Related

Calculating Total From Number Of Minutes And Cost P/m

Aug 6, 2009

I need to calculate the total cost of outbound calls based on the total duration of outbound calls multiplied by cost per minute. For example, in a given month, the total duration of outbound calls is 261:16:34 being 216 hours, 16 minutes and 34 seconds. I have this figure in cell A1 with the format [h]:mm:ss. I then convert this to minutes in cell B1 by saying B1=A1, but having the format [m], which gives me 15676. In cell C1, I have the cost per minte value of £0.026. But when I apply the formula D1=B1*C1, I get £0.283, when 15676*£0.026 should in fact be £407.58.

View 2 Replies View Related

Calculating Manhours/labor Cost In 2003

Aug 12, 2009

I am having trouble trying to calculate cost for a specific task. I know this is something simple and I am going to kick myself when it gets solved, but I have total brain lock right now! Here is the example of what I am trying to do.

A B C D E F
# of people start finish time man hours labor cost
3 1:35 2:05 :30 1.5 $15.00

I am entering the values in A, B and C, with B & C formatted as TIME. D is calculated by =(C3-B3), but I am lost trying to calculate E and F.

View 2 Replies View Related

Divide Cost Over A Number Of Years At Different Frequencies?

May 31, 2013

I have 4 columns labelled 1st Year, Frequency, Spread and cost. then I have columns spanning upto 30 they represent years but can be labelled as 1 to 30. My challenge is that I want a formula/function/macro that depending on what I type in the first 4 columns it will populate the remaining 30 columns with either cost or 0.

Example: if I had £9,000 at a frequency of 10, starting on the 5th year with a spread of 3.

The cost represents the cost of each frequent. so in the example I would get £3,000 on each of the 5th,6th and 7th years then another £3,000 on the 15th,16th,17th years and finally another £3,000 on the 25th, 26th and 27th years.

what I want to be able to do is have one function/formula that I can paste across all of the year cells. I currently have a formula that can copy the cost at different frequencies but I cant figure the spread out.

=IF($K:$K>0,IF(OR($8:$8=$J:$J,MOD($8:$8-$J:$J,$K:$K)=0),$L:$L,0),0)

where K is frequency
8:8 is year
J is cost

what is the best way to approach this?

View 2 Replies View Related

Profit / Loss Payroll And Cost Of Supplies

Mar 6, 2013

I have multiple departments I need to keep track of multiple numbers with. For example, I need to see the cost of payroll for last fiscal year, vs this fiscal year and have excel show that as a gain (bad) or shrink (good).

I need to track sales this FY vs last FY, obviously, a gain is good and shrink is bad.

Finally, I need to track d&d goods this FY vs last FY.

Where I'm getting held up is, for example last year's payroll was 12000 and this year's is 12500 but next months is 11000 this FY vs 13000. How do I get excel to figure out that payroll decreasing is good and not just do simple subtraction or negative addition thereby allowing me to just plug the numbers in and excel figure out if what I'm throwing at the cell is bad or good comparing to last year?

Also, if I could figure out the if/then to show positive numbers green and negative numbers red that would be quite awesome too.

View 1 Replies View Related

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

View 4 Replies View Related

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.

View 5 Replies View Related

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

View 9 Replies View Related

ADVERTISEMENT