Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 Tutorials

Get Formulas from Cells in Excel with VBA
Get a formula from a cell using VBA in Excel. This method returns the actual formula instead of the output value. G ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
How to Input, Edit, and manage Formulas and Functions in Excel
In this tutorial I am going to introduce how to input, edit and manage excel formulas. To start entering a formula, ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...

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

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




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


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?


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!


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.


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







I am trying to write a macro to hide various columns based on a list selection at cell B3.

The following is a rundown....

Columns C:G contain actual/forecast/budget/variance to forecast/variance to budget details....

Cell B3 has a drop down list that gives the following options.

1. "Hide Month" - i.e. to hide all columns relating to the month C:G
2. "Month to budget" - i.e. to hide columns relating to the month C:G other than the Actual column (C) budget column (E) and variance to budget column (G)
3. "Month to forecast" - i.e. to hide columns relating to the month C:G other than the Actual column (C) forecast column (D) and variance to Forecast column (F)

I have currently pulled in bits and pieces from other macros I use however with every selection all columns hide... Quite frustrating!

Any help would be appreciated and rewarded with Kudos!



Please Login or Register  to view this content.



For each month of the year, I have a budget column with sales and cost data
(e.g. in column B) and then next to that I have a column where the actual
data goes when the data is available.


Col B Col C Col D
Col E
1m Budget 1m Actual 2m Budget 2m
Actual

Row7 Sales $100 $110 $105

Row9 Labor Cost $40 $55 $60

I want to be set up a formulae to be able to sum the data for all 12 months
for each row using the budget data, but as the actual data gets filled in and
becomes available I want the actual data to be used instead of the budget
data for that month so the total year forecast becomes a combination of
actual data and budget.

For example the original forecast for sales would have been 100 (month 1
budget) + 105 (month 2 budget) = 205 but now the revised sales forecast is
110 (actual month1) + 105 (month 2 budget) = 215. The data runs from column B
through to Y.

Do I use a sumif if a value is in the actual columns and how does this get
set up to check the actuals automatically and sum them instead of the budget
columns to the left if they exist.

Many Thanks!




I am trying to find a template that can add up my colleagues hours ie. start
time and finish time minus any breaks then multiply the resulting hours by a
cost rate per hour.






How should I create a formula in Excel that calculates overtime considering both daily and weekly hourly totals?

I am creating a weekly expense report that includes pay

Daily Hours 8 $15.00
Weekly Hours > 40 $15.00
All hours count toward weekly total
Saturday, Sunday treated like any other day
Work week starts on Sunday

Let's say I work 15 hours a day

Sunday
8 hours @ $10.00 ea.
7 hours @ $15.00 ea.

Monday
8 hours @ $10.00 ea.
7 hours @ $15.00 ea.

Tuesday
8 hours @ $10.00 ea.
7 hours @ $15.00 ea. - over 8 for the day and reached 40/wk

Wednesday (and rest of the week)
0 hours @ $10.00 ea.
15 hours @ $15.00 ea. - over 40/wk

I appreciate your comments!


Hellppp!!

I'm doing up a timesheet which has got 3 columns. It contains "Ordinary Hours", "Time & Half" and "Double Time".

I require a formula which calculates the cost automatically. If I put in 10 hours Mondays to Friday I need Excel to calculate 7.2hrs for Ordinary Hours eg $50, and 2 hours Time & Half $55 and 0.8 hrs $59 for Double time. I'll probably need one formula in a cell and the rest I'd probably be able to adopt the formula.

On Saturday it's first 2 hours for Time & Half and 8 hours on Double Time.

Would anyone be able to help me with this?

Regards

Euwest39


I'm attempting to create an accurate Sum within a PivotTable.

The scenario is a job# has employees that work on it. What's the total labor cost?
Assume each employee earns $10/hr..

Job # / Emp Tot Hours TotCost1 TotCost2
Job 1000 15 Hours $150.00 $300.00
Emp A 10 hours $100.00 $100.00
Emp B 5 Hours $ 50.00 $50.00

I can achieve the desired answer of $150 by storing the Cost as a calculated field in my data source. So far, my attempts to use a calculated pivottable field to achieve the result, thus not relying on storing a calculated value, has resulted in the last column (TotCost2) above. The line items appear correctly but the aggregated total sums the rates, and then multiplies by the hours, giving me an inflated total cost #. In the example above I'm guessing the $300 represents (10 hours + 5 Hours) * ( $10 + $10) = (15 Hours)* ($20/Hr).

What I'd want to accomplish with a calculated field is (10 Hours * $10/Hr)+(5 hours* $10/hr) = ($100)+($50)=($150)

It goes against my experience in MS Access to store a calculated value. I'd much prefer to have the pivot table calculate the value as needed. If that's not a valid practice with Excel pivot tables, please advise.

Attached is a workbook with the problem presented in a simple format.

Am I missing something fundamental here? Is there a Sumproduct type ability on aggregated Sum lines within the PivotTable framework??

Any suggestions most welcome!

Thanks,
Shred


Hi all,

I have a workbook with expenses categoried by each cost centre recorded in different worksheets from Jan to Dec 09 (Jan - June 2009 is actual while July - Dec 09 is forecast). Now i am asked to prepare the expense with the breakdown in accordance to the cost centre in one worksheet for twelve month period.

As i am pressed for time, i wonder whether there is a quick way to link all these worksheets. FYI, there are 73 cost centres.

Thanks for all the input in advance.


I have a date range (say 2008-9-29 to 2008-10-5) and between those two dates is 100 hours of work. I need to come up with a formula that will split the hours into the respective months using 19.5 hours/day as a baseline. Such as:

9-29=19.5
9-30=19.5
10-1=19.5
10-2=19.5
10-3=19.5
10-6=2.5

I do not need them put in individual days, but it should put 39 hours into September and 71 hours into October. This would need to take into account the NETWORKDAYS function, so weekends are not counted. Not sure if I explained it well enough but any help anyone could give me would be appreciated. Thanks.