{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["GC","Contents","Circ_Diag_MS","Int_Rev_BO","Checks_SC","Err_Chks_BO","Lookup_SC","GL"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":95,"comments":1,"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":"GC","index":0,"n_rows":25,"n_cols":7,"rows_kept":25,"cut":false,"hidden":false,"merged":[[24,6,24,8],[10,2,10,5],[23,6,23,8]]}
["GC"]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Interest Revenue Example"]
[null,null,{"$f":"\"Circular Reference Model\"&Err_Chks_Msg","v":"Circular Reference Model"}]
[null,null,"Go to Table of Contents"]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Primary Developer:  Liam Bastick"]
[]
[null,null,"General Cover Notes:"]
[null,null,"How to calculate interest on average cash balances."]
[]
[null,null,"Any queries, please e-mail:",null,null,null,"liam.bastick@sumproduct.com"]
[null,null,"Website:",null,null,null,"www.sumproduct.com"]

{"sheet":"Contents","index":1,"n_rows":11,"n_cols":17,"rows_kept":11,"cut":false,"hidden":false,"merged":[[2,1,2,8],[7,5,7,6],[7,7,7,15],[8,5,8,6],[8,7,8,15]]}
["C","Table of Contents"]
[null,{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,"Go to Cover Sheet"]
[]
[]
["é","Section & Sheet Titles",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"  Page  "]
[]
[null,null,null,null,null,"a.",null,{"$f":"Circ_Diag_MS!B1","v":"Interest on Average Cash Balance - How the Circular Occurs"},null,null,null,null,null,null,null,null,3]
[null,null,null,null,null,"b.",null,{"$f":"Int_Rev_BO!B1","v":"Interest on Average Cash Balance Calculation"},null,null,null,null,null,null,null,null,4]
[]
[null,"Total Pages:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,4]

{"sheet":"Circ_Diag_MS","index":2,"n_rows":24,"n_cols":47,"rows_kept":24,"cut":false,"hidden":false,"merged":[[9,7,13,31],[23,7,27,31],[23,46,27,70],[9,46,13,70],[2,1,2,11],[3,11,3,12],[3,1,3,2],[3,3,3,6],[3,7,3,8],[3,9,3,10]]}
["MS","Interest on Average Cash Balance - How the Circular Occurs"]
[null,{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,"Go to Table of Contents"]
["é","ç",null,"è"]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"Closing Cash = Opening Cash \n                                   + Change in Cash Held",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Average Cash = Proportion of Opening Cash \n                               + (1 - Proportion of Closing Cash)"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"Change in Cash = Net Cash Receipts \n                        + Interest Revenue",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Interest Revenue = Average Cash × Interest Rate"]

{"sheet":"Int_Rev_BO","index":3,"n_rows":43,"n_cols":17,"rows_kept":43,"cut":false,"hidden":false,"merged":[[2,1,2,5],[8,14,8,17]]}
["BO","Interest on Average Cash Balance Calculation"]
[null,{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,"Go to Table of Contents"]
["é","ç"]
[]
[null,null,null,null,null,null,null,null,"Circular",null,"Goal Seek",null,"Formulae"]
[]
[]
[null,"Cash Flow Statement ($000)",null,null,null,null,null,null,null,null,null,null,null,null,"Assumptions"]
[]
[null,null,true,null,null,null,null,null,{"$f":"IF(LO_Err_Chks_Cust_1_Err_Chk<>0,\"Run Macro\",\"\")","v":""},null,{"$f":"IF(LO_Err_Chks_Cust_2_Err_Chk<>0,\"Run Macro\",\"\")","v":""},null,null,null,"Interest & Tax Rates"]
[]
[null,null,null,"Macro Iterations:",null,null,10,null,null,null,null,null,null,null,null,"Category Names","Rate"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Interest Rate",0.05]
[null,null,"Revenue",null,null,null,null,null,null,null,null,null,null,null,null,"Corporate Tax Rate",0.3]
[]
[null,null,null,"Net Cash Receipts (Pre-Tax and Interest)",null,null,null,null,{"$f":"Non_Interest_Movement","v":100},null,{"$f":"Non_Interest_Movement","v":100},null,{"$f":"Non_Interest_Movement","v":100},null,"Opening Balances"]
[null,null,null,"- Interest Revenue (Circular Calc.)",null,null,null,{"$f":"(Opening_Balance*Proportion_of_Period+(1-Proportion_of_Period)*I32)*Cash_Rate","v":4.325699745547074}]
[null,null,null,"- Interest Revenue (Goal Seek)",null,null,null,3.4047919293820934,null,null,null,null,null,null,null,"Category Names","Balance"]
[null,null,null,"- Illustrative Difference",null,null,null,{"$f":"H18-H19","v":0.92090781616498063},null,null,null,null,null,null,null,"Opening Cash Balance ($000)",50]
[null,null,null,"Interest Revenue",null,null,null,null,{"$f":"IF(CB_Inc_Circular,H18,H19)","v":4.325699745547074},null,4.325699745547074,null,{"$f":"(Opening_Balance*Cash_Rate+(1-Proportion_of_Period)*Non_Interest_Movement*(1-Tax_Rate)*Cash_Rate)\n/(1-(1-Proportion_of_Period)*(1-Tax_Rate)*Cash_Rate)","v":4.325699745547074}]
[null,null,null,"Pre-tax Cashflow",null,null,null,null,{"$f":"SUM(I17,I21)","v":104.32569974554707},null,{"$f":"SUM(K17:K21)","v":104.32569974554707},null,{"$f":"SUM(M17:M21)","v":104.32569974554707},null,"Period Cashflows"]
[null,null,null,"Cash Tax Paid",null,null,null,null,{"$f":"MIN(0,-SUM(I22)*Tax_Rate)","v":-31.297709923664119},null,{"$f":"MIN(0,-SUM(K22)*Tax_Rate)","v":-31.297709923664119},null,{"$f":"MIN(0,-SUM(M22)*Tax_Rate)","v":-31.297709923664119}]
[null,null,null,"Change in Cash Held",null,null,null,null,{"$f":"SUM(I22:I23)","v":73.027989821882954},null,{"$f":"SUM(K22:K23)","v":73.027989821882954},null,{"$f":"SUM(M22:M23)","v":73.027989821882954},null,null,"Category Names","Balance"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Non-Interest Cash Movement ($000)",100]
[null,"Balance Sheet ($000)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Proportion into Period of Movement",null,0.5]
[null,null,"Cash at Bank"]
[]
[null,null,null,"Opening Balance",null,null,null,null,{"$f":"$Q$20","v":50},null,{"$f":"$Q$20","v":50},null,{"$f":"$Q$20","v":50}]
[null,null,null,{"$f":"D24","v":"Change in Cash Held"},null,null,null,null,{"$f":"I24","v":73.027989821882954},null,{"$f":"K24","v":73.027989821882954},null,{"$f":"M24","v":73.027989821882954}]
[null,null,null,"Closing Balance",null,null,null,null,{"$f":"SUM(I30:I31)","v":123.02798982188295},null,{"$f":"SUM(K30:K31)","v":123.02798982188295},null,{"$f":"SUM(M30:M31)","v":123.02798982188295}]
[]
[null,null,null,"Average Balance",null,null,null,null,{"$f":"I30*$Q$27+(1-$Q$27)*I32","v":86.513994910941477},null,{"$f":"K30*$Q$27+(1-$Q$27)*K32","v":86.513994910941477},null,{"$f":"M30*$Q$27+(1-$Q$27)*M32","v":86.513994910941477}]
[]
[null,null,null,"Interest Rate"]
[null,null,null,"Target ",null,null,null,null,{"$f":"Cash_Rate","v":0.05},null,{"$f":"Cash_Rate","v":0.05},null,{"$f":"Cash_Rate","v":0.05}]
[null,null,null,"Actual",null,null,null,null,{"$f":"I21/I34","v":0.05},null,{"$f":"K21/K34","v":0.05},null,{"$f":"M21/M34","v":0.05}]
[null,null,null,"Difference",null,null,null,null,{"$f":"I37-I38","v":0},null,{"$f":"K37-K38","v":0},null,{"$f":"M37-M38","v":0}]
[]
[null,"Checks"]
[]
[null,null,"Error Check (Run Macro):",null,null,null,null,null,{"$f":"IF(ROUND(Int_Diff_1,3)<>0,1,0)","v":0},null,{"$f":"IF(ROUND(Int_Diff_2,3)<>0,1,0)","v":0},null,{"$f":"IF(ROUND(Int_Diff_3,3)<>0,1,0)","v":0}]

{"sheet":"Checks_SC","index":4,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":true,"merged":[[11,2,11,5]]}
["SC"]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Error Checks"]
[null,null,"Section 2."]
[null,null,{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,null,"Go to Table of Contents"]
[null,null,"ç","è"]

{"sheet":"Err_Chks_BO","index":5,"n_rows":24,"n_cols":13,"rows_kept":24,"cut":false,"hidden":true,"merged":[[2,1,2,5]]}
["BO","Error Checks"]
["Err_Chks",{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,"Go to Table of Contents"]
["é","ç","è"]
[]
[]
[null,"Error Checks"]
[]
[null,null,true]
[]
[null,null,"Errors Detected - Summary"]
[]
[null,null,null,{"$f":"D24","v":"Total Error Areas:"},null,null,null,null,{"$f":"Err_Chks_Ttl_Areas","v":0}]
[null,null,null,"Error Message (Empty if None):",null,null,null,null,{"$f":"IF(OR(NOT(CB_Err_Chks_Show_Msg),Err_Chks_Ttl_Areas=0),\"\",IF(Err_Chks_Ttl_Areas=1,\" (Error in \"&INDEX(CA_Err_Chks_Area_Names,MATCH(1,CA_Err_Chks_Flags,0))&\")\",\" (\"&TEXT(Err_Chks_Ttl_Areas,\"#,##0\")&\" Errors Detected)\"))","v":""}]
[]
[null,null,"Error Checks by Area"]
[]
[null,null,null,"Error Checks",null,null,null,null,null,null,"Check","Include?","Msg Flag"]
[]
[null,null,null,{"$f":"IF(ISERROR(Int_Rev_BO!B1),\"Miscellaneous Sheet\",Int_Rev_BO!B1)","v":"Interest on Average Cash Balance Calculation"},null,null,null,null,null,null,{"$f":"IF(ISERROR(Int_Rev_BO!$M$43),1,(Int_Rev_BO!$M$43<>0)*1)","v":0},"Yes",{"$f":"K20*(L20=Yes)","v":0}]
[null,null,null,{"$f":"IF(ISERROR(Int_Rev_BO!B1),\"Miscellaneous Sheet\",Int_Rev_BO!B1)","v":"Interest on Average Cash Balance Calculation"},null,null,null,null,null,null,{"$f":"IF(ISERROR(Int_Rev_BO!$I$43),1,(Int_Rev_BO!$I$43<>0)*1)","v":0},"Yes",{"$f":"K21*(L21=Yes)","v":0}]
[null,null,null,{"$f":"IF(ISERROR(Int_Rev_BO!B1),\"Miscellaneous Sheet\",Int_Rev_BO!B1)","v":"Interest on Average Cash Balance Calculation"},null,null,null,null,null,null,{"$f":"IF(ISERROR(Int_Rev_BO!$K$43),1,(Int_Rev_BO!$K$43<>0)*1)","v":0},"Yes",{"$f":"K22*(L22=Yes)","v":0}]
[]
[null,null,null,"Total Error Areas:",null,null,null,null,null,null,null,null,{"$f":"SUMIF(CA_Err_Chks_Inc,Yes,CA_Err_Chks_Flags)","v":0}]

{"sheet":"Lookup_SC","index":6,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":true,"merged":[[11,2,11,5]]}
["SC"]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Model Lookup Tables"]
[null,null,"Section 3."]
[null,null,{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,null,"Go to Table of Contents"]
[null,null,"ç","è"]

{"sheet":"GL","index":7,"n_rows":38,"n_cols":13,"rows_kept":38,"cut":false,"hidden":true,"merged":[[2,1,2,2]]}
["GL","General Lookup"]
[null,{"$f":"Model_Name","v":"Circular Reference Model"}]
[null,"Go to Table of Contents"]
["é","ç"]
[]
[]
[null,"Months Lookup",null,null,"Names:","Model Period Type Lookup",null,null,"Names:","Model Denomination Lookup",null,null,"Names:"]
[]
[null,null,"Month",null,"LU_Mths",null,"Model Period Type",null,"LU_Pers",null,"Denomination",null,"LU_Denom"]
[null,null,"January",null,"Jan",null,"Annual",null,"Ann",null,"$Billions",null,"Billions"]
[null,null,"February",null,"Feb",null,"Semi-Annual",null,"Semi_Ann",null,"$Millions",null,"Millions"]
[null,null,"March",null,"Mar",null,"Quarterly",null,"Qtrly",null,"$'000",null,"Thousands"]
[null,null,"April",null,"Apr",null,"Monthly",null,"Mthly",null,"$",null,"Currency"]
[null,null,"May",null,"May"]
[null,null,"June",null,"Jun"]
[null,null,"July",null,"Jul","Period Names Lookup",null,null,"Names:","Time Constants Lookup",null,null,"Names:"]
[null,null,"August",null,"Aug"]
[null,null,"September",null,"Sep",null,"Period Name",null,"LU_Per_Names",null,"Time Constant"]
[null,null,"October",null,"Oct",null,"Year",null,"Yr_Name",null,60,null,"Secs_In_Min"]
[null,null,"November",null,"Nov",null,"Half Year",null,"Half_Yr_Name",null,60,null,"Mins_In_Hr"]
[null,null,"December",null,"Dec",null,"Quarter",null,"Qtr_Name",null,24,null,"Hrs_In_Day"]
[null,null,null,null,null,null,"Month",null,"Mth_Name",null,7,null,"Days_In_Wk"]
[null,null,null,null,null,null,null,null,null,null,52,null,"Wks_In_Yr"]
[null,"Model Quarter Lookup",null,null,"Names:",null,null,null,null,null,3,null,"Mths_In_Qtr"]
[null,null,null,null,null,"Periods in Year Lookup",null,null,"Names:",null,6,null,"Mths_In_Half_Yr"]
[null,null,"Quarter",null,"LU_Qtrs"]
[null,null,"Q1",null,"Qtr_1",null,"Periods in Year",null,"LU_Pers_In_Yr"]
[null,null,"Q2",null,"Qtr_2",null,1,null,"Yrs_In_Yr","Conversion Factor Lookup",null,null,"Names:"]
[null,null,"Q3",null,"Qtr_3",null,2,null,"Halves_In_Yr"]
[null,null,"Q4",null,"Qtr_4",null,4,null,"Qtrs_In_Yr",null,"Conversion Factor"]
[null,null,null,null,null,null,12,null,"Mths_In_Yr",null,10,null,"Ten"]
[null,null,null,null,null,null,null,null,null,null,100,null,"Hundred"]
[null,"Model Half Year Lookup",null,null,"Names:",null,null,null,null,null,1000,null,"Thousand"]
[null,null,null,null,null,"Yes / No Input Lookup",null,null,"Names:",null,1000000,null,"Million"]
[null,null,"Half Year",null,"LU_Halves",null,null,null,null,null,1000000000,null,"Billion"]
[null,null,"H1",null,"Half_1",null,"Yes / No Input",null,"LU_Yes_No"]
[null,null,"H2",null,"Half_2",null,"Yes",null,"Yes"]
[null,null,null,null,null,null,"No",null,"No"]