Pivot Table - Forecast at completion based on actuals to date

R

ramgouda

New Member
Joined
Mar 16, 2014
Messages
7
Hello All,

I am newbie here and struggling to find a solution for an excel calculation in Pivot tables.

have a simple pivot table with the source data having the information about Month, Project, Workstream, task name,Org ID, resource, planned hours, actual hours, planned cost and actual cost. There are three report filters Month, Project ID, Workstream ID on the pivot table.

I need to create two calculated fields 'Forecast at completion-hours' and 'FAC - Cost', on the pivot table.
I am looking for two solution options.
1. based on the current month, the formula should pick actual up to current month and add the planned cost for rest of the year. e.g for june, FAC = actual upto may+planned from June to DEC.
2. If possible, create a drop down to allow the users to chose the month they want to see the FAC and use that month value to calculate the FAC.

It would be helpful even the first option is achieved. That would work for me.

Could you please advise me how to go about it?

I don't know how to attach my sample excel file here on the forum, so below is the sample data and the pivot table.

Source Data:
[TABLE="width: 676"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Project ID[/TD]
[TD]WorkStream ID[/TD]
[TD]Task Name[/TD]
[TD]Org[/TD]
[TD]Resource[/TD]
[TD]Planned Hours[/TD]
[TD]Actual Hours[/TD]
[TD]Planned Cost[/TD]
[TD]Actual Cost[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P201[/TD]
[TD]WS268[/TD]
[TD]T1[/TD]
[TD]OA01[/TD]
[TD]Name 1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]$560.00[/TD]
[TD="align: right"]$520.00[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P202[/TD]
[TD]WS298[/TD]
[TD]T2[/TD]
[TD]OA02[/TD]
[TD]Name 2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$280.00[/TD]
[TD="align: right"]$280.00[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]P203[/TD]
[TD]WS299[/TD]
[TD]T3[/TD]
[TD]OA03[/TD]
[TD]Name 3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$200.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]P201[/TD]
[TD]WS268[/TD]
[TD]T1[/TD]
[TD]OA04[/TD]
[TD]Name 4[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]$1,120.00[/TD]
[TD="align: right"]$1,120.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]P202[/TD]
[TD]WS269[/TD]
[TD]T2[/TD]
[TD]OA05[/TD]
[TD]Name 1[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]$1,400.00[/TD]
[TD="align: right"]$1,520.00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]P201[/TD]
[TD]WS269[/TD]
[TD]T3[/TD]
[TD]OA06[/TD]
[TD]Name 2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]$1,400.00[/TD]
[TD="align: right"]$1,520.00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]P202[/TD]
[TD]WS268[/TD]
[TD]T1[/TD]
[TD]OA07[/TD]
[TD]Name 3[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]$1,680.00[/TD]
[TD="align: right"]$1,440.00[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]P201[/TD]
[TD]WS299[/TD]
[TD]T4[/TD]
[TD]OA08[/TD]
[TD]Name 4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$560.00[/TD]
[TD="align: right"]$800.00[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]P202[/TD]
[TD]WS268[/TD]
[TD]T1[/TD]
[TD]OA09[/TD]
[TD]Name 5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$280.00[/TD]
[TD="align: right"]$280.00[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]P201[/TD]
[TD]WS298[/TD]
[TD]T3[/TD]
[TD]OA10[/TD]
[TD]Name 6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$280.00[/TD]
[TD="align: right"]$280.00[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]P203[/TD]
[TD]WS268[/TD]
[TD]T2[/TD]
[TD]OA11[/TD]
[TD]Name 1[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]$2,240.00[/TD]
[TD="align: right"]$2,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]P203[/TD]
[TD]WS267[/TD]
[TD]T2[/TD]
[TD]OA12[/TD]
[TD]Name 2[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]$1,960.00[/TD]
[TD="align: right"]$2,400.00[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]P204[/TD]
[TD]WS268[/TD]
[TD]T5[/TD]
[TD]OA01[/TD]
[TD]Name 4[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]$1,960.00[/TD]
[TD="align: right"]$2,240.00[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]P205[/TD]
[TD]WS298[/TD]
[TD]T3[/TD]
[TD]OA03[/TD]
[TD]Name 4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD="align: right"]$2,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]P202[/TD]
[TD]WS289[/TD]
[TD]T6[/TD]
[TD]OA02[/TD]
[TD]Name 5[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$560.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]P203[/TD]
[TD]WS290[/TD]
[TD]T7[/TD]
[TD]OA05[/TD]
[TD]Name 6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$1,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]P204[/TD]
[TD]WS291[/TD]
[TD]T8[/TD]
[TD]OA04[/TD]
[TD]Name 7[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$1,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]P205[/TD]
[TD]WS292[/TD]
[TD]T9[/TD]
[TD]OA12[/TD]
[TD]Name 3[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$2,240.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]P201[/TD]
[TD]WS268[/TD]
[TD]T10[/TD]
[TD]OA14[/TD]
[TD]Name 5[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$4,480.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]P204[/TD]
[TD]WS267[/TD]
[TD]T11[/TD]
[TD]OA14[/TD]
[TD]Name 4[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$4,480.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]P205[/TD]
[TD]WS298[/TD]
[TD]T12[/TD]
[TD]OA14[/TD]
[TD]Name 2[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$4,480.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]P202[/TD]
[TD]WS299[/TD]
[TD]T13[/TD]
[TD]OA07[/TD]
[TD]Name 1[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$4,480.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]P203[/TD]
[TD]WS290[/TD]
[TD]T1[/TD]
[TD]OA08[/TD]
[TD]Name 8[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$2,800.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]P201[/TD]
[TD]WS291[/TD]
[TD]T2[/TD]
[TD]OA14[/TD]
[TD]Name 7[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$2,800.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]P205[/TD]
[TD]WS292[/TD]
[TD]T3[/TD]
[TD]OA13[/TD]
[TD]Name 6[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$2,800.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]P206[/TD]
[TD]WS298[/TD]
[TD]T4[/TD]
[TD]OA12[/TD]
[TD]Name 5[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$2,800.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]P208[/TD]
[TD]WS267[/TD]
[TD]T5[/TD]
[TD]OA11[/TD]
[TD]Name 10[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,040.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]P207[/TD]
[TD]WS299[/TD]
[TD]T6[/TD]
[TD]OA01[/TD]
[TD]Name 11[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,040.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]P204[/TD]
[TD]WS268[/TD]
[TD]T7[/TD]
[TD]OA05[/TD]
[TD]Name 12[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,600.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]P201[/TD]
[TD]WS289[/TD]
[TD]T8[/TD]
[TD]OA06[/TD]
[TD]Name 9[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,600.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]P202[/TD]
[TD]WS290[/TD]
[TD]T9[/TD]
[TD]OA06[/TD]
[TD]Name 8[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$3,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]P202[/TD]
[TD]WS289[/TD]
[TD]T10[/TD]
[TD]OA09[/TD]
[TD]Name 10[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$3,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]P203[/TD]
[TD]WS290[/TD]
[TD]T11[/TD]
[TD]OA09[/TD]
[TD]Name 6[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$3,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]P204[/TD]
[TD]WS291[/TD]
[TD]T12[/TD]
[TD]OA03[/TD]
[TD]Name 11[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$3,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]P205[/TD]
[TD]WS292[/TD]
[TD]T8[/TD]
[TD]OA02[/TD]
[TD]Name 12[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$3,920.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]P202[/TD]
[TD]WS289[/TD]
[TD]T7[/TD]
[TD]OA01[/TD]
[TD]Name 1[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,600.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]P208[/TD]
[TD]WS267[/TD]
[TD]T5[/TD]
[TD]OA14[/TD]
[TD]Name 2[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,600.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]P206[/TD]
[TD]WS268[/TD]
[TD]T4[/TD]
[TD]OA12[/TD]
[TD]Name 3[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,600.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]P208[/TD]
[TD]WS292[/TD]
[TD]T1[/TD]
[TD]OA03[/TD]
[TD]Name 4[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$5,600.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]
Pivot Table:
Month, Project ID and Workstream are report filters.
[TABLE="width: 706"]
<tbody>[TR]
[TD]Month[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD="colspan: 3"]Calculations I am looking for is:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project ID[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD="colspan: 6"]FAC Hrs at June = Actual Hrs upto May + Planned Hrs from June to Dec[/TD]
[/TR]
[TR]
[TD]WorkStream ID[/TD]
[TD](All)[/TD]
[TD][/TD]
[TD="colspan: 6"]FAC Cost at June = Actual Cost upto May + Planned Cost from June to Dec[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Org[/TD]
[TD]Resource[/TD]
[TD]Task Name[/TD]
[TD]Plan Hrs[/TD]
[TD]Actual Hrs[/TD]
[TD]Planned Costs[/TD]
[TD]Actual Costs[/TD]
[TD]FAC-Hr[/TD]
[TD]FAC-Cost[/TD]
[/TR]
[TR]
[TD]OA01[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]329.00[/TD]
[TD="align: right"]69.00[/TD]
[TD="align: right"]$13,160.00[/TD]
[TD="align: right"]$2,760.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA02[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]119.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]$4,760.00[/TD]
[TD="align: right"]$280.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA03[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]293.00[/TD]
[TD="align: right"]55.00[/TD]
[TD="align: right"]$11,720.00[/TD]
[TD="align: right"]$2,200.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA04[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]76.00[/TD]
[TD="align: right"]28.00[/TD]
[TD="align: right"]$3,040.00[/TD]
[TD="align: right"]$1,120.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA05[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]223.00[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]$8,920.00[/TD]
[TD="align: right"]$1,520.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA06[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]273.00[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]$10,920.00[/TD]
[TD="align: right"]$1,520.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA07[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]154.00[/TD]
[TD="align: right"]36.00[/TD]
[TD="align: right"]$6,160.00[/TD]
[TD="align: right"]$1,440.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA08[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]84.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]$3,360.00[/TD]
[TD="align: right"]$800.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA09[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]203.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]$8,120.00[/TD]
[TD="align: right"]$280.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"]$280.00[/TD]
[TD="align: right"]$280.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA11[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]182.00[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]$7,280.00[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]315.00[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]$12,600.00[/TD]
[TD="align: right"]$2,400.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]70.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]$2,800.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OA14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]546.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]$21,840.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2874.00[/TD]
[TD="align: right"]415.00[/TD]
[TD="align: right"]$114,960.00[/TD]
[TD="align: right"]$16,600.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Apologies for the lengthy post. Any help is massively appreciated.

..Ram
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You must log in or register to reply here.

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top