≡ Menu

Charts

Annual Growth rates from Linear Trendlines

Annual Growth rates from Linear Trendlines Thumbnail

In this post I’ll demonstrate how to generate annual growth rates based on historic linear trends. It effectively adds the % annual growth rate to a linear trendline produced on an excel chart. I’ve previously published a post sharing a model I used to produce linear forecasts from historic activity data, so I could generate [...]

{ 2 comments }

Using Formulas to create a Dynamic Chart Title

Using Formulas to create a Dynamic Chart Title Thumbnail

The ability to use formulas in Excel chart titles is very limited and so most people tend to hard code their chart titles. The only formula you can use in the chart title is one that references another cell, for example =$C$1 That does enable you to create a dynamic chart title though - just [...]

{ 1 comment }
Using Ramp-Up Profiles for QIPP Savings and Expenditure Thumbnail

You can use this ramp up trick to apply a phasing profile to planned expenditure but I am illustrating it here to model the monthly delivery of planned savings. In the NHS we tend to refer to savings as either QIPP (Quality, Innovation, Productivity and Prevention) or CIP (Cost Improvement Program) depending on whether you [...]

{ 2 comments }

Forecasting using Excel’s Linear Trendline

Forecasting using Excel’s Linear Trendline Thumbnail

Excel offers the option to insert a series of trendlines on your charts. I have been using the linear trendline feature to represent the historic trends in SUS activity data and I wanted to use the associated formula to generate forecast data based on this trendline. If you think back to your algebra days you [...]

{ 2 comments }

The Double Bridge – An Excel Waterfall Template

The Double Bridge – An Excel Waterfall Template Thumbnail

It seems that each NHS planning round inspires me to produce yet another Excel Bridge template.The powers that be seem to love the visual appeal of a colourful waterfall and I find that pulling the data together for a detailed bridge makes the pain of completing the Source and Apps, TRU64 templates, marginally less painful.This [...]

{ 0 comments }

Excel Waterfall Charts with Ease

Excel Waterfall Charts with Ease Thumbnail

It's Operating Plan time at work again, and that means long term financial models and bridge diagrams to show the causes of movement between years. I've previosuly written about Excel waterfall charts and I had to refer to my own waterfall chart tutorial to help me with this months submission. I've been researching the topic [...]

{ 6 comments }

Excel Histogram Charts from a Pivot Table

Excel Histogram Charts from a Pivot Table Thumbnail

Today I am processing data from a patient level costing system (PLiCS) and trying to assess the cost distribution by the patients diagnosis. Frequency distribution charts can be a bit painful to produce in excel so I have fallen back on my favourite tool – the pivot table which enables me to produce dynamic excel [...]

{ 3 comments }

Waterfall Charts that Cross the X-axis

Waterfall Charts that Cross the X-axis Thumbnail

Waterfall charts have almost reached cult status in the office recently. They are popping up all over. We are well into the new years planning process and a waterfall chart is the perfect way to demonstrate how a starting position either increases or decreases through a series of changes. So in our case, when planning [...]

{ 1 comment }