I had a delegate this week who had a bit of an unusual problem. I ran an Excel course and she attended knowingly but she hardly knew Excel at all. I felt the need to help her to take away something concrete from the course so I talked to her on her own. In the end, what I did for her was to show her how to take the template she uses at work and add a few fields that would generate graphs (micro dashboard) and use the CONCATENATE function to generate natural language conclusions.
What follows is by no means finished since I have not yet had time to refine anything. But this is the demonstration I gave. The colourful charts you see at the top of the work sheet are my famous conditional formatting charts that show different colours automatically: everyone likes to see them!
Firstly the graphs
The file concerns capital investment appraisal: Payback, ARR, NPV, IRR and Profitability Index. I don’t show it here but this worksheet is the summary sheet in which the results of three projects are discussed and illustrated.
So, the first chart shows the Payback period in years for each project. The second graph shows the ARR results and the third graphs shows the NPV outcomes.
Really nothing unusual there apart from the formatting: I have tried to be minimalist!
The words in rows 11 to 15
Here are the formulas for the Payback and ARR conclusions:
=”The quickest PB is “&MIN(B25:B27)&” Years which is for “&VLOOKUP(MIN(B25:B27),B25:C27,2,0)
=”The highest ARR is “&ROUND(MAX(D25:D27)*100,2)&”% which is for “&VLOOKUP(MAX(D25:D27),D25:E27,2,0)
Note the use of these functions:
- MIN
- VLOOKUP
- CONCATENATE (I always use the & version)
- ROUND
Now, take what you see here and develop it: it really isn’t difficult. The idea for my delegate was for her to take this example and turn her own template into this template in which she simply copies and pastes the data she gets and this template generates her report for management from it.
It really is not difficult.
Combo Box Controlled Chart
We have discussed these before but look at the extended screenshot below:
Here you can see the blue and white 2D Column Graph including a ComboBox at the top right hand corner. What is happening here is that in addition to the colourful graphs, I am suggesting that you might want to include just one chart but it is a chart that you can update live. Click on the drop down box, combo box, choose the Payback etc option and you get the results instantly. Like this:
each graph shows the results for al projects for each measure: NPV, ARR and PB here. In addition I have programmed them to show the data table rather than the horizontal axis because the measures are shown in different units and that makes the axis a bit tricky to show. This is a template so I have tried to keep it really simple.
This one chart, let me confirm, can replace all three charts at the top of the worksheet if you are doing, for example, a live presentation of results.
Because this file is currently unfinished I do not want to publish it. Please feel free to ask me for guidance, however, at any time!
Duncan Williamson
Excel with Excel Master 