Create essential financial charts

Welcome to our guide on creating impactful financial charts. As an FP&A professional, you know the power of visualizing data to inform strategic decisions. This article will guide you through creating three critical charts in your spreadsheets using your Cube data:

• Trend charts to track quarterly revenue trends by market
• Variance analysis of actuals vs. budget with column charts
• Line charts with thresholds to track cash on hand or runway

If you are new to Cube, you may want to learn more about:

• Building ranges
• Fetching or pulling data from Cube to your spreadsheet
• Scenarios such as your actuals, budgets, and forecasts

The basics: How to create a chart

First, fetch your data using the Spreadsheet App. Consider creating a new sheet with many charts to use as a dashboard or inserting charts relevant to the data on each sheet in your workbook. 

In Excel

1. Highlight the data for your trend chart.
2. Go to the Insert tab.
3. Select your preferred chart style or see the recommended charts for this dataset.
4. Select the chart and use the Chart Design and Format tabs to customize. 
5. In Chart Design, use Add Chart Element to add a trendline, legend, or other elements. 
6. Right-click on the chart elements to adjust the scale, format, and titles.

In Google Sheets

1. Highlight the data for your trend chart.
2. Go to the Insert tab.
3. Select Chart to open the Chart Editor.
4. In the Chart Editor, select your preferred chart type.
5. Customize your chart using the customization options in the Chart Editor.
6. Use the Setup tab to adjust data ranges and series.

With your charts now set up, each data fetch from Cube to this workbook will automatically refresh them, ensuring they consistently display the most up-to-date information.

Build trend charts to track revenue trends

Trend charts are essential tools for visualizing data trends over time. They are particularly useful for tracking financial metrics like revenue and cost trends or profit margins and can be created quickly after fetching actuals, budget, and forecast data from Cube.

Example: track revenue trends by market

In this example, we'll create a line chart to track revenue trends over a time period, broken down by market across several quarters. Following these steps to build a trend chart in your spreadsheet will help your teams visualize trends, identify growth patterns, and forecast future revenues. This visual representation aids in detecting anomalies and making strategic decisions based on historical data trends.

Prepare your data

Fetch the revenue data for each market from Cube into your spreadsheet. You may need to actualize a budget or forecast scenario by merging scenarios first. Or simply bring historical actuals and forecasted or budgeted revenue data into the same data table using multiple ranges.

Then, organize your revenue data chronologically and ensure data is clean and consistent for accurate trend analysis. Consider aggregating data into larger periods if you have very granular details (like daily figures).

Create the trend chart

1. Highlight the revenue data.
2. Go to the Insert tab and select Line Chart.
3. In the Chart Editor (Google Sheets) or Chart Tools (Excel), select a line chart style that best represents your data.
4. Adjust or add more data series until there is one line per segment. You may also need to adjust or add data series if you wish to discern between actuals vs. forecast or budget data. 
5. Format the line colors, axis labels, title, and key to support your audience's understanding.

Customize for clarity

Add a trendline to denote patterns. Update axes labels and add labels to data points to call out specific information. 

Use column charts to compare

Variance analysis compares actual financial performance to planned or budgeted figures, highlighting areas of over or underperformance. Column charts can effectively communicate this type of data. 

Example: analyze monthly expense variance

In this example, we'll construct a column chart to compare actual expenses against monthly budgeted figures. This chart highlights areas where spending is over or under the budget, enabling your teams to pinpoint inefficiencies or unexpected costs quickly. It is a critical tool for budgetary control and financial management, facilitating adjustments in spending or strategy as needed.

Prepare your data

Ensure your data has columns for actual and budgeted figures fetched from Cube, and calculate the variance in a separate column. You can look at your variance over time using column sections for months, quarters, or the time period you need. 

You can also use conditional formatting to highlight areas where expenses exceeded or fell short of the budget.

Create the column chart

1. Highlight the actuals and budget data.
2. Go to the Insert tab and select Column Chart.
3. In the Chart Editor (Google Sheets) or Chart Tools (Excel), select a clustered column chart style.
4. Format the columns, axis labels, title, and key as needed. 

Bonus: use a column and line combination chart to show variance trends

Clustered column charts excel at showing the variance for each time period, but when you want to see variance trends over time, you'll want to use a column and line combination chart. Learn more about fetching data to date. 

1. Add a column for the year-to-date (YTD) variance in your dataset. 
2. Select the variance and YTD variance data. 
3. Go to the Insert tab and select Combo, then Column Chart.
4. In the Chart Editor (Google Sheets) or Chart Tools (Excel), select a clustered column line combination chart style.
5. Format the columns, axis labels, tile, and key as needed. 

Customize for clarity

Use contrasting colors for actual and budgeted figures and add data labels to clarify exact figures. Adjust the axis scales if necessary for better representation of variances. 

Add thresholds to charts for added context

Tracking cash on hand and burn rates is vital for understanding a business's liquidity, sustainability, and financial health. Build a chart to show historical and projected models and add thresholds to your charts to communicate goals and minimums efficiently. 

Example: Cash on hand or runway with thresholds

In this example, we’ll prepare our cash on hand, calculate our burn rate, and then create a chart. This combination chart is ideal for showing the progression of the runway over time, and thresholds allow for quick comprehension of where data stands in relation to key metrics. 

Prepare your data

Fetch your current and forecasted or budgeted cash balances for the time periods you want to visualize. This includes all liquid assets that can be readily used for business expenses. Then, calculate your monthly burn rate and runway calculation. Alternatively, if you frequently reference these figures across your packages, they could be added as a formula in Cube and fetched to your spreadsheet.

Add one or more rows or columns for thresholds for each time period. We’ll add these to the chart to highlight key benchmarks or zones. 

Create the runway chart

1. Highlight the runway and cash on hand data.
2. Go to the Insert tab and select a Combo chart.
3. Select the Clustered column with line on secondary axis.
4. You may need to manually adjust the second column axis range and labels in the Chart Editor (Google Sheets) or Chart Tools (Excel).
5. Insert thresholds one at a time:
   1. In Excel, click on the chart and click Select Data then the plus icon. In Google Sheets, click on Add Series in the Chart Editor. 
   2. Select the threshold data from your sheet. 
   3. Format the line to change its size or color. 
   4. Repeat to add other thresholds.
6. Adjust whether the data series are displayed on the primary or secondary axis or add data series if necessary. 
7. Format the colors and sizes of lines and columns to make your chart easier to understand. 

Customize for clarity

Consider including text annotations at significant points, like when major financial decisions (e.g., a funding round) affect the cash balance and runway.

Experiment with different styles and customizations to make these charts even more powerful tools in your FP&A toolkit.