Forecast Formula

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

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.

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

Hi everyone

been ages since I have been on here look for advice so please be gentle.

I have a worksheet with the following data set up:-


A B C D E
1 Area Dec-08 Jan-08 Feb-08 Mar-08
2 Current Forecast 8 10 14 18
3 Last Months FOrecast 5 10 12 16
4 Variance 3 0 2 2
5 Comments - Merged Cell to column R
6 Area Dec-08 Jan-08 Feb-08 Mar-08
7 Current Forecast 4 7 8 3
8 Last Months FOrecast 2 5 6 3
9 Variance 2 2 2 0
10 Comments - Merged Cell to column R

I need to sum each column for each section, thereofre creating a similar table at the bottom of my worksheet which bypasses the merged cell and date and only sums the relevant sections (so something like =sum(B2+B7, etc) and for 15 columns.

Now I know the lazy and long winded way is to individually code a loop for each column and Forecast.Variance and Current but that would take absolutely ages and make my macro huge (the compilation of the table is part of the macro)

I havent used arrays before and I was wondering if there was an easy way to do this?

Any help would be greatly appreciated.

Thanks
Sean

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.