Email:      Pass:    Pass?
Hi!
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Forecast Formula

Forum Register
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

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
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.
Get Comment Text from Cell Comments in Excel - UDF
- This free Excel UDF outputs all text from a comment in Excel. This benefit of this UDF is that it doesn't display the c

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)+((A1-40)*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 =D3-D4 E5 =E3-E4 F5 =F3-F4 G5 =G3-G4 H5 =H3-H4 D9 =D7-D8 E9 =E7-E8 F9 =F7-F8 G9 =G7-G8 H9 =H7-H8 D13 =D11-D12 E13 =E11-E12 F13 =F11-F12 G13 =G11-G12 H13 =H11-H12 D17 =D15-D16 E17 =E15-E16 F17 =F15-F16 G17 =G15-G16 H17 =H15-H16

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 0-1 hours cost 1 dollar
if 1-2 hours cost is 4 dollars
if 2-3 hours cost is 7 dollars
if 3-4 hours cost is 10 dollars
if 5-24 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+(J9-40)*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 Jan-Apr. 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 non-zero 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 non-zero 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 (A-F)/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