YTD Actual vs Plan help

C

Coyote44

New Member
Joined
Feb 25, 2008
Messages
6
I've got a spreadsheet that includes the following:

Column 1 - Month
Column 2 - Actual $
Column 3 - Plan $
Column 4 - Variance (column 3-2)

The spreadsheet has 12 rows for the fiscal months and a total column on the bottom.

What I'm trying to do is create a YTD trending total on a seperate sheet. My problem is I can't seem to get a formula put together to do this. in the YTD column, is it possible to have it ignore the 2008 plan that is already entered for each month?

Any help would be appreciated. My monitor is about 30 minutes aware from recycling;)!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Coyote44 said:
I've got a spreadsheet that includes the following:

Column 1 - Month
Column 2 - Actual $
Column 3 - Plan $
Column 4 - Variance (column 3-2)

The spreadsheet has 12 rows for the fiscal months and a total column on the bottom.

What I'm trying to do is create a YTD trending total on a seperate sheet. My problem is I can't seem to get a formula put together to do this. in the YTD column, is it possible to have it ignore the 2008 plan that is already entered for each month?

Any help would be appreciated. My monitor is about 30 minutes aware from recycling;)!
Click to expand...

Can you post an example of your data?
 
Upvote 0
Here you go. Used copy paste feature. Let me know what you think!
Another question, is there a way to conditional format the "plan" column to ignore any prefilled cells below the current month? What I'm also looking to do is to keep a YTD plan column which would only update if I entered in data in the actual column. Any thoughts?

<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=316 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=3 width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: maroon 0.5pt solid; BORDER-LEFT: maroon 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Month</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: maroon 0.5pt solid; BORDER-LEFT: #ff6600; WIDTH: 63pt; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>GM $ Actual</TD><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: maroon 0.5pt solid; BORDER-LEFT: #ff6600 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>GM Plan $</TD><TD class=xl67 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: maroon 0.5pt solid; BORDER-LEFT: #ff6600 0.5pt solid; WIDTH: 63pt; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>Variance</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>February</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3000000</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>-1000000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>March</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1000000</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>-1000000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>April</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>May</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>June</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>July</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>August</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>September</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>October</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>November</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>December</TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000000</TD><TD class=xl70 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: #ff6600 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ff6600 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: maroon 0.5pt solid; BORDER-BOTTOM: maroon 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>January</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600; BORDER-BOTTOM: maroon 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: maroon 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>5000000</TD><TD class=xl73 style="BORDER-RIGHT: maroon 0.5pt solid; BORDER-TOP: #ff6600; BORDER-LEFT: #ff6600 0.5pt solid; BORDER-BOTTOM: maroon 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>YTD</TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>$4,000,000</TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>$55,000,000</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>-2000000</TD></TR></TBODY></TABLE>
 
Upvote 0
Upvote 0
Actually it did. I did some further research in a book and found another solution as well. Thanks for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Best way to lay out financial data to build a dashboard
Replies
1
Views
143
GraH
GraH
N
  • Question Question
Stacked Chart to show value and status YTD
Replies
2
Views
856
neilo1969
N
Q
  • Question Question
Macros help on multiple files to be created from a set of large data
Replies
0
Views
509
questforexcel
Q
A
  • Solved
Apply A Formula to Increments of a Value
Replies
7
Views
290
Cubist
Cubist
T
  • Question Question
Catch up commission plan - need help with the formula
Replies
1
Views
467
StephenCrump
S

Forum statistics

Threads
1,225,817
Messages
6,187,193
Members
453,411
Latest member
healthcares

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