GC SumProduct Pty Ltd Waterfall Chart Example Go to Table of Contents Primary Developer: Liam Bastick General Cover Notes: How to construct a simple waterfall chart. "Any queries, please e-mail:" liam.bastick@sumproduct.com Website: www.sumproduct.com ##### Sheet/List 2 ##### C Table of Contents Waterfall Chart Example Go to Cover Sheet é Section & Sheet Titles Page 1. Waterfall Chart Example 3 a. Waterfall Chart Example - Walkthrough 4 Total Pages: 4 ##### Sheet/List 3 ##### SC Waterfall Chart Example Section 1. Waterfall Chart Example Go to Table of Contents ç è Section Cover Notes: The following worksheet provides a simple walkthrough example. ##### Sheet/List 4 ##### GA General Assumptions Waterfall Chart Example Go to Table of Contents é ç è General Assumptions Set Primary Model Period Type 1 Financial Year End Month 12 Model Start Date 1/1/06 Forecast Periods 20 First Period End Month December First Period End Date 31/12/06 First Period Financial Title Year Model Denomination 2 Notes: 1. "The ""First Period End Date"" only applies to Forecast Output and Forecast Assumptions Sheets based on the ""Month End"" Forecast Sheet Type." 2. "The ""Model Denomination"" assumption will not necessarily automatically change the denomination of the outputs of this model." ##### Sheet/List 5 ##### BA Waterfall Chart Example - Walkthrough Waterfall Chart Example Go to Table of Contents é ç è Waterfall Chart Example - Walkthrough Assumptions Amt. Cum. Opening Balance " $1,000.00 " " $1,000.00 " Mvmt 1 $53.17 " $1,053.17 " Mvmt 2 $72.12 " $1,125.29 " Mvmt 3 $85.16 " $1,210.45 " Mvmt 4 ($775.42) $435.03 Mvmt 5 ($853.16) ($418.13) Mvmt 6 $28.33 ($389.80) Mvmt 7 $977.42 $587.62 Mvmt 8 $46.14 $633.76 Mvmt 9 $81.12 $714.88 Mvmt 10 $12.44 $727.32 Closing Balance $727.32 Chart Calculations Table Op. Bal. Cl. Bal. Pos Inv Pos Dec Vis Pos Inc Vis Neg Inv Neg Dec Vis Neg Inc Vis Opening Balance " $1,000.00 " Mvmt 1 " $1,000.00 " - $53.17 - - - Mvmt 2 " $1,053.17 " - $72.12 - - - Mvmt 3 " $1,125.29 " - $85.16 - - - Mvmt 4 $435.03 $775.42 - - - - Mvmt 5 - $435.03 - - ($418.13) - Mvmt 6 - - - ($389.80) - ($28.33) Mvmt 7 - - $587.62 - - ($389.80) Mvmt 8 $587.62 - $46.14 - - - Mvmt 9 $633.76 - $81.12 - - - Mvmt 10 $714.88 - $12.44 - - - Closing Balance $727.32 ##### Sheet/List 6 ##### SC [Insert Output Section Title] Section 3. Waterfall Chart Example Go to Table of Contents ç è Section Cover Notes: [Insert section cover note 1] [Insert section cover note 2] [Insert section cover note 3] ##### Sheet/List 7 ##### FO [Insert Forecast Output Sheet Title] Waterfall Chart Example Go to Table of Contents é ç è Year Ending 31 December 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 Period End Year 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 Financial Year 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 Days in Period End Year 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 Days in Financial Year 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 Financial Year Period Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Period Start Date (From Start of Day...) 1/1/06 1/1/07 1/1/08 1/1/09 1/1/10 1/1/11 1/1/12 1/1/13 1/1/14 1/1/15 1/1/16 1/1/17 1/1/18 1/1/19 1/1/20 1/1/21 1/1/22 1/1/23 1/1/24 1/1/25 Period End Date (Until End of Day...) 31/12/06 31/12/07 31/12/08 31/12/09 31/12/10 31/12/11 31/12/12 31/12/13 31/12/14 31/12/15 31/12/16 31/12/17 31/12/18 31/12/19 31/12/20 31/12/21 31/12/22 31/12/23 31/12/24 31/12/25 Months in Financial Year 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 Days in Period 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 Days in Financial Year 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 365 365 366 365 Counter 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Spare/Custom - - - - - - - - - - - - - - - - - - - - ##### Sheet/List 8 ##### SC Model Lookup Tables Section 4. Waterfall Chart Example Go to Table of Contents ç è Section Cover Notes: [Insert section cover note 1] [Insert section cover note 2] [Insert section cover note 3] ##### Sheet/List 9 ##### GL General Lookup Tables Waterfall Chart Example Go to Table of Contents é ç Months Lookup Names: Model Period Type Lookup Names: Model Denomination Lookup Names: Month LU_Mths Model Period Type LU_Pers Denomination LU_Denom January Jan Annual Ann $Billions Billions February Feb Semi-Annual Semi_Ann $Millions Millions March Mar Quarterly Qtrly $'000 Thousands April Apr Monthly Mthly $ Currency May May June Jun July Jul Period Names Lookup Names: Time Constants Lookup Names: August Aug September Sep Period Name LU_Per_Names Time Constant October Oct Year Yr_Name 60 Secs_In_Min November Nov Half Year Half_Yr_Name 60 Mins_In_Hr December Dec Quarter Qtr_Name 24 Hrs_In_Day Month Mth_Name 7 Days_In_Wk 52 Wks_In_Yr Model Quarter Lookup Names: 3 Mths_In_Qtr Periods in Year Lookup Names: 6 Mths_In_Half_Yr Quarter LU_Qtrs Q1 Qtr_1 Periods in Year LU_Pers_In_Yr Q2 Qtr_2 1 Yrs_In_Yr Conversion Factor Lookup Names: Q3 Qtr_3 2 Halves_In_Yr Q4 Qtr_4 4 Qtrs_In_Yr Conversion Factor 12 Mths_In_Yr 10 Ten 100 Hundred Model Half Year Lookup Names: " 1,000 " Thousand Yes / No Input Lookup Names: " 1,000,000 " Million Half Year LU_Halves " 1,000,000,000 " Billion H1 Half_1 Yes / No Input LU_Yes_No H2 Half_2 Yes Yes No No