Pages Menu
Categories Menu

Posted by on Oct 11, 2016

Analysis Paralysis: Find Time to Get the Croissant with Your Cup of Coffee

Analysis Paralysis: Find Time to Get the Croissant with Your Cup of Coffee

Diagrams projecting from tabletYou’ve just submitted the report that your supervisor wanted on projected HR costs for next quarter. As you lean back to enjoy your second cup of coffee and begin to tackle your next project, an email asking you to run the analysis based on a different set of assumptions pops up in your inbox. So much for getting a head-start on that other project.

We’ve all been in this situation at least once. I’ll be the first to admit that in my early years as an analyst, I found myself copying and pasting – creating new worksheets for each request. Soon my workbooks looked like an eight-headed beast that couldn’t be slain, like the mythical Greek hydra.

Changing one or two input items yielding different results that management can act upon is referred to as “modeling”. Fortunately, Microsoft Excel’s “Scenario Manager” provides the functionality that allows an analyst to generate these different scenarios with a minimal amount of tedious copying, pasting, and formatting. This function allows the analyst to establish a menu of scenarios that will be used to produce the target value (HR costs in my example) and create a production-ready summary table. Here are some tips for using to help free up the time to enjoy that second cup of coffee.

  • Think through the computations and how you structure that within the worksheet. For example, if I am modeling HR costs based on number of FTEs (employees) and benefit costs (fixed and variable), I put those input items in separate cells.
  • Many analysts will limit themselves to one input item, but Scenario Manager allows for multiple items that can be varied.
  • Use named ranges for the input items that will be changing. Scenario Manager will use these names in the output. If you don’t name the ranges, only the addresses will be used – e.g. “$B$1” – which will only lead to questions and confusion.
  • My personal preference is to use three scenarios. One will represent the status quo and the other two will be aggressive and conservative. Alternatively, you could use the terms Low, Medium, High. Exhibiting more than three will likely generate more questions…which means more work for you!
  • Even though a variable may not need to be changed, I tend to include it in my output report, but the value will remain constant. I do this for a couple of reasons. First, it reminds me of the variables I used to construct the model. Second, it demonstrates to the audience that you are proactive and considering other items that could impact your analysis – even though you are keeping the value constant for the current analysis.

Let me share a bit more detail, using the example of HR costs…

Below is the section of the worksheet where I put my computations. Notice that I have used named ranges and they are used in the formula in cell B6. Based upon this structure, my scenarios can vary any of the four input items: FTE, Fixed Benefit $, Salary, and Variable Benefit.

Data Analytics

I am going to run scenarios that change the number of FTEs and Fixed Benefit and keep the other two constant. These scenarios will be (Low) 25 FTE, $250 Fixed Benefit; (Medium) 50 FTE, $100 Fixed Benefit; and (High) 100 FTE, $75 Fixed Benefit.

Scenario Manager can be found by clicking the Data tab and selecting the “What-if Analysis” menu item. I won’t go into detail on the mechanics, but you can view my spreadsheet to aid you in understanding this tool. The report generated is below.

excel-image-2

At a glance, you can see the current values along with the modeled values and associated HR costs. No need to copy and paste three or more times and fight with formatting the table…Excel automatically generates the formatting – so the work is done for you! If further changes are requested, the modifications required are minimal and the time saved is considerable.

Maybe you can also enjoy a croissant with that second cup of coffee…


Guest Blogger: 

Dennis Kunimura is an independent consultant with over 30 years of experience conducting quantitative and risk analysis as an actuary and statistician.  He specializes in developing predictive models that help companies maximize customer engagement and revenue.  He is accredited with three international statistical organizations and is recognized as a Chartered Scientist by the Royal Statistical Society.

Post a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>