All Resources

The Quagmire Challenge: Timing Flexibility (February 2018)

The Question

Flexibility is a key tenet of good financial modeling. An assumption that often changes is timing (e.g. expenditures, product releases, investments, etc). Therefore, a well-built model has flexible assumptions that allow for easy changes to the timing of key events.

In this edition of the Quagmire, Bluth Development Corp. is looking to commence construction of its frozen key lime restaurant concept. Your task is to create model flexibility by automating the timing of the construction costs.

If you click here you can download the spreadsheet with the sample data.    

Instructions: Complete the yellow highlighted cells throughout the model. All of the information required to fully complete this is already contained within the spreadsheet (i.e. you do not have to perform any further work on the data).

The following are the details for the new real estate development:

  • It is unknown which year in the date range construction will begin;
  • Construction will take three years; and
  • There are three potential expenditure patterns for the three years of construction


Task 1 – Create a Scenario Manager. Fill in the highlighted cells in Row 10 with a consistent formula that shows which cost structure is currently running through the model, based off of a scenario switch that you will need to build in cell F4. This should allow for the automation of the three potential cost allocation cases.

Task 2 – Dynamic Construction Status. Fill in the highlighted cells in Row 25 with a consistent formula. These cells should contain information on whether or not construction is live during each year of the model.

Task 3 – Dynamic Cost Allocation. Fill in the highlighted cells in Row 27 with a consistent formula.  This row should bring in the live cost allocation percentages from the scenario manager that you created in Task 1.   

Task 4 – Dynamic Costs.  Fill in the highlighted cells in Row 29 with a consistent formula.  This row should accurately display the total cost in dollars for each year.

Goal.  Once completed, you should be able to change the first year of construction in cell D12 to each year in the drop-down, as well as choose any of the three potential cost allocation cases from your scenario switch. The entire model should also dynamically update – ultimately flowing down to the correct total costs in each year.

Have fun and good luck. The top solutions will receive a Marquee prize pack and winners will be announced in the April edition of The BenchMarq. Submit your answer to info@marqueegroup.ca, subject: Quagmire #6.

The Solution

As with all of our Quagmire challenges, there are multiple ways to solve them. The February 2018 Quagmire was no different, as each of the submissions that we received were unique.  In the solution below we highlight just one approach, however each of the winners had the following elements in their solutions:

  • Flexible / Dynamic: Worked in all timing situations and construction cost scenarios; formulas had no hardcodes
  • Intuitive / Simple: Minimal work / formula length; no changes to the template
  • User Friendly: Transparent in the way it was built; transferrable from a user-to-user perspective

Solution Steps:

Task 1 – Create a Scenario Manager. To fill in highlighted Cell F4, a Combo Box was inserted using the following keyboard shortcut:

  • Alt > L (Developer) > I (Insert) > C (Combo Box (Form Control))

A rectangle was then drawn around Cell F4 to create the structure of the Combo Box.  We then right clicked the newly-created Combo Box and selected “Format Control”.  In the Input range box we entered $F$12:$F$14, and in the Cell link box we entered $F$4.

We then left-clicked anywhere in Excel to deselect the combo box, then left-clicked the Combo Box and chose Case #1.  At this point, the Combo Box is created.

The next task is to fill in the highlighted cells in Row 10.  For this we used the CHOOSE function by entering the following formula in Cell H10:

  • =CHOOSE($F$4,H12,H13,H14)

The formula above was then pasted across the remaining highlighted cells in Row 10.

At this point, the Scenario Manager is created.  The user can now change the case running in the Combo Box and Row 10 should update automatically with the Live Case that will eventually run throughout the spreadsheet.

Task 2 – Dynamic Construction Status. To fill in the highlighted cells in Row 25, we combined the IF and AND functions by entering the following formula in Cell D25:

  • =IF(AND(D21>=$D$12,D21<=$D$13),1,0)

The formula above was then pasted across the remaining highlighted cells in Row 25.

This results in a string of 1’s and 0’s across Row 25.  Since 1 represents a construction period and 0 represents a non-construction period, we custom formatted the highlighted cells in Row 25 so that 1 = Yes and 0 = No.  We did this by:

  • Selecting the highlighted cells in Row 25
  • Pressing Ctrl + 1 to open the Format Cells dialogue box
  • Selecting the Custom category under the Number tab
  • Entering [=1]”Yes”;[=0]”No” in the Type input box

At this point, the Dynamic Construction Status is created.  Changing the year in Cell D12 will now automatically update the Yes/No Construction flags across Row 25.  

Task 3 – Dynamic Cost Allocation. To fill in the highlighted cells in Row 27, we combined the OFFSET and SUM functions by entering the following formula in Cell D27:

  • =OFFSET($G$10,0,SUM($D$25:D25))*D25

The formula above was then pasted across the remaining highlighted cells in Row 27.

This formula incorporates the cells in Row 10 and Row 25 to return the appropriate period cost allocations, including zeros in the non-construction periods.

At this point, the Dynamic Cost Allocation is created.  Changing the year in Cell D12 will now automatically update the percentage allocation periods across Row 27, and changing the case within the Scenario Manager will automatically update the percentage allocations within the time periods across Row 27.

Task 4 – Dynamic Costs.  To fill in the highlighted cells in Row 29, we entered the following formula in Cell D27:

  • =$D$10*D27

 

The formula above was then pasted across the remaining highlighted cells in Row 29.

This formula incorporates Cell D10 and the cells in Row 27 to return the appropriate period cost.

At this point, the Dynamic Costs have been fully modelled out.  Changing the year in Cell D12 and changing the case within the Scenario Manager will automatically update the cost allocations within the time periods.