Free Microsoft Excel 2013 Quick Reference

Chart Help - Actuals vs. Forecast

I have a 3 year chart that shows the net sales by month in a column format. The 3 years are 2007, 2008 and 2009. What I would like is that the lines or fill of the columns for months that have past would be one color or style, while the lines or fill of the columns for future months would be another.

For example, right now, I would want all the columns from January 2007 through July 2008 (The Actuals) to be smooth line style, while the months August 2008 through December 2009 would be dotted line style.

The data is in one row, and I would like to have it so that each month I do not have to do anything except designate the Current Month and the styles will be applied.


Post your answer or comment

comments powered by Disqus
Hi folks
Will try to be brief:
I want to do some actual vs forecast performance analysis using a pivot table and chart.
I have attached an example of what I am trying to do, and my attempt at the pivot table.
My data source (excel dump from external software) lists each Forecasts and Actuals by each month across the top.
When I try to get this into a pivot table, I cant work out how to differentiate the actuals from the forecasts. I want to be able to dynamically analyse by project, manager, and site (example charts on the sheet attached).
I would really appreciate any help anyone can offer, thank you so much!

I have a line chart with weekly data for 2 years. Weeks on the X axis and
Pounds on the Y axis. They represents actual and forecasted pounds. I need to
show the first portion of the line (actual) with solid color while the rest
of the line (forecast) with dotted line. We are talking about one single line
for production. How can I accomplish this? I don't want to do it manually.
I'd rather have it changes automatically every week when the actual data
override the forecasted data.

Thanks.

sahafi

Guys -

Aim - I need to visualy represent our membership statistics in a monthly dashboard

I have attached figuers which I recieve from accounts to show you what I receive - I have supplied Jan's which show budget and forcast for the year. I have also attached Junes which is when we re-forcasted and Aug's, the latest figuers.

The overall target for the year is made up of New members/renewing members and life members.

Could someone please take a look and maybe offer some advice/ideas/comments/suggestions on how to best present this data in a dashboard?

If the re-forecast did not take place then i would simply present cumulative totals Actual vs budget. but i'm not sure how to cater for this given the re-forecast.

Additionally, each month, previous months actual figures change ever so slighlty.

Please, for those of you who have done this stuff before, take a look and spread the knowledge!

I am trying to show a "actual" daily amount in a chart against the 3 targets set. The 3 targets set make up an amount in %, so i would like to show the actual as a percentage (to the normal target, not min or stretch)
Is this possible? I have attached a sheet of what i have so far, but just need to get my "actual" total on the sheet showing how far i have to go, or how much i still need to do.
Any help appreciated.
Thanks

I am trying to show a chart that shows the percentage of an actual vs. a plan number, but it is not showing properly.

I have the following numbers:

Plan Actual
10900 10000

When I chart these two numbers I get Plan showing as 52% of the chart and Actual at 48%. I want it to show the actual at 92% (actual/plan).

How do I do this?

Okay, I've got 3 years worth of sales, returns, cancels, GM%, etc. I want to give a forecast for the coming fiscal year. But at the same time as the year progresses, I want to 1)keep up with actual vs forecast AND 2)have the forecast change for the remainder of the year as I enter fy09 months actual data. How difficult will that be? I'm sure there is a canned formula or data analysis pak tool that will do it, but I'm unfamiliar. Any help is appreciated.

I'm just a beginner at writing macros. And I'm really lost with proper
syntax in order to point to the desired directory location where we need the
file saved.

I recorded the macro, and then edited respective to tab names, etc. I'm
thinking it's maybe the syntax around the TimePeriod string. I'm also not
familiar with all the SaveAs conditions.

Here's the statement that where I get the error:

ActiveWorkbook.SaveAs([Q:FinanceForecast2007"&(TimePeriod"TRGPrelim
Actual vs
Forecast],[.xls],[""],[Normal],[""],[""],[False],[False],[""],[""],[""],[""])

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

I'm having some issues using DIR(), the code below (which I found on this forum ) worked fine when I first set it up and ran it. I was able to run it multiple times with it opening and copying the information wanted. The issue is that once I close Excel and reopened the file, the code will no longer work and it's giving me a run-time error 1004: application-defined or object-defined error when trying to execute the line of code Workbooks.Open wbName (or atleast that when it stop executing but could it be the line above?). If I open another workbook and add this code, it will again work until I save/close/reopen. As you can probably tell, I'm by far no expert in VBA, I'm thinking that wbName is being stored with the file path and file name and it's not clearing out when I close and reopen Excel?

Sub GetForecastData2()

Dim DeptFolder As String
Dim MyPath As String
Dim wbName As String

MyPath = "FinancialGeneral Ledger, Revenue, Forecast filesCash Flash"
DeptFolder = "DCF_Data_Files"

wbName = Dir(MyPath & DeptFolder & "" & "2012 HEI Forecast*.xlsx")

    Do While Len(wbName) > 0
        Workbooks.Open wbName
        wbName = Dir
    Loop
Set WkbkTemp = ActiveWorkbook

Sheets("ACTUAL VS. FORECAST").Select
    ActiveSheet.Cells.Copy
    Windows("Data.xlsm").Activate
    Sheets("HEIForecast").Select
    With Cells(1, 1)
    .PasteSpecial xlValues
    .PasteSpecial xlFormats
    End With
    Cells.Select
    Selection.MergeCells = False
    WkbkTemp.Activate
    Application.CutCopyMode = False
    ActiveWindow.Close SaveChanges:=False

End Sub


Hi,

Need your support on the following:

I am looking to calculate the actuals vs forecasted values based upon a waterfall calculation as attached (tab overview)

I am basically looking out a way to calculate the actuals vs forecasted values based on some % increament. Any suggestion/support around it would be welcome.

I need to span the budget as follows:
In Jan month, we would spend 100 and rest 11 month will be forecasted..
in feb month, we would spend 100 + 100 and rest 10 month will be forecasted..

like this when we reach Dec, we would have just 1 actual 100 rs to spend...

at the end, I would like to calculate the actuals spent and forecasted amount...

looking forward to your support. Thanks in advance.

Hi all.

I would like to chart actual vs budgeted expenditure in a bar chart in a single bar. By this I mean that if 70% of budget expenditure has been incurred, the bar should only be 70% filled.

So far my efforts have failed. Can anyone suggest a solution?

FD

I have a table of data, let's call it ACTUAL SALES by month, similar to
following:

Business Unit West East North South
Jan09 20 30 15 10
Feb09 10 20 30 40
Mar09 14 16 12 10

I have a table that looks like it, for PLANNED SALES data, with various
numbers for each business unit's planned target for the same periods for
which I have actuals.

What I want to do is (ideally) create a stacked bar chart of the Actuals for
each region (ie, the height of the stack = total actual sales), with a "line"
showing the summarized "planned" or target sales for each region (ie, if the
stacked bar does not reach the total PLAN line, then we are under target).

My second choice would be a stacked line, but would not convery the picture
as nicely. I am using excel 2007.

I can (re)-organize this data however i need to, to make this work, but am
not sure how to do this.

Thanks for any help, guidance!

PatK

I have a pre-existing donut chart for a dashboard. This was previously used to illustrates
% of actual results relative to target sales. (10,000 actual vs 15,000 target = 67% of target)

I want to use this as dashboard for 'response rates' which is already expressed as a %. (example : actual direct mail response rate is 11% vs target response rate of 23%)

How can I set up this dashboard where minimum value is formatted as 0% minimum value and maximum value is 23% (target response)

Thanks for your help.

Hello everyone,

my first post here, and I'm in with a bone question straightaway, but I hope you can help.

I have 2 columns, one for altitude, going from 0 - 25000' in 5000' increments, and another for the absolute temperature, in K, falling from 288.15 to 238.65 linearly, iaw an equation for temp vs altitude which I am happy to say I managed without difficulty.

What I want to achieve, is a chart with Altitude on the 'y' axis and Temperature on the 'x' axis, and a plot of the temperature falling with increasing altitude - ie a negative gradient line.

What I actually get when I go through the chart wizard is as follows:

http://i290.photobucket.com/albums/l...ipps/Graph.jpg

where as you can see, the x axis appears to be some random control numbers from 0 - 6 and the y axis is altitude. However, the temp is also plotted wrt to the same y axis scale as the altitude, which means (due to the small temp numbers in volved) that the temp line is more or less flat across the bottom of the chart.

I have tried all sorts of messing around to get this to work, and I know now that I am missing something - probably to do with the original layout of the data and the way it is then selected to create a chart from.

Please help - I need this skill for a new job and even the Excel tutor on my level 2 course today couldn't help me!

Thanks in advance,

Russ

Hi,
I have made a line chart with 3 series, being, Planned/Forecast/Actuals, my manager would like the line chart to show the Planned, but the Forecast and Actuals to be one line, where the line changes colour each month as the actuals are entered. Does anyone have any ideas please? I have been working on this for the last month or more and just can't get it to work

Please Help.

Cheers
Blueiis.

I am trying to create a sum of values, relative to two conditions. The sum will tell me what the forecasted revenue in a given account will be over a period of time. I have a series of columns labeled with month names, and whether that column is a Forecasted or Actual value. We need to keep and track the forecasted value, so each actual and forecasted value is in a separate column.

The total forecasted revenue, which is what I am trying to calculate, is the total of actual revenues, plus the total of forecasted revenue for months that have no actual revenue (these months would be in the future). I figured that if I defined a future month as having a "BLANK" actual revenue value, then I could have a formula test to see if the Actual Revenue for the month is BLANK, then use the Forecasted Revenu in the sum. Otherwise, use the Actual Revenue. I have to use the BLANK instead of zero, because it is conceivable that any given client could have no revenue in a given month.

I thought I had this working, but it's not. I've included a sample of my layout and what I am trying to achieve. I just added the values to get the results in teh Forecasted Revenue column.

Thanks for any help!
F

i want to produce a chart to show a projects spend over the next year.

I have 2 months actual spend (Apr & May) and the coming months are all 'Forecast' figures.

i want to produce a graph (line) to show the cumulative spend of the project (I can manage this), but when you look at the graph i want it to show the months which are actual in one colour, and the forecast spend in another colour. All without manual intervention of right clicking and formatting the individual data point.

can anyone give me any idea how to do this?

cheers

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!

Can anyone help me with the attached chart?

The first chart is how it currently looks, if you look at the small
black section you can see it tapers off.

however i would like it to look like the second chart and end abruptly
in a straight line.

Im using a combination of Line and Area charts.

the data is in a 3 rows.

Budget: cumulative
Won: this only shows data in the past. (also cumulative)
Forecast: only shows data in the future. (also cumulative)

i.e

month 1 | 2 | 3 | 4

Budget: 1,000|2,000|3,000|4,000
Won: 800|1,600| 0| 0
Forecast: 0| 0|2,000|5,000|

Any ideas cos im stumped?

+-------------------------------------------------------------------+
|Filename: Group-Forecast.-both.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=3454 |
+-------------------------------------------------------------------+

--
jarvo
------------------------------------------------------------------------
jarvo's Profile: http://www.excelforum.com/member.php...o&userid=18983
View this thread: http://www.excelforum.com/showthread...hreadid=376299

Ok, I am trying to make a stacked chart that starts at points other
than zero but can still have the data label show the actual point.
Even more complicated is I would like to have a label that is based on
a point somewhere in the range of the stack.

Here is a sample of what I am looking for:
The start point mid point and end point I want to display the stacked
chart a
10
15
20

Currently I just use 10,5,5 as my data for the chart and then just hide
the first column in the stack (0-10).
I would like to be able have the very bottom stack start at 10 so that
there are just two stacks in this data plot, 10-15 and 15-20.
Then I sould like to be able to plot on the stacked chart a seperate
data point that is somewhere in the range - say 17.5. This data point
will change so really this chart is kind of a slider chart.

I have been messing with this one for a while now and am just stumped.
Any help is greatly appreciated.

Will

I am trying to generate a stacked bar chart (of sorts) for the following
information:

Country:
Actual Q1: $150,000
Actual Q2: $87,500
Actual Q3: 0
Actual Q4: 0
Budgeted: $1,775,000
*I have multiple countries as well - but if I can sort out one, I'll be fine*

What I'm trying to show is the usage of the Overall Budget by Quarter:
___
|||||
|||||
|||||
-----
|Q2|
| |
-----
| |
|Q1|
| |
------

The ||||| part is the unused budget (Budgeted - SUM(Q1:Q4))
Q1 value is the Actual amount above.
Q2 value is the Acutal amount above.
Q3 and Q4 would both have 0 values.

Is there a way to set this up to achieve what I'm looking for? I know its
possible, the question is, How?

Thanks in advance.

--
Dawg House Inc.
"We live in it, therefore, we know it!"

I have a financial forecasting spreadsheet with the following values:

1. Forecast for the month
2. Actuals for the month
3. Variance

At the end of a one year period, I have total forecasted for the year, actuals spent for the year, variance and forecast to complete. This is the value I need help with.

The forecast to complete field needs to calculate the following:

We are in the month of August (month 8) and up until July we have actuals values populated within each month. Is there a formula which can add the forecast fields from August through to December considering the fact that actuals have been populated for all previous months?

Thanks.

Hi,
I'm looking to create a chart where I can give users the option to select what items they want to look at on the chart.

For example, if I have line chart plotting out monthly trends, I would have 3 options:
1. Budget
2. Actuals
3. Forecast

Instead of having all 3 lines show up at the same time, the user would have the option of selecting just one of the 3 items, or 2 of the 3 items, or maybe even all 3 items.

Can someone help me with this?

Thank you so much in advance!

Hi,

I have the data as in the attachment. I am trying to make a line chart to compare the progress of Focus (FC) vs. Actual (AC). However, i cannot make it this out. Seem i always get only one line.

Could you please advice? Your comments will be very much appreciated.


No luck finding an answer? You could always try Google.