Original Post Date: Wednesday, October 17, 2012

PRICE was recently tasked by a client to provide an Analysis of Alternatives (AOA). As we assembled a team to complete this, I was tasked with modeling all the alternatives inside TruePlanning.  After the initial data call, we realized that this project would be cumbersome due to the large amount of data. While developing the project plan, I had to think of a crafty way to get data in and out of TruePlanning efficiently.

It was interesting to note how much capability I could utilize from the TruePlanning Companion Applications to effectively support the production of over 90+ models for our baseline point estimates.  In this part 2 of my blog, I will share my use of the “Excel Solution – Complete” and the “Sensitivity Analyzer”.

To start, I built the initial product break-down structure inside TruePlanning.  I opened the Excel Solution and downloaded this project into Microsoft Excel.  One caveat - the runtime is a little slow as the Excel Solution is using VBA to interface through Cost Object Modeling (COM) in the TruePlanning framework to access the database to load the project and fill in needed inputs. However, once this process was complete, I could make mass data changes instantly like changing the price of the hardware for the same technology across multiple cost objects.

Then with a click of a button on the add-in tab the data that I updated in excel was imported back to TP for a new calculation and reprocessed back into my excel file for further analysis. Being that I had large project files which contained over 800+ cost objects this took a little while to process through VBA (2hours plus). However through a little manipulation I still was able to reduce my time of completing my project files by a quarter of the time.

Once all 9 of the point estimates were set, I needed to define which inputs were the key drivers into our model. This is where the “Sensitivity Analyzer” saved my day! Within 15 -20 minutes I could define, inside each model, which inputs where the main drivers and their relationship to the total cost of the component or overall project.  Furthermore I could drill down and pick an input, like weight of the structure, and show how linear the relationship is between my weights as it increased to my cost. The Analyzer produced graphs and charts which I used to easily import into a Microsoft Power Point deck for presenting data to the customer.

In my years of estimating, I have seen some miraculous things done with getting an AOA or estimate out for review; but usually it takes twice as long, and many times you are not sure what the cost drivers are.  Using the Excel Solution I was able to meet my deadlines with no sweat and some sleep, plus the Sensitivity Analyzer provided data insight for better decisions on how to precede with the estimate.

Now the question begs to be answered: Can it be done even faster when you receive that fateful call stating: “Redo the whole estimate, we have different data!!!”… Find out in ‘Excel Friend or Foe Part 3’!