{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Cover","Navigator","Style Guide","Model Parameters","Pre-Tax Cost of Equity Example","Timing","Lookup Data","Error Checks"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":62,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover","index":0,"n_rows":22,"n_cols":7,"rows_kept":22,"cut":false,"hidden":false,"merged":[[16,2,16,9],[17,2,17,9],[20,6,20,8],[21,6,21,8]]}
[]
[]
["Navigator"]
[]
[null,null,{"$f":"Client_Name","v":"SumProduct Pty Limited"}]
[null,null,{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Primary Developer:  Liam Bastick"]
[]
[null,null,"General Cover Notes:"]
[null,null,"Example to show how the Pre-Tax Cost of Equity may be solved."]
[]
[]
[]
[null,null,"Any queries, please e-mail:",null,null,null,"liam.bastick@sumproduct.com"]
[null,null,"Website:",null,null,null,"www.sumproduct.com"]

{"sheet":"Navigator","index":1,"n_rows":15,"n_cols":7,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Navigator"]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,{"$f":"Overall_Error_Check","v":0}]
[]
[]
[null,1,"Table of Contents"]
[]
[null,null,null,null,null,"Cover"]
[null,null,null,null,null,"Style Guide"]
[null,null,null,null,null,"Model Parameters"]
[null,null,null,null,null,"Pre-Tax Cost of Equity Example"]
[null,null,null,null,null,"Timing"]
[null,null,null,null,null,"Lookup Data"]
[null,null,null,null,null,"Error Checks"]

{"sheet":"Style Guide","index":2,"n_rows":74,"n_cols":11,"rows_kept":74,"cut":false,"hidden":false,"merged":[[80,2,80,6],[74,2,74,6],[75,2,75,6],[76,2,76,6],[77,2,77,6],[78,2,78,6],[79,2,79,6],[73,2,73,6],[62,2,62,6],[63,2,63,6],[64,2,64,6],[65,2,65,6],[66,2,66,6],[67,2,67,6],[68,2,68,6],[69,2,69,6],[70,2,70,6],[71,2,71,6],[72,2,72,6],[61,2,61,6],[47,2,47,6],[48,2,48,6],[49,2,49,6],[50,2,50,6],[51,2,51,6],[52,2,52,6],[53,2,53,6],[54,2,54,6],[57,2,57,6],[59,2,59,6],[60,2,60,6],[46,2,46,6],[35,2,35,6],[36,2,36,6],[37,2,37,6],[38,2,38,6],[39,2,39,6],[40,2,40,6],[41,2,41,6],[42,2,42,6],[43,2,43,6],[44,2,44,6],[45,2,45,6],[34,2,34,6],[17,2,17,6],[18,2,18,6],[19,2,19,6],[24,2,24,6],[25,2,25,6],[26,2,26,6],[27,2,27,6],[28,2,28,6],[29,2,29,6],[30,2,30,6],[31,2,31,6],[16,2,16,6],[2,0,2,4],[7,2,7,6],[8,2,8,6],[9,2,9,6],[10,2,10,6],[11,2,11,6],[12,2,12,6],[13,2,13,6],[14,2,14,6],[15,2,15,6]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Style Guide"}]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Formatting of Headers / Dividers"]
[]
[null,null,"Description",null,null,null,null,null,"Display",null,"Style Name"]
[]
[null,null,"Sheet Title",null,null,null,null,null,{"$f":"C10","v":"Sheet Title"},null,"Sheet Title"]
[null,null,"Model Name",null,null,null,null,null,{"$f":"C11","v":"Model Name"},null,"Model Name"]
[]
[null,null,"Header 1",null,null,null,null,null,{"$f":"C13","v":"Header 1"},null,"Header 1"]
[null,null,"Header 2",null,null,null,null,null,{"$f":"C14","v":"Header 2"},null,"Header 2"]
[null,null,"Header 3",null,null,null,null,null,{"$f":"C15","v":"Header 3"},null,"Header 3"]
[null,null,"Header 4",null,null,null,null,null,{"$f":"C16","v":"Header 4"},null,"Header 4"]
[]
[null,null,"Notes",null,null,null,null,null,{"$f":"C18","v":"Notes"},null,"Notes"]
[]
[null,null,"Table Heading",null,null,null,null,null,{"$f":"C20","v":"Table Heading"},null,"Table Heading"]
[]
[]
[null,{"$f":"MAX($B$5:$B22)+1","v":2},"Individual Cell Styles"]
[]
[null,null,"Description",null,null,null,null,null,"Display",null,"Style Name"]
[]
[null,null,"Assumption",null,null,null,null,null,"Assumption",null,{"$f":"C27","v":"Assumption"}]
[]
[null,null,"Constraint",null,null,null,null,null,{"$f":"C29","v":"Constraint"},null,{"$f":"C29","v":"Constraint"}]
[]
[null,null,"Empty",null,null,null,null,null,null,null,{"$f":"C31","v":"Empty"}]
[]
[null,null,"Error Check",null,null,null,null,null,0,null,{"$f":"C33","v":"Error Check"}]
[]
[null,null,"Hyperlink",null,null,null,null,null,"Hyperlink",null,{"$f":"C35","v":"Hyperlink"}]
[]
[null,null,"Internal Reference",null,null,null,null,null,{"$f":"'Error Checks'!E12","v":"NPVs reconcile"},null,{"$f":"C37","v":"Internal Reference"}]
[]
[null,null,"Line Calculation",null,null,null,null,null,77,null,"Line Calc"]
[]
[null,null,"Line Total",null,null,null,null,null,{"$f":"I39","v":77},null,{"$f":"C41","v":"Line Total"}]
[]
[null,null,"Parameter",null,null,null,null,null,365,null,{"$f":"C43","v":"Parameter"}]
[]
[null,null,"Range Name Description",null,null,null,null,null,"Not_Named",null,{"$f":"C45","v":"Range Name Description"}]
[]
[null,null,"Row Reference",null,null,null,null,null,{"$f":"ROW(C47)","v":47},null,"Row Ref"]
[]
[null,null,"Row Summary",null,null,null,null,null,{"$f":"I41","v":77},null,{"$f":"C49","v":"Row Summary"}]
[]
[null,null,"Units",null,null,null,null,null,"A$",null,{"$f":"C51","v":"Units"}]
[]
[null,null,"WIP",null,null,null,null,null,null,null,{"$f":"C53","v":"WIP"}]
[]
[]
[null,{"$f":"MAX($B$5:$B55)+1","v":3},"Numerical Styles"]
[]
[null,null,"Description",null,null,null,null,null,"Display",null,"Style Name"]
[]
[null,null,"Comma",null,null,null,null,null,123456.789,null,{"$f":"C60","v":"Comma"}]
[]
[null,null,"Comma [0]",null,null,null,null,null,-123456.789,null,"Comma [0]"]
[]
[null,null,"Currency",null,null,null,null,null,123456.789,null,"Currency"]
[]
[null,null,"Currency [0]",null,null,null,null,null,123456.789,null,"Currency [0]"]
[]
[null,null,"Date",null,null,null,null,null,{"$f":"TODAY()","v":"2020-05-27"},null,{"$f":"C68","v":"Date"}]
[]
[null,null,"Date Heading",null,null,null,null,null,{"$f":"TODAY()","v":"2020-05-27"},null,{"$f":"C70","v":"Date Heading"}]
[]
[null,null,"Numbers 0",null,null,null,null,null,-123456.789,null,{"$f":"C72","v":"Numbers 0"}]
[]
[null,null,"Percent",null,null,null,null,null,0.5,null,{"$f":"C74","v":"Percent"}]

{"sheet":"Model Parameters","index":3,"n_rows":31,"n_cols":9,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,9,0,10],[2,0,2,4],[10,6,10,13],[11,6,11,13]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Model Parameters"}]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"General"]
[]
[null,null,"Key Inputs"]
[]
[null,null,null,null,"General"]
[null,null,null,null,"Model Name",null,{"$f":"IF(ISERROR(OR(FIND(\"[\",CELL(\"filename\",A1)),FIND(\"]\",CELL(\"filename\",A1)))),\"\",MID(CELL(\"filename\",A1),FIND(\"[\",CELL(\"filename\",A1))+1,FIND(\"]\",CELL(\"filename\",A1))-FIND(\"[\",CELL(\"filename\",A1))-1))","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
[null,null,null,null,"Client Name",null,"SumProduct Pty Limited"]
[]
[]
[null,{"$f":"MAX($B$5:$B14)+1","v":2},"General Range Names"]
[]
[null,null,"Technical Assumptions"]
[]
[null,null,null,null,"Days in Year",null,365]
[null,null,null,null,"Months in Month",null,1]
[null,null,null,null,"Months in Quarter",null,3]
[null,null,null,null,"Months in Half Yr",null,6]
[null,null,null,null,"Months in Year",null,12]
[null,null,null,null,"Quarters in Year",null,4]
[]
[null,null,null,null,"Rounding Accuracy",null,0]
[]
[null,null,null,null,"Very Large Number",null,9.9999999999999997E+98]
[null,null,null,null,"Very Small Number",null,1E-8]
[]
[null,null,null,null,"Thousand",null,1000]

{"sheet":"Pre-Tax Cost of Equity Example","index":4,"n_rows":114,"n_cols":29,"rows_kept":114,"cut":false,"hidden":false,"merged":[[0,8,0,9],[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Pre-Tax Cost of Equity Example"}]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[]
[null,null,null,null,null,null,null,null,null,{"$f":"Timing!J5","v":"2020-12-31"},{"$f":"Timing!K5","v":"2021-12-31"},{"$f":"Timing!L5","v":"2022-12-31"},{"$f":"Timing!M5","v":"2023-12-31"},{"$f":"Timing!N5","v":"2024-12-31"},{"$f":"Timing!O5","v":"2025-12-31"},{"$f":"Timing!P5","v":"2026-12-31"},{"$f":"Timing!Q5","v":"2027-12-31"},{"$f":"Timing!R5","v":"2028-12-31"},{"$f":"Timing!S5","v":"2029-12-31"},{"$f":"Timing!T5","v":"2030-12-31"},{"$f":"Timing!U5","v":"2031-12-31"},{"$f":"Timing!V5","v":"2032-12-31"},{"$f":"Timing!W5","v":"2033-12-31"},{"$f":"Timing!X5","v":"2034-12-31"},{"$f":"Timing!Y5","v":"2035-12-31"},{"$f":"Timing!Z5","v":"2036-12-31"},{"$f":"Timing!AA5","v":"2037-12-31"},{"$f":"Timing!AB5","v":"2038-12-31"},{"$f":"Timing!AC5","v":"2039-12-31"}]
[null,null,{"$f":"Timing!C6","v":"Start Date"},null,null,null,null,null,null,{"$f":"Timing!J6","v":"2020-01-01"},{"$f":"Timing!K6","v":"2021-01-01"},{"$f":"Timing!L6","v":"2022-01-01"},{"$f":"Timing!M6","v":"2023-01-01"},{"$f":"Timing!N6","v":"2024-01-01"},{"$f":"Timing!O6","v":"2025-01-01"},{"$f":"Timing!P6","v":"2026-01-01"},{"$f":"Timing!Q6","v":"2027-01-01"},{"$f":"Timing!R6","v":"2028-01-01"},{"$f":"Timing!S6","v":"2029-01-01"},{"$f":"Timing!T6","v":"2030-01-01"},{"$f":"Timing!U6","v":"2031-01-01"},{"$f":"Timing!V6","v":"2032-01-01"},{"$f":"Timing!W6","v":"2033-01-01"},{"$f":"Timing!X6","v":"2034-01-01"},{"$f":"Timing!Y6","v":"2035-01-01"},{"$f":"Timing!Z6","v":"2036-01-01"},{"$f":"Timing!AA6","v":"2037-01-01"},{"$f":"Timing!AB6","v":"2038-01-01"},{"$f":"Timing!AC6","v":"2039-01-01"}]
[null,null,{"$f":"Timing!C7","v":"End Date"},null,null,null,null,null,null,{"$f":"Timing!J7","v":"2020-12-31"},{"$f":"Timing!K7","v":"2021-12-31"},{"$f":"Timing!L7","v":"2022-12-31"},{"$f":"Timing!M7","v":"2023-12-31"},{"$f":"Timing!N7","v":"2024-12-31"},{"$f":"Timing!O7","v":"2025-12-31"},{"$f":"Timing!P7","v":"2026-12-31"},{"$f":"Timing!Q7","v":"2027-12-31"},{"$f":"Timing!R7","v":"2028-12-31"},{"$f":"Timing!S7","v":"2029-12-31"},{"$f":"Timing!T7","v":"2030-12-31"},{"$f":"Timing!U7","v":"2031-12-31"},{"$f":"Timing!V7","v":"2032-12-31"},{"$f":"Timing!W7","v":"2033-12-31"},{"$f":"Timing!X7","v":"2034-12-31"},{"$f":"Timing!Y7","v":"2035-12-31"},{"$f":"Timing!Z7","v":"2036-12-31"},{"$f":"Timing!AA7","v":"2037-12-31"},{"$f":"Timing!AB7","v":"2038-12-31"},{"$f":"Timing!AC7","v":"2039-12-31"}]
[null,null,{"$f":"Timing!C8","v":"Number of Days"},null,null,null,null,null,null,{"$f":"Timing!J8","v":366},{"$f":"Timing!K8","v":365},{"$f":"Timing!L8","v":365},{"$f":"Timing!M8","v":365},{"$f":"Timing!N8","v":366},{"$f":"Timing!O8","v":365},{"$f":"Timing!P8","v":365},{"$f":"Timing!Q8","v":365},{"$f":"Timing!R8","v":366},{"$f":"Timing!S8","v":365},{"$f":"Timing!T8","v":365},{"$f":"Timing!U8","v":365},{"$f":"Timing!V8","v":366},{"$f":"Timing!W8","v":365},{"$f":"Timing!X8","v":365},{"$f":"Timing!Y8","v":365},{"$f":"Timing!Z8","v":366},{"$f":"Timing!AA8","v":365},{"$f":"Timing!AB8","v":365},{"$f":"Timing!AC8","v":365}]
[null,null,{"$f":"Timing!C9","v":"Counter"},null,null,null,null,null,null,{"$f":"Timing!J9","v":1},{"$f":"Timing!K9","v":2},{"$f":"Timing!L9","v":3},{"$f":"Timing!M9","v":4},{"$f":"Timing!N9","v":5},{"$f":"Timing!O9","v":6},{"$f":"Timing!P9","v":7},{"$f":"Timing!Q9","v":8},{"$f":"Timing!R9","v":9},{"$f":"Timing!S9","v":10},{"$f":"Timing!T9","v":11},{"$f":"Timing!U9","v":12},{"$f":"Timing!V9","v":13},{"$f":"Timing!W9","v":14},{"$f":"Timing!X9","v":15},{"$f":"Timing!Y9","v":16},{"$f":"Timing!Z9","v":17},{"$f":"Timing!AA9","v":18},{"$f":"Timing!AB9","v":19},{"$f":"Timing!AC9","v":20}]
[]
[null,{"$f":"MAX($B$10:$B10)+1","v":1},"Key Assumptions"]
[]
[null,null,"Key Inputs"]
[]
[null,null,null,"Financing Assumptions"]
[]
[null,null,null,null,"Pre-Tax Cost of Debt","%",0.08,null,"PreTax_Cost_of_Debt"]
[null,null,null,null,"Post-Tax Cost of Equity","%",0.12,null,"PostTax_Cost_of_Equity"]
[null,null,null,null,"Tax Rate","%",0.3,null,"Tax_Rate"]
[null,null,null,null,"Proportion of Debt","%",0.5,null,"Proportion_of_Debt"]
[]
[null,null,null,null,"Post Tax WACC","%",{"$f":"(PreTax_Cost_of_Debt*(1-Tax_Rate)*Proportion_of_Debt)+(PostTax_Cost_of_Equity*(1-Proportion_of_Debt))","v":8.7999999999999995E-2},null,"Post_Tax_WACC"]
[]
[null,null,null,null,"Terminal Value","[1,0]","Yes",null,"Terminal_Value_Switch"]
[]
[null,null,null,null,{"$f":"IF(Terminal_Value_Switch=Yes,\"Growth Rate in Perpetuity\",\"\")","v":"Growth Rate in Perpetuity"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},0.02,null,{"$f":"IF(Terminal_Value_Switch=Yes,\"Growth_Rate_in_Perpetuity\",\"\")","v":"Growth_Rate_in_Perpetuity"}]
[]
[null,null,null,null,{"$f":"IF(Terminal_Value_Switch=Yes,\"Tolerance in NPV\",\"\")","v":"Tolerance in NPV"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},0.9,null,{"$f":"IF(Terminal_Value_Switch=Yes,\"TV Tolerance\",\"\")","v":"TV Tolerance"}]
[]
[]
[null,null,null,"Timing Assumptions"]
[]
[null,null,null,null,"Number of Periods","#",8,null,"Number_of_Periods"]
[null,null,null,null,"Tax Delay","# Periods",1,null,"Tax_Delay"]
[null,null,null,null,"Timing of Cash Flows","Text","Middle",null,"Timing_of_CashFlows"]
[]
[null,null,null,null,"Post-Tax Discount Rates"]
[null,null,null,null,{"$f":"Start","v":"Start"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:I$8)/Days_in_Year),\"\")","v":1},{"$f":"IF(K$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:J$8)/Days_in_Year),\"\")","v":0.91890528957302442},{"$f":"IF(L$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:K$8)/Days_in_Year),\"\")","v":0.84458206762226495},{"$f":"IF(M$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:L$8)/Days_in_Year),\"\")","v":0.77627028274105225},{"$f":"IF(N$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:M$8)/Days_in_Year),\"\")","v":0.71348371575464364},{"$f":"IF(O$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:N$8)/Days_in_Year),\"\")","v":0.65562396043115823},{"$f":"IF(P$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:O$8)/Days_in_Year),\"\")","v":0.6025955518668733},{"$f":"IF(Q$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:P$8)/Days_in_Year),\"\")","v":0.55385620575999384},{"$f":"IF(R$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:Q$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(S$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:R$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(T$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:S$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(U$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:T$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(V$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:U$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(W$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:V$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(X$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:W$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Y$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:X$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Z$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:Y$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AA$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:Z$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AB$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:AA$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AC$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($I$8:AB$8)/Days_in_Year),\"\")","v":""}]
[null,null,null,null,{"$f":"Middle","v":"Middle"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:I$8)+SUM($J$8:J$8))/(2*Days_in_Year)),\"\")","v":0.95859547754672014},{"$f":"IF(K$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:J$8)+SUM($J$8:K$8))/(2*Days_in_Year)),\"\")","v":0.88096023146145541},{"$f":"IF(L$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:K$8)+SUM($J$8:L$8))/(2*Days_in_Year)),\"\")","v":0.80970609509324931},{"$f":"IF(M$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:L$8)+SUM($J$8:M$8))/(2*Days_in_Year)),\"\")","v":0.74421516093129536},{"$f":"IF(N$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:M$8)+SUM($J$8:N$8))/(2*Days_in_Year)),\"\")","v":0.6839422632256309},{"$f":"IF(O$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:N$8)+SUM($J$8:O$8))/(2*Days_in_Year)),\"\")","v":0.62855077937519011},{"$f":"IF(P$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:O$8)+SUM($J$8:P$8))/(2*Days_in_Year)),\"\")","v":0.57771211339631434},{"$f":"IF(Q$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:P$8)+SUM($J$8:Q$8))/(2*Days_in_Year)),\"\")","v":0.53098539834220071},{"$f":"IF(R$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:Q$8)+SUM($J$8:R$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(S$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:R$8)+SUM($J$8:S$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(T$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:S$8)+SUM($J$8:T$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(U$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:T$8)+SUM($J$8:U$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(V$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:U$8)+SUM($J$8:V$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(W$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:V$8)+SUM($J$8:W$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(X$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:W$8)+SUM($J$8:X$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(Y$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:X$8)+SUM($J$8:Y$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(Z$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:Y$8)+SUM($J$8:Z$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(AA$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:Z$8)+SUM($J$8:AA$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(AB$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:AA$8)+SUM($J$8:AB$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(AC$9<=Number_of_Periods,(1+Post_Tax_WACC)^-((SUM($I$8:AB$8)+SUM($J$8:AC$8))/(2*Days_in_Year)),\"\")","v":""}]
[null,null,null,null,{"$f":"End","v":"End"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:J$8)/Days_in_Year),\"\")","v":0.91890528957302442},{"$f":"IF(K$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:K$8)/Days_in_Year),\"\")","v":0.84458206762226495},{"$f":"IF(L$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:L$8)/Days_in_Year),\"\")","v":0.77627028274105225},{"$f":"IF(M$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:M$8)/Days_in_Year),\"\")","v":0.71348371575464364},{"$f":"IF(N$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:N$8)/Days_in_Year),\"\")","v":0.65562396043115823},{"$f":"IF(O$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:O$8)/Days_in_Year),\"\")","v":0.6025955518668733},{"$f":"IF(P$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:P$8)/Days_in_Year),\"\")","v":0.55385620575999384},{"$f":"IF(Q$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:Q$8)/Days_in_Year),\"\")","v":0.50905901264705311},{"$f":"IF(R$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:R$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(S$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:S$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(T$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:T$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(U$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:U$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(V$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:V$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(W$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:W$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(X$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:X$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Y$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:Y$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Z$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:Z$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AA$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:AA$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AB$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:AB$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AC$9<=Number_of_Periods,(1+Post_Tax_WACC)^-(SUM($J$8:AC$8)/Days_in_Year),\"\")","v":""}]
[null,null,null,null,{"$f":"Timing_of_CashFlows&\" Chosen\"","v":"Middle Chosen"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,INDEX(J$38:J$40,MATCH(Timing_of_CashFlows,$E$38:$E$40,0)),\"\")","v":0.95859547754672014},0.88096023146145541,0.80970609509324931,0.74421516093129536,0.6839422632256309,0.62855077937519011,0.57771211339631434,0.53098539834220071,"","","","","","","","","","","",""]
[]
[null,null,null,null,"Pre-Tax Discount Rates"]
[null,null,null,null,{"$f":"Start","v":"Start"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:I$8)/Days_in_Year),\"\")","v":1},{"$f":"IF(K$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:J$8)/Days_in_Year),\"\")","v":0.89537651283574693},{"$f":"IF(L$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:K$8)/Days_in_Year),\"\")","v":0.80194120333784447},{"$f":"IF(M$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:L$8)/Days_in_Year),\"\")","v":0.71825615748413751},{"$f":"IF(N$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:M$8)/Days_in_Year),\"\")","v":0.64330390509507396},{"$f":"IF(O$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:N$8)/Days_in_Year),\"\")","v":0.57599920723764564},{"$f":"IF(P$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:O$8)/Days_in_Year),\"\")","v":0.51589190776387817},{"$f":"IF(Q$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:P$8)/Days_in_Year),\"\")","v":0.46205699096812808},{"$f":"IF(R$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:Q$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(S$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:R$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(T$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:S$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(U$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:T$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(V$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:U$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(W$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:V$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(X$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:W$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Y$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:X$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Z$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:Y$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AA$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:Z$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AB$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:AA$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AC$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($I$8:AB$8)/Days_in_Year),\"\")","v":""}]
[null,null,null,null,{"$f":"Middle","v":"Middle"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:I$8)+SUM($J$8:J$8))/(2*Days_in_Year)),\"\")","v":0.94624336871427905},{"$f":"IF(K$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:J$8)+SUM($J$8:K$8))/(2*Days_in_Year)),\"\")","v":0.84737200693906689},{"$f":"IF(L$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:K$8)+SUM($J$8:L$8))/(2*Days_in_Year)),\"\")","v":0.75894611616217234},{"$f":"IF(M$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:L$8)+SUM($J$8:M$8))/(2*Days_in_Year)),\"\")","v":0.67974774068629906},{"$f":"IF(N$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:M$8)+SUM($J$8:N$8))/(2*Days_in_Year)),\"\")","v":0.60872205426421366},{"$f":"IF(O$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:N$8)+SUM($J$8:O$8))/(2*Days_in_Year)),\"\")","v":0.54511772113215173},{"$f":"IF(P$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:O$8)+SUM($J$8:P$8))/(2*Days_in_Year)),\"\")","v":0.48823300028386507},{"$f":"IF(Q$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:P$8)+SUM($J$8:Q$8))/(2*Days_in_Year)),\"\")","v":0.43728437606304982},{"$f":"IF(R$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:Q$8)+SUM($J$8:R$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(S$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:R$8)+SUM($J$8:S$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(T$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:S$8)+SUM($J$8:T$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(U$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:T$8)+SUM($J$8:U$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(V$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:U$8)+SUM($J$8:V$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(W$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:V$8)+SUM($J$8:W$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(X$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:W$8)+SUM($J$8:X$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(Y$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:X$8)+SUM($J$8:Y$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(Z$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:Y$8)+SUM($J$8:Z$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(AA$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:Z$8)+SUM($J$8:AA$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(AB$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:AA$8)+SUM($J$8:AB$8))/(2*Days_in_Year)),\"\")","v":""},{"$f":"IF(AC$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-((SUM($I$8:AB$8)+SUM($J$8:AC$8))/(2*Days_in_Year)),\"\")","v":""}]
[null,null,null,null,{"$f":"End","v":"End"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:J$8)/Days_in_Year),\"\")","v":0.89537651283574693},{"$f":"IF(K$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:K$8)/Days_in_Year),\"\")","v":0.80194120333784447},{"$f":"IF(L$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:L$8)/Days_in_Year),\"\")","v":0.71825615748413751},{"$f":"IF(M$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:M$8)/Days_in_Year),\"\")","v":0.64330390509507396},{"$f":"IF(N$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:N$8)/Days_in_Year),\"\")","v":0.57599920723764564},{"$f":"IF(O$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:O$8)/Days_in_Year),\"\")","v":0.51589190776387817},{"$f":"IF(P$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:P$8)/Days_in_Year),\"\")","v":0.46205699096812808},{"$f":"IF(Q$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:Q$8)/Days_in_Year),\"\")","v":0.41383991431057182},{"$f":"IF(R$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:R$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(S$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:S$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(T$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:T$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(U$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:U$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(V$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:V$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(W$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:W$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(X$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:X$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Y$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:Y$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(Z$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:Z$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AA$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:AA$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AB$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:AB$8)/Days_in_Year),\"\")","v":""},{"$f":"IF(AC$9<=Number_of_Periods,(1+Pre_Tax_WACC)^-(SUM($J$8:AC$8)/Days_in_Year),\"\")","v":""}]
[null,null,null,null,{"$f":"Timing_of_CashFlows&\" Chosen\"","v":"Middle Chosen"},{"$f":"IF(Terminal_Value_Switch=Yes,\"%\",\"\")","v":"%"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,INDEX(J$44:J$46,MATCH(Timing_of_CashFlows,$E$44:$E$46,0)),\"\")","v":0.94624336871427905},0.84737200693906689,0.75894611616217234,0.67974774068629906,0.60872205426421366,0.54511772113215173,0.48823300028386507,0.43728437606304982,"","","","","","","","","","","",""]
[]
[]
[null,null,null,"Cash Flow Assumptions"]
[]
[null,null,null,null,"Pre-Tax Cash Flows","$'000",null,null,null,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100]
[]
[]
[null,{"$f":"MAX($B$10:$B54)+1","v":2},"Post-Tax NPV"]
[]
[null,null,"Calculation"]
[]
[null,null,null,"Post-Tax"]
[]
[null,null,null,null,{"$f":"E52","v":"Pre-Tax Cash Flows"},{"$f":"F52","v":"$'000"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,J$52,\"\")","v":100},100,100,100,100,100,100,100,"","","","","","","","","","","",""]
[null,null,null,null,"Tax",{"$f":"F61","v":"$'000"},null,null,null,{"$f":"IF(AND(J$9<=Number_of_Periods,J$9-Tax_Delay>0),-OFFSET(J61,,-Tax_Delay)*Tax_Rate,\"\")","v":""},-30,-30,-30,-30,-30,-30,-30,"","","","","","","","","","","",""]
[null,null,null,null,{"$f":"E$24","v":"Terminal Value"},{"$f":"F62","v":"$'000"},null,null,null,{"$f":"IF(AND(J$9=Number_of_Periods,Terminal_Value_Switch=Yes),SUM(J61:J62)*(1+Growth_Rate_in_Perpetuity)/(Post_Tax_WACC-Growth_Rate_in_Perpetuity),\"\")","v":""},"","","","","","",1050.0000000000002,"","","","","","","","","","","",""]
[]
[null,null,null,null,"Total (Undiscounted)",{"$f":"F63","v":"$'000"},null,null,null,{"$f":"SUM(J61:J63)","v":100},{"$f":"SUM(K61:K63)","v":70},70,70,70,70,70,1120.0000000000002,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[null,null,null,null,"Discount Factor","#",null,null,null,{"$f":"IF(J$9<=Number_of_Periods,J$41,\"\")","v":0.95859547754672014},0.88096023146145541,0.80970609509324931,0.74421516093129536,0.6839422632256309,0.62855077937519011,0.57771211339631434,0.53098539834220071,"","","","","","","","","","","",""]
[]
[null,null,null,null,"Present Value(s)",{"$f":"F65","v":"$'000"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,J65*J67,\"\")","v":95.859547754672008},61.66721620230188,56.679426656527454,52.095061265190672,47.87595842579416,43.998554556263308,40.439847937742002,594.7036461432649,"","","","","","","","","","","",""]
[]
[null,null,null,null,"NPV",{"$f":"F69","v":"$'000"},null,null,{"$f":"SUM(J69:AC69)","v":993.3192589417564}]
[]
[null,null,null,null,"TV Tolerance",{"$f":"F24","v":"[1,0]"},{"$f":"AND(Terminal_Value_Switch=Yes,(SUMPRODUCT(J63:AC63,J67:AC67)/I71)>TV_Tolerance)*1","v":0}]
[]
[]
[null,{"$f":"MAX($B$10:$B75)+1","v":3},"Pre-Tax NPV"]
[]
[null,null,"Calculating Pre-Tax Cost of Equity"]
[]
[null,null,null,"Financing Assumptions"]
[]
[null,null,null,null,"Pre-Tax Cost of Debt","%",{"$f":"PreTax_Cost_of_Debt","v":0.08}]
[null,null,null,null,"Pre-Tax Cost of Equity","%",0.15302284284435153]
[null,null,null,null,"Proportion of Debt","%",{"$f":"Proportion_of_Debt","v":0.5}]
[]
[null,null,null,null,"Pre-Tax WACC","%",{"$f":"(PreTax_Cost_of_Debt*Proportion_of_Debt)+(PreTax_Cost_of_Equity*(1-Proportion_of_Debt))","v":0.11651142142217577}]
[]
[]
[null,null,"Calculation"]
[]
[null,null,null,"Pre-Tax"]
[]
[null,null,null,null,{"$f":"E61","v":"Pre-Tax Cash Flows"},{"$f":"F71","v":"$'000"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,J$52,\"\")","v":100},100,100,100,100,100,100,100,"","","","","","","","","","","",""]
[null,null,null,null,{"$f":"E$24","v":"Terminal Value"},{"$f":"F93","v":"$'000"},null,null,null,{"$f":"IF(AND(J$9=Number_of_Periods,Terminal_Value_Switch=Yes),J93*(1+Growth_Rate_in_Perpetuity)/(Pre_Tax_WACC-Growth_Rate_in_Perpetuity),\"\")","v":""},"","","","","","",1056.8697310323014,"","","","","","","","","","","",""]
[]
[null,null,null,null,"Total (Undiscounted)",{"$f":"F94","v":"$'000"},null,null,null,{"$f":"SUM(J93:J94)","v":100},100,100,100,100,100,100,1156.8697310323014,0,0,0,0,0,0,0,0,0,0,0,0]
[]
[null,null,null,null,"Discount Factor","#",null,null,null,{"$f":"IF(J$9<=Number_of_Periods,J$47,\"\")","v":0.94624336871427905},0.84737200693906689,0.75894611616217234,0.67974774068629906,0.60872205426421366,0.54511772113215173,0.48823300028386507,0.43728437606304982,"","","","","","","","","","","",""]
[]
[null,null,null,null,"Present Value(s)",{"$f":"F96","v":"$'000"},null,null,null,{"$f":"IF(J$9<=Number_of_Periods,J96*J98,\"\")","v":94.6243368714279},84.737200693906686,75.894611616217233,67.974774068629912,60.872205426421367,54.511772113215173,48.823300028386505,505.88105852068816,"","","","","","","","","","","",""]
[]
[null,null,null,null,"NPV",{"$f":"F100","v":"$'000"},{"$f":"(ROUND(I71-I102,Rounding_Accuracy)<>0)*1","v":0},null,{"$f":"SUM(J100:AC100)","v":993.31925933889283}]
[]
[null,null,null,null,"TV Tolerance",{"$f":"F24","v":"[1,0]"},{"$f":"AND(Terminal_Value_Switch=Yes,(SUMPRODUCT(J94:AC94,J98:AC98)/I102)>TV_Tolerance)*1","v":0}]
[]
[]
[null,null,"Summary"]
[]
[null,null,null,"Pre-Tax Cost of Equity"]
[]
[null,null,null,null,{"$f":"\"Correct \"&$D$109","v":"Correct Pre-Tax Cost of Equity"},"%",{"$f":"PreTax_Cost_of_Equity","v":0.15302284284435153}]
[null,null,null,null,{"$f":"\"Incorrect \"&$D$109","v":"Incorrect Pre-Tax Cost of Equity"},"%",{"$f":"PostTax_Cost_of_Equity/(1-Tax_Rate)","v":0.17142857142857143}]
[]
[null,null,null,null,"Difference","%",{"$f":"ABS(G111-G112)","v":1.8405728584219905E-2}]

{"sheet":"Timing","index":5,"n_rows":23,"n_cols":29,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,8,0,9],[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Timing"}]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"J$7","v":"2020-12-31"},{"$f":"K$7","v":"2021-12-31"},{"$f":"L$7","v":"2022-12-31"},{"$f":"M$7","v":"2023-12-31"},{"$f":"N$7","v":"2024-12-31"},{"$f":"O$7","v":"2025-12-31"},"2026-12-31","2027-12-31","2028-12-31","2029-12-31","2030-12-31","2031-12-31","2032-12-31","2033-12-31","2034-12-31","2035-12-31",{"$f":"Z$7","v":"2036-12-31"},"2037-12-31","2038-12-31",{"$f":"AC$7","v":"2039-12-31"}]
[null,null,"Start Date",null,null,null,null,null,null,{"$f":"IF(J$9=1,Model_Start_Date,I$7+1)","v":"2020-01-01"},"2021-01-01","2022-01-01","2023-01-01","2024-01-01","2025-01-01","2026-01-01","2027-01-01","2028-01-01","2029-01-01","2030-01-01","2031-01-01","2032-01-01","2033-01-01","2034-01-01","2035-01-01","2036-01-01","2037-01-01","2038-01-01","2039-01-01"]
[null,null,"End Date",null,null,null,null,null,null,{"$f":"EOMONTH(J$6,MOD(Periodicity+Reporting_Month_Factor-MONTH(J$6),Periodicity))","v":"2020-12-31"},"2021-12-31","2022-12-31","2023-12-31","2024-12-31","2025-12-31","2026-12-31","2027-12-31","2028-12-31","2029-12-31","2030-12-31","2031-12-31","2032-12-31","2033-12-31","2034-12-31","2035-12-31","2036-12-31","2037-12-31","2038-12-31","2039-12-31"]
[null,null,"Number of Days",null,null,null,null,null,null,{"$f":"J7-J6+1","v":366},{"$f":"K7-K6+1","v":365},365,365,366,{"$f":"O7-O6+1","v":365},365,365,366,365,365,365,366,365,365,365,366,{"$f":"AA7-AA6+1","v":365},365,365]
[null,null,"Counter",null,null,null,null,null,0,{"$f":"N(I$9)+1","v":1},{"$f":"N(J$9)+1","v":2},3,4,5,{"$f":"N(N$9)+1","v":6},{"$f":"N(O$9)+1","v":7},{"$f":"N(P$9)+1","v":8},{"$f":"N(Q$9)+1","v":9},{"$f":"N(R$9)+1","v":10},{"$f":"N(S$9)+1","v":11},{"$f":"N(T$9)+1","v":12},{"$f":"N(U$9)+1","v":13},{"$f":"N(V$9)+1","v":14},{"$f":"N(W$9)+1","v":15},{"$f":"N(X$9)+1","v":16},{"$f":"N(Y$9)+1","v":17},{"$f":"N(Z$9)+1","v":18},{"$f":"N(AA$9)+1","v":19},20]
[]
[null,{"$f":"MAX($B$10:$B10)+1","v":1},"Timing Assumptions"]
[]
[null,null,"Data (do not change once modelling has commenced)"]
[]
[null,null,null,"Model Start Date",null,null,null,"2020-01-01"]
[]
[null,null,null,"Number of Months in a Full Period",null,null,null,12]
[]
[null,null,null,"Example Reporting Month",null,null,null,12,{"$f":"\"e.g. \"&TEXT(DATE(YEAR(Model_Start_Date)+IF(Example_Reporting_Month<MONTH(Model_Start_Date),1,0),Example_Reporting_Month+1,1)-1,\"dd-Mmm-yy\")","v":"e.g. 31-Dec-20"}]
[]
[null,null,null,"Reporting Month Factor",null,null,null,{"$f":"MOD(Example_Reporting_Month-1,Periodicity)+1","v":12}]
[]
[null,null,null,"Months per Year",null,null,null,12]

{"sheet":"Lookup Data","index":6,"n_rows":26,"n_cols":9,"rows_kept":26,"cut":false,"hidden":false,"merged":[[0,9,0,10],[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Lookup Data"}]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Timing of Cash Flows"]
[]
[null,null,"Lookup Data"]
[]
[null,null,null,"LU_Timing"]
[]
[null,null,null,null,null,{"$f":"D10","v":"LU_Timing"}]
[null,null,null,null,null,"Start",null,{"$f":"F12","v":"LU_Timing"}]
[null,null,null,null,null,"Middle"]
[null,null,null,null,null,"End"]
[]
[]
[null,{"$f":"MAX($B$5:$B17)+1","v":2},"Yes or No"]
[]
[null,null,"Lookup Data"]
[]
[null,null,null,"LU_Yes_No"]
[]
[null,null,null,null,null,{"$f":"D22","v":"LU_Yes_No"}]
[null,null,null,null,"Yes","Yes",null,{"$f":"F24","v":"LU_Yes_No"}]
[null,null,null,null,"No","No"]

{"sheet":"Error Checks","index":7,"n_rows":17,"n_cols":9,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,8,0,9],[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Error Checks"}]
[{"$f":"Model_Name","v":"Chapter 7.3 - SP Calculating the Pre-Tax Cost of Equity.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Error Checks"]
[]
[null,null,"Summary of Errors"]
[]
[null,null,null,"Assumptions"]
[]
[null,null,null,null,"NPVs reconcile",null,null,null,{"$f":"HL_NPV_Check","v":0}]
[null,null,null,null,"Post-Tax TV within tolerance",null,null,null,{"$f":"HL_PostTax_TV_Tolerance","v":0}]
[null,null,null,null,"Pre-Tax TV within tolerance",null,null,null,{"$f":"HL_PreTax_TV_Tolerance","v":0}]
[]
[]
[null,null,null,null,{"$f":"C8","v":"Summary of Errors"},null,null,null,{"$f":"MIN(1,SUM(I11:I15))","v":0}]