0 ratings0% found this document useful (0 votes) 10 views8 pagesForecast Chart STEPS
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content,
claim it here.
Available Formats
Download as PDF or read online on Scribd
—
Menu
—
Home » Visualization » Forecast Chart
Forecast Chart
Last Updated on: November 30, 2022
Learn how to build a forecast chart in Excel! It is a custom combination chart that uses
one column and two line charts. This article will show you how to create a simple forecast
chart using conditionally formatted colors.
What is a forecast chart?
Let us see the main task: we want to show the trend of the last period. In standard graphs,
you can configure this view with a red arrow at the end of the line if we are in a recession
and green if there is a rise. Prepare Data
Okay, prepare the data!
The secret is to create a table using three series using the Data, Positive and Negative
labels.
We will apply a green arrow if the last month's performance is better than the past period.
Elsewhere we will use a red arrow.
We should have to leave empty the last cell in the Data row. Why? The answer is simple.
We don't want to show this point on the chart. How to do that?
Use the “Not applicable” function to hide the given data points for charts. For example,
enter the function in cell H8. sum =] i [x ve nal
A B c D E F G H
JAN FEB. MAR = APR) MAY _JUN
2
3 Sales 50 45 6533 roo ES
4
5
6 Data Table for Charting
7 JAN FEB. MAR = APR MAY
8 Data 50 45 33 100}
9 Positive #N/A #N/A #N/A #N/A #N/A #N/A
10 Negative #N/A #N/A #N/A #N/A
Let us see the positive and negative series!
The first four months are unnecessary because we want to display the differences between
the last two months.
To hide the unnecessary lines, we will enter the “Not applicable” function into the range
from cell C9 to cell F10, Then, we'll calculate the missing values using a simple IF function.
We will hide the positive series if the actual value is higher than the previous months.
If the actual value is lower than the previous month's value, we will show the data points
for the red arrow.
Apply the following formula for cell G9 and cell H9.
F (H39G3,63,NA())
F (H3>G3,H3,NA()) ga ~ fe =F(H363,62,NA())
A B c D E F G H
1 Data
2 JAN FEB MAR APR MAY JUN
3 Sales so 456s Ss 33100,
4
5
6 Data Table for Charting
7 JAN FEB. MAR = APR) MAY JUN
8 Data 50 45 65 33 100 #N/A
9| Positive #N/A #N/A #N/A #N/A) #N/Al #N/A
10 Negative #N/A N/A BN/A NZ
Use this formula for the negative series too.
410 © fe -=AF(H3<63,H3,NA())
A B c D E F G H
1 Data
2 JAN FEB MAR = APR) MAY__JUN
3 Sales so 4s cs Ss 33100 IE
4
5
6 Data Table for Charting
7 JAN FEB MAR APR MAY JUN
8 Data 50 45 65 33 100 ~#N/A
9 Positive #N/A #N/A #N/A N/A #N/A_ #N/A
0 | Negative #N/A #N/A #N/A #N/A t00[__ 98]
Create a sales forecast chart
Now select the data range! Next, click on the Insert Tab and choose a line chart.
We will format the chart based on our rules. First, type a value in cell H3 greater than the
previous month to create a green line. Chart Title
700
80
60
20
JAN Fea MAR APR MAY JUN
Data Positive ae Negative
Select the last data point on the chart! Right-click and select Format Data Series from the
list. Change the default color to green in the "Fill & Select” section.
4 ine
O Wotne
© Sel ne
Gradient ne
Automatic
Color
wath
Compound ype
Dos ope
capype
inope
ein Arne ype
MAR APR may JUN Begin iow ase
End arow ype
End Anew size — >>
7 Smooth ine +.
Data — Positive mp Negative
° Locate the ‘End Arrow Type’ field and click on the arrow icon. Now change the value in cell
H3 and enter a smaller value than the previous month's data.
Change the default color to red in the “Fill & Select” section.
Format the chart, and add a chart title and markers for the data points if you want.
Conclusion
To build a Sales forecast chart, you have to use only a few small tricks, like the NAQ Excel
function.
That's all. Download the Excel Workbook!
Additional resources:
‘* Download Free Chart Templates
* Progress circle chart
* Apply conditional formatting for a chart
Category: il Visualization
Istvan Vozar
Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps
people to reach the top in Excel.
Ultimate Dashboard Tools © 8
é
@ N
Bullet ScoreMeter ScoreMeter Variance
LEE, SORE SGNGE ee
i hil rl
Variance — Variance ~—-Variance ‘Waterfall
2. 3 a Charts ~
TeeMap Gantt On
a os
Pro Dashboard Add-in for Excel, Fully automated. Lightweight, No coding skills required.
Get it now
Topics
Formulas
Functions
Excel Dashboard
Shortcuts
Excel Tables
Data visualization in Excel
Privacy Policy Terms of Service Refund Policy Contact us About Us ‘© 2019-2022. ExcelKid is a trademark of Visual Analytics Ltd.
