
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Could someone help me with creating a formula for forecast of costs per month and hours against the baseline and actuals. Below is an example of my data.
MONTHLY REPORT
Position / Title
Cost Report
Cost Variance  Comments
Job Hours
Hours Variance  Comments
Baseline Cost
Actual Cost
Variance Cost
Forecast Cost
Baseline Hours
Actual Hours
Variance Hours
Forecast Hours
$ 144,362.11
$ 
$ 144,362.11
$ 
744
23
721
0
Principal
$ 25,855.50
$ 25,855.50
94
23
71
Project Manager
$ 23,690.00
$ 
$ 23,690.00
103
0
103
Engineer
$ 72,740.88
$ 
$ 72,740.88
391
0
391
Engineer Assistant
$ 18,101.33
$ 
$ 18,101.33
113
0
113
Project Assistant
$ 3,974.40
$ 
$ 3,974.40
43
0
43
Similar Excel Video Tutorials
FORECAST function
 See how to use the FORECAST function for linear algebra. Formula and functions ...
LINEST function
 Do Multiple Regression and Linear Algebra with the Array LINEST function. Also see how to use the SLOPE, INTERCEPT and the FORECAST functions for line ...
Excel
 Excel! Excel Is Fun! Excel Is Power! See a long formula that uses the IF, AND, MOD, ROW, RANDBETWEEN, and COLUMN functions. See how to add a Condition ...
Helpful Excel Macros
Delete a VBA Module From Excel
 Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Similar Topics
Below I am trying to use Vlookup, Hlookup and Match to populate time sheets with each employees 'Planned' hours as anticiapted at the beginning of the year. Then each employee will be given a time sheet with what activities they are support to work on (again, Planned hours). Then collection of time will take place each week and variance to be reviewed for project impacts. Problem is, I have been unable to figure out the code. Thoughts from anyonr?
Thanks in advance,
Keith
Time Sheet  Employee 1
Actuals fw1 fw2 fw3
Planned Actual Variance Planned Actual Variance Planned Actual Variance
Employee 1 0 0 0
Employee 2 0 0 0
Employee 3 0 0 0
Employee 4 0 0 0
Separate Spreadsheet
Planned Hours fw1 fw2 fw3 fw4
Employee 1 4 5 6 8
Employee 2 5 6 8 4
Employee 3 6 8 4 5
Employee 4 8 4 5 6
I am trying to create an IF statement that has two possible calculations rather than two values that should be applied.
In my workbook I have the following columns: Forecast, Actual, Variance, Variance percent.
The forecast column contains a manually entered number as does the actual column.
The variance column = actual divided by forecast.
For the variance percent column what I want to say is: IF forecast is greater than zero then calculate percent by dividing the variance by the forecast BUT if forecast = zero then calculate variance percent by dividing the variance by the actual.
Sarah
Hi everyone,
I am trying to come up with a formula that will tell me the increase or decrease between two numbers.
Example:
Baseline Labor Cost = 1,000
Month 1 Labor Cost = 1,500
Month 2 Labor Cost = 500
Month 1 Variance = +500
Month 2 Variance = 500
The results of the formula should show the variance for each month.
I know this is probably simple, but I have gotten all wrapped around the axle and can't make it work properly!!!
Hi
I attached a workbook. Basically, i need to readjust the formula for forecast cost from March 2010 to Dec 2010 (Range D3:M5) so that the total cost (Range N3:N5) will be the same as total budget cost (Range N11:N13).
The different between total forecast cost and total budget cost is because some cost in Jan & Feb 2010 were not taken up in Actual. Now, i need to add those not taken up amount in Jan & Feb 2010 to March  Dec 2010 by smooth it out so that total cost will be equal to total budget cost.
I hope you understand what i am trying to say.
Thanks
Can anyone help me create a "Variance" field (calculated item) in my pivot table below? I would like this "Variance" field to subtract "Forecast" from "Actuals". I have searched for help on calculated items but haven't found a solution yet. I would appreciate any assistance.
******** ******************** ************************************************************************>
Microsoft Excel  Shipments Report  Feb 18.xls
___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C4
=
C
D
E
F
G
4
Type
5
Program
Data
Actuals
Forecast
Variance B/(W)
6
A
Sum of Qty Ship
5

7
Sum of Extended Price
180,000
264,400
8
Sum of Extended Cost
207,349
278,717
9
Sum of Gross Marg $
(27,349)
(14,317)
10
B
Sum of Qty Ship
281

11
Sum of Extended Price
639,260
1,043,714
12
Sum of Extended Cost
466,806
778,671
13
Sum of Gross Marg $
172,454
265,043
14
C
Sum of Qty Ship
1

15
Sum of Extended Price
4,562
146,499
16
Sum of Extended Cost
6,393
86,623
17
Sum of Gross Marg $
(1,831)
59,876
Summary
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I know this probably has to be an 'if' statement, but just can't get it worked out.
What I have is a Row of hours worked per week and need to calculate the cost @ $50. / hour and 1.5x ($75) for hours over 40 hours in the week.
A B C D E
60 10 48 56.5 15
The formula I used =SUM(A1*50)+((A140)*25)
Works fine if hours are over 40, but doesn't work for those weeks under 40.
Any help would be appreciated!
I have a simple budget spreadsheet. Multiple people may be editing it and I wanted to give the controllers an easy way to dictate which cells can be edited. I would like to PROTECT the entire workbook, but allow editing only on the light yellow highlighted cells.
I think there is a way to do this with the workbook_change trigger, but can't seem to find help. This would make it simple for others to highlight cells and not have to worry about locking/unlocking cells.
Appreciate your help!!
Sheet1
A
B
C
D
E
F
G
H
1
Project
Capital/ Expense
FC / AC / Var
Jan
Feb
Mar
Apr
May
2
3
Portal
Capital
Forecast
$ 23,000
$ 21,000
$ 25,000
$ 25,000
$ 20,000
4
Actual
$ 21,000
$ 20,000
$ 27,000
5
Variance
$ 2,000
$ 1,000
$ (2,000)
$ 25,000
$ 20,000
6
7
Expense
Forecast
$ 6,000
$ 5,500
$ 23,000
$ 2,200
$ 5,000
8
Actual
$ 5,000
$ 7,000
$ 21,000
9
Variance
$ 1,000
$ (1,500)
$ 2,000
$ 2,200
$ 5,000
10
11
HR Website
Capital
Forecast
$ 25,000
$ 22,000
$ 23,000
$ 25,000
$ 20,000
12
Actual
$ 12,000
$ 18,000
$ 22,000
13
Variance
$ 13,000
$ 4,000
$ 1,000
$ 25,000
$ 20,000
14
15
Expense
Forecast
$ 6,000
$ 5,500
$ 12,000
$ 2,200
$ 5,000
16
Actual
$ 5,000
$ 7,000
$ 15,000
17
Variance
$ 1,000
$ (1,500)
$ (3,000)
$ 2,200
$ 5,000
Spreadsheet Formulas
Cell
Formula
D5
=D3D4
E5
=E3E4
F5
=F3F4
G5
=G3G4
H5
=H3H4
D9
=D7D8
E9
=E7E8
F9
=F7F8
G9
=G7G8
H9
=H7H8
D13
=D11D12
E13
=E11E12
F13
=F11F12
G13
=G11G12
H13
=H11H12
D17
=D15D16
E17
=E15E16
F17
=F15F16
G17
=G15G16
H17
=H15H16
Excel tables to the web >> Excel Jeanie HTML 4
Hello. I need to figure out how to make a rate table where the rate changes and depends on time. For example,
if 01 hours cost 1 dollar
if 12 hours cost is 4 dollars
if 23 hours cost is 7 dollars
if 34 hours cost is 10 dollars
if 524 hours cost is 15 dollars
the problem that I am finding is, if, for example the time price I need to calculate is 49 hours. How do I make a formula for the two 24 hour days and the additional 1 hour price. Thank you so much. I have been at this for days!
Christina
I have a sheet with employee hours listed daily horizontally. I have a total hours column, rate per hour column, and a total cost column that figures OT over 40 hours. That formula is =IF(J9>40, 40*K9+(J940)*K9*1.5,J9*K9). All is good works great. Boss now wants to have a total cost by day. I’ve spent hours and hours today trying to get this to work. Overtime is not paid until 40 hours are reached but employees work many different hours in a day. Let’s say 9 hours per day for 5 days. Mon, Tues, Wed, Thurs, 9 hours each all straight time but Friday hours the first 4 are straight and then the next 5 are OT. I need to list the cost by day totals. Using $10 per hour as an example, $90 each day until Friday $115. This is for a construction company, work is weather permitting. Some days could be just a few hours and others many. No OT until over 40. I thought I had it working but the calculation fails when the daily total is below 8. Thanks in advance for any help you may be able to give me.
Joe T
I am creating a comparison sheet for kilowatt hours used, in D29 I have the actual KWH used. What I need is a formula that will give me the cost of the 1st 400 KWH at $0.08570 per KWH in BV7, the 2nd 400 KWH at $0.12175 per KWH in BW7, and the cost of the remaining KWH at $0.14427 per KWH in BX7. Now be advised that some times the KWH may totol 653 for the month and sometimes 1950 for the month. Hope you can help. Alfonso.
columns C through to O show the variance level of stock for different weeks. The cost of that item is in column V. So in column W i need a formula to to total the cost of that variance.
However if we are down 2 items i.e the variance is 2 then the sum would be 2 x stock price but if the variance is +2 then no some should take place because I only want to account for the loss's/defiects.
Thanks in advance
Hi, I'm using Excel 2002 and am having trouble with what I thought was a simple conditional command.
On part of the spreadsheet I have 3 columns: Hours (D5), Cost Per Hour (E5) and Total Cost (F5)
Here's what I'm trying to do.....
If there is no value in the Hours then the Total Cost should show an empty cell and conversly if there is a value in Hours then calculate Hours*Cost Per Hour in the Total Cost cell.
My condition for cell F5 is as follows:
=IF(D5="","",SUM(D5*E5))
Whilst it works if there is a value in D5, when empty F5 shows #VALUE!
Anyone help please?
I'm at lost as to the most efficient solution to this problem which I'm hoping the Excel expert community can help with.
I have a worksheet that I am pulling data in using the index function from another workbook. The data pulled in are project ids and hours worked for months that have occurred. Once the date pulls in; I remove all links and allow users to forecast hours worked for each project in future months. The example below shows data pulled in via index function for projects and hours worked for months JanApr. with user entered forecast hours for future months May  Dec:
Project Jan. Feb. Mar. Apr. May Jun. Jul. Aug. Sep. Oct. Nov. Dec.
5612 5 6 7 1 2 8 9 4 8 7 5 5
5613 8 4 9 0 7 4 7 1 2 4 2 1
At the end of May, I need to update this worksheet with actual hours worked for the month of May while leaving user entered forcast hours for Jun  Dec. untouched. In addition, there may be new project numbers that need to be added. I can use the Index function again to pull in actual hours for May and new project numbers but how would I be able to keep the forecasted hours users entered last month for existing projects?
Any help would be greatly appreciated!
Hi folks,
I have a schedule formula I am working on.... Except I am running into a problem.
I am trying to figure out a way to determine the cost of the labor in a schedule for the day... using a matrix, you would place the hours in a grid above, and the hours would be calculated below.
so...
Sun Mon Tues Weds Thrs Fri Sat  day of week
5 10 10 10 10 10  hours worked
if paid $10 an hour... the cost per day is...
Sun Mon Tues Weds Thrs Fri Sat  day of week
50 100 100 100 125 150  cost per day
My issue is how to write the formula to calculate how many hours are past overtime, and how many arent.... the problem I am having is not the first day with over time, it's the second day doesn't calculate right.
here's a sample version.
Hi folks,
I have a cost estimation spreadsheet that I have been using that allows me to enter various tasks into a table by task description, assigned engineer (if any), cost account, and cost/hours. For example,
Design Circuit Board, John, Labor, 100 hours
Enclosure, Jill, Labor, 150 hours
Parts, , Expense, $100
I have other tables that analyze this table of expenses with respect to total hours or total dollars.
Unfortunately, I have to repeat the task list in each of the other tables. Is there a way for me to input the task list in one location and have the list updated in the other tables? The task list grows and shrinks as tasks are added or are completed.
Thanks.
i have information in a table basically :
1 2 3 4 5 6 7
month cost cost cost cost cost cost cost
month cost cost cost cost cost cost cost
month cost cost cost cost cost cost cost
month cost cost cost cost cost cost cost
im using a combo box on a different page which gives me a column number for my selection
my data table at the top is defined as 'cost' and the column number defined as 'selectedcell'
underneath im using =index(cost,selectedcell,A1)
which is returning
month month cost cost cost cost cost.....
i cant fingure out how to correct it?
any help would be fantastic
Thanks
Tom
Hi all,
History: The Department I work for is converting its paper personnel files into electronic format.
Problem: We have 9000 files to prepare with an August 22 deadline. We are working in 6 hour shifts per workday. 6 hours/day; 5days/week. I need to predict the number of overtime hours we will need based on the number of files already complete, known hours remaining and known hours elapsed. We will continue the 6 hours/day in addition to the overtime. The project began June 12. Attached is the dataset I've compiled. The data is updated daily.
Here is the forecast formula I've used but it doesn't appear to be correct.
=FORECAST(C4,F4:F54,(IF(I4:I54>=1,I4:I54)))
Can someone please tell me where I'm going wrong?
Thank you
Hi all,
History: The Department I work for is converting its paper personnel files into electronic format.
Problem: We have 9000 files to prepare with an August 22 deadline. We are working in 6 hour shifts per workday. 6 hours/day; 5days/week. I need to predict the number of overtime hours we will need based on the number of files already complete, known hours remaining and known hours elapsed. We will continue the 6 hours/day in addition to the overtime. The project began June 12. Attached is the dataset I've compiled. The data is updated daily.
Here is the forecast formula I've used but it doesn't appear to be correct.
=FORECAST(C4,F4:F54,(IF(I4:I54>=1,I4:I54)))
Can someone please tell me where I'm going wrong?
Thank you
Hi All,
I previously had a problem that was solved with the wonderful help of Greg M, a valued forum contributor. He has been on vacation and may be too busy to help me, and I don't want to keep bugging him. Here is a link to that thread to help with background info:
http://www.excelforum.com/showthread...=645094&page=2
I am trying to retrieve data from many "Job cost" workbooks to a master "Job Cost Summary" workbook. The problem is that the data I am trying to retrieve is almost always in defferent cells. However, there are some consistencies that I outlined in the link above, but will repeat he
1. The data will always be found in the range B2:K25.
2. The Descriptions are always in column B and they are always in the same order from top to bottom starting in row 8.
***But they change rows based on what type of cost is entered on that job. For example, "Fabrication Labor" then "Erection Labor" then "Materials" will be in rows 8, 9, and 10 respectively. But if there is no "Erection Labor" on the next job, "Materials will move up to row 9.
(my thought on this is a code/formula that searches column B for a Description, if can't find it then Data=0, if it finds it begin searching that row for the data)
3. Regarding the columns where the data is stored, again the order is always the same from left to right staring with Description in column B, then comes Material Wt, Man Hours and Actual Cost.
***But, Material Wt, Labor Hours and Actual Cost are never in consistent columns, and they will not necessarily be nest to each other. The only consistency is that Actual Cost will be to the right of Material Wt and Labour Hours. AND, there are only two data entries per description: Material Wt and Actual Cost OR Man Hours and Actual Cost.
(continuing my thought above, search the row from left to right from C to K for the first nonzero value and that will be the Material Wt or the Man Hours, then search from right to left from K to C and the first nonzero value will be the Actual Cost)
I feel like if I can think it through, it can be written???
I am attaching some actual Job Cost files and the Job Cost Summary, which already contains the VBA code to retrieve bid data (module 1) which works beautifully (thanks again Greg M). I attempted to start the code to retrieve the cost data (module 2) but ran into the problems above.
Thanks,
Natasha
So I can't seem a way to do this, but I've heard before that you can link data labels to other data than the Chart is showing. For instance, I am comparing actual data to forecasted data. I want the data label to show the variance between the two in percentage form.
Ex.
Forecast = 200
Actual = 210
Variance = 10% = (210  200) / 200
I have a 3D Clustered Column in which it shows the Actual and Forecast. The only remaining part is the Variance that I want above those two columns.
Does anybody know how to do this?
I would like to assistance with developing formula to add 4 hours to baseline time. The time field is formatted in military time. I tried formula that referred cell with baseline time + 4 but this did not work. I have a dataset of over 1,000 records and I need to advance the baseline time of all records by 4 hours.
Trying to calculate a variance budgeted volumes vs actual volumes.
Actuals (A)=3
Forecast (F)=0
If I do the formula (AF)/F , I get a div/0 error. I am trying to show the % variance between the two numbers plus or minus. What is the correct formula for this?
Thanks!
Hi. I really suck at Excel and need help.
I'm making this chart for a fantasy league I'm in.
To buy certain players it cost an amount of points.
Like 98 overall player cost 60 points.
How do I make the excel sheet in way where if they enter the player overall, it will output the point value.
This is the value I have. I have also attached the Excel file
98 ovr......cost............60 points
97 ovr......cost............59 points
96 ovr......cost............58 points
95 ovr......cost............57 points
94 ovr......cost............56 points
93 ovr......cost............55 points
92 ovr......cost............54 points
91 ovr......cost............53 points
90 ovr......cost............52 points
89 ovr......cost............30 points
88 ovr......cost............29 points
87 ovr......cost............28 points
86 ovr......cost............27 points
85 ovr......cost............26 points
84 ovr......cost............25 points
83 ovr......cost............24 points
82 ovr......cost............23 points
81 ovr......cost............22 points
80 ovr......cost............21 points
79 ovr......cost............20 points
78 ovr......cost............19 points
77 ovr......cost............18 points
76 ovr......cost............17 points
75 ovr......cost............16 points
74 ovr......cost............15 points
73 ovr......cost............14 points
72 ovr......cost............13 points
71 ovr......cost............12 points
70 ovr......cost............11 points
69 ovr......cost............10 points
68 ovr......cost............9 points
67 ovr......cost............8 points
66 ovr......cost............7 points
65 ovr......cost............6 points
64 ovr......cost............5 point
63 ovr......cost...........4 points
62 ovr......cost............3 points
61 ovr......cost............2 points
60 ovr......cost............1 points
Hi,
I need to compare cost by actual vs forecast vs budget. and thinking of using pivot table for comparison. That is how i created a data worksheet. from there, i create a pivot table. However, when i tried to use calculated field to calculate each variance, it cannot be done with i what had in mind based on my current layout in source data.
Can someone here look into my source data and comment how to change it in order to get the variance in 1) budget vs actual, 2) forecast vs actual in pivot table.
Thanks
Sorry for this lame post.....
I've a cell showing the amount of hours spent on a project (68:45:00).
My hourly rate is $ 105
How do I convert the hours into a number which I can then multiply with 105
to get the correct cost?
Regards
Mr. Smith

