{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Input","InstructionsAndHelp"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":1,"defined_names":291,"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":"Input","index":0,"n_rows":262,"n_cols":39,"rows_kept":262,"cut":false,"hidden":false,"merged":[[83,20,83,21],[83,18,83,19],[83,12,83,13],[83,14,83,15],[3,9,3,13],[5,11,12,13],[13,11,19,13],[83,8,83,9],[83,10,83,11],[5,15,12,18],[67,1,67,3],[83,16,83,17],[74,1,74,2],[75,1,75,2],[76,1,76,2],[70,1,70,2],[72,1,72,2],[71,1,71,2],[73,1,73,2],[77,1,77,2],[83,2,83,3],[78,1,78,2],[83,4,83,5],[83,6,83,7],[157,1,157,2],[158,1,158,2],[69,1,69,2],[156,1,156,2],[153,1,153,2],[155,1,155,2],[163,1,163,2],[159,1,159,2],[161,1,161,2],[160,1,160,2],[162,1,162,2]]}
[]
[null,"Barton Consultancy"]
[]
["Client Assisted Account Based Pension Input Sheet",null,null,null,null,null,null,"Version",1.1599999999999999,"Instructions and Help"]
[null,null,null,null,null,null,null,"Published","2019-07-17"]
["Instructor Person",null,null,null,null,null,null,null," ",null,null,"For this workbook to operate optimally, users need to have macro settings at low or medium security and certain references avaialable.   If this is not the case, close this workbook, change the settings and reopen the workbook",null,null,null,"To ensure required references are available,  key Alt F11 to invoke Visual Basic,  select Tools, References and ensure that the following are selected.  Visual Basic for Applications, Microsoft Excel 15.0 Object Library, OLE Automation, Microsoft Forms 2.0 Object library and Microsoft Outlook 15.0 Object Library.\n"]
[]
["Identification code"]
["Title",null,{"$f":"IF(InstrID>0,\"\",IF(InstrTitle=\"\",\"\",IF(OR(EXACT(InstrTitle,LOWER(InstrTitle)),EXACT(InstrTitle,UPPER(InstrTitle))),\"Proper case? -->\",\"\")))","v":""},null,{"$f":"IF(InstrID>0, \"No need to input person details if unchanged\",IF(L207=1, \"   <----- Strange title\",\"\"))","v":"   <----- Strange title"}]
["First name",null,{"$f":"IF(InstrID>0,\"\",IF(InstrFirstName=\"\",\"\",IF(EXACT(InstrFirstName,UPPER(InstrFirstName)),\"Proper case? -->\",\"\")))","v":""}]
["Surname",null,{"$f":"IF(InstrID>0,\"\",IF(InstrSurname=\"\",\"\",IF(EXACT(InstrSurname,UPPER(InstrSurname)),\"Proper case? -->\",\"\")))","v":""}]
["Telephone"]
["Email"]
[null,null,null,null,null,null,null,null,null,null,null,"If \"#NAME?\" appears, it is likely that the Analysis Tool Pak add in is not active on your computer.\n\nPlease invoke Excel Help and search for \"YEARFRAC\". This will lead you to how to instal the add in."]
["Instructor Firm"]
[]
["Identification code"]
["Name",null,{"$f":"IF(InstrFirmID>0,\"\",IF(InstrFirmName=\"\",\"\",IF(EXACT(InstrFirmName,UPPER(InstrFirmName)),\"Proper case? -->\",\"\")))","v":""},null,{"$f":"IF(InstrFirmID>0, \"No need to input firm details if unchanged\",\"\")","v":""}]
["Street address",null,{"$f":"IF(InstrFirmID>0,\"\",IF(InstrFirmSt=\"\",\"\",IF(EXACT(InstrFirmSt,UPPER(InstrFirmSt)),\"Proper case? -->\",\"\")))","v":""}]
["Street address line 2",null,{"$f":"IF(InstrFirmID>0,\"\",IF(InstrFirmSt2=\"\",\"\",IF(EXACT(InstrFirmSt2,UPPER(InstrFirmSt2)),\"Proper case? -->\",\"\")))","v":""}]
["Street town"]
["Street postcode",null,null,null,null,null,null,{"$f":"IF(InstrFirmPost<>\"\", \"\",IF(InstrFirmSt2=\"\",InstrFirmState &\"       \" & InstrFirmStPost,\"\"))","v":"       "}]
["State"]
["Postal address",null,{"$f":"IF(InstrFirmID>0,\"\",IF(InstrFirmPost=\"\",\"\",IF(EXACT(InstrFirmPost,UPPER(InstrFirmPost)),\"Proper case? -->\",\"\")))","v":""}]
["Postal town"]
["Postal postcode"]
["Telephone"]
["Email"]
[]
["Fund Information"]
[]
["Identification code",null,null,null,{"$f":"IF(ISBLANK(SubjID), \"\", IF(ISNUMBER(SubjID), \"\", \"   <----- ID must be numeric.  Fund reference may be alpha numeric\"))","v":""}]
["Fund name",null,{"$f":"IF(SubjID>0,\"\",IF(SubjName=\"\",\"\",IF(EXACT(D33,UPPER(D33)),\"Proper case? -->\",\"\")))","v":""},null,{"$f":"IF(SubjID>0,\"No need to input fund details if unchanged\",IF(SubjName<>\"\",\"\",\"Please input fund name\"))","v":"Please input fund name"}]
["Trustee name",null,{"$f":"IF(SubjID>0,\"\",IF(SubjTrustee=\"\",\"\",IF(EXACT(D34,UPPER(D34)),\"Proper case? -->\",\"\")))","v":""},null,{"$f":"IF(SubjID>0,\"No need to input fund details if unchanged\",IF(SubjTrustee<>\"\",\"\",\"Please input trustee name\"))","v":"Please input trustee name"}]
["Fund reference "]
["Job reference"]
["Financial year end",null,null,"2013-06-30",{"$f":"IF(AND(ISNUMBER(CertDate),CertDate>=EOMONTH( NOW(), - 24),CertDate< NOW()),\"\",\" < -- Not a valid date within the past 24 months\")","v":" < -- Not a valid date within the past 24 months"}]
["Segregated current pension liabilites",null,null,null,{"$f":"IF(SegCP>0,\"   <----- This is usually nil\",\"\")","v":""}]
["Segregated non current pension liabilities",null,null,null,{"$f":"IF(SegNCP>0,\"   <----- This is usually nil\",\"\")","v":""}]
["Are final balances * to be calculated? (True or False)",null,null,null,{"$f":"IF(AND(CalcFinBal<>TRUE,CalcFinBal<>FALSE), \"< ---Should be true or false\",\"\")","v":""}]
["Unsegregated superannuation liabilities",null,null,null,{"$f":"IF(CalcFinBal=TRUE,IF(ABS(UnsegTot-D164)>0.005,\"Does not yet reconcile with  \"&$A164&TEXT(D164,\" $###,###.00\"),\"\"),IF(ABS(UnsegTot-D79)>0.005,\"Does not yet reconcile with \"&$A79&TEXT(D79,\" $###,###.00\"),\"\"))","v":""},"*  Excluding deferred tax liabilities"]
[{"$f":"IF(CalcFinBal=TRUE,\"Administration expenses\", \"Not needed\")","v":"Not needed"},null,null,null,{"$f":"IF(AND(CalcFinBal=TRUE,Admin=0),\"   <----- Administration expenses must be supplied to calculate final balances\",\"\")","v":""}]
[{"$f":"IF(CalcFinBal=TRUE,\"Franked income\", \"Not needed\")","v":"Not needed"},null,null,null,{"$f":"IF(AND(CalcFinBal=TRUE,SUM(Franked,Foreign,OtherIncome)=0),\"   <----- Income must be supplied to calculate final balances\",\"\")","v":""}]
[{"$f":"IF(CalcFinBal=TRUE,\"Foreign income\", \"Not needed\")","v":"Not needed"}]
[{"$f":"IF(CalcFinBal=TRUE,\"Foreign tax credits\", \"Not needed\")","v":"Not needed"}]
[{"$f":"IF(CalcFinBal=TRUE,\"All other income and capital gains\", \"Not needed\")","v":"Not needed"}]
[{"$f":"IF(CalcFinBal=TRUE,\"Full potential investment tax on foreign income\", \"Not needed\")","v":"Not needed"},null,{"$f":"IF(CalcFinBal=TRUE,\"Suggest \" &TEXT(0.15* Foreign,\"###,###.00\") & \"   \",\"\")","v":""},null,{"$f":"IF(AND(CalcFinBal=TRUE,SUM(FullForeign,FullTax)=0),\"   <----- Full tax must be supplied to calculate final balances\",\"\")","v":""}]
[{"$f":"IF(CalcFinBal=TRUE,\"Full potential investment tax ignoring foreign income\", \"Not needed\")","v":"Not needed"},null,null,null,{"$f":"IF(AND(CalcFinBal=TRUE,SUM(FullForeign,FullTax)=0),\"   <----- Full tax must be supplied to calculate final balances\",\"\")","v":""}]
[]
["Additional periods to be certified (Dates inclusive)",null,null,null,null,null,null,null,"These are  needed when the fund moves from either all in pension or all in accumulation to a blend"]
[null,null,null,"From","To"]
["Additional period 1",null,null,"2017-07-01","2018-06-30",{"$f":"IF(D52>=E52,\"Wrong\",\"\")","v":""}]
["Additional period 2",null,null,"2018-07-01","2018-06-30",{"$f":"IF(D53>=E53,\"Wrong\",\"\")","v":"Wrong"}]
["Additional period 3",null,null,"2018-07-01","2018-06-30","Wrong"]
["Additional period 4",null,null,"2018-07-01","2018-06-30","Wrong"]
[]
[]
["Instructor Confirmations"]
[]
["Account Based Pensions permitted by Trust Deed (True or False)",null,null,null,null,null,null,null,false,{"$f":"IF(AND(ConfABP<>TRUE,ConfABP<>FALSE), \"< ---Should be true or false\",\"\")","v":""}]
["Pensions paid in accordance with Trust Deed and pension creation minutes  (True or False)",null,null,null,null,null,null,null,false,{"$f":"IF(AND(ConfAccord<>TRUE,ConfAccord<>FALSE), \"< ---Should be true or false\",\"\")","v":""}]
["Unless specified otherwise, all information relates to unsegregated assets and liabilities  (True or False)",null,null,null,null,null,null,null,false,{"$f":"IF(AND(ConfUnseg<>TRUE,ConfUnseg<>FALSE), \"< ---Should be true or false\",\"\")","v":""}]
["Confirmed that pensions are Retirement Pensions within the meaning of ITAA 97  section 307-75 (True or False)",null,null,null,null,null,null,null,false]
[]
[]
["Individual Account Information  (Unsegregated Elements Only)"]
[]
[null,{"$f":"IF(R68>0,\"Pens start & name incompatible\",\"\")","v":""},null,null,{"$f":"IF(E69=\"\", \"\", IF(OR(AND(OR(ISNUMBER(FIND(\"ABP\",UPPER(E69),  1)), ISNUMBER(FIND(\"PENS\",UPPER(E69),  1))), E71=0), AND(ISNUMBER(FIND( \"ACCUM\",UPPER(E69), 1)), E71>0)), \"Problem?\", \"\"))","v":""},{"$f":"IF(F69=\"\", \"\", IF(OR(AND(OR(ISNUMBER(FIND(\"ABP\",UPPER(F69),  1)), ISNUMBER(FIND(\"PENS\",UPPER(F69),  1))), F71=0), AND(ISNUMBER(FIND( \"ACCUM\",UPPER(F69), 1)), F71>0)), \"Problem?\", \"\"))","v":""},"","","","","","","","",null,{"$f":"SUM(IF(E68:N68<>\"\", 1, 0))","v":0}]
["Account name",null,null,"All",null,null,null,null,null,null,null,null,null,null,null,"Error count"]
["Account commenced",{"$f":"IF(P70>0,\"Problem --->\",\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlAccComm>CertDate,1,0))+SUM(IF(AccountNames<>\"\",1,0)*IF(IndvlAccComm=0,1,0))","v":0}]
["Retirement phase pension commenced ",{"$f":"IF(P71>0,\"Problem --->\",\"\")","v":"Problem --->"},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlPensComm>CertDate,1,0) + IF(IndvlPensComm<IndvlAccComm,1,0)*IF(IndvlPensComm>0,1,0)) + IF(SUM(IndvlPensComm)=0,1,0)","v":1}]
["Beginning year balance",{"$f":"IF(P72>0,\"Problem --->\",\"\")","v":""},null,{"$f":"SUM(E72:N72)","v":0},null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlBegBal<0,1,0))","v":0}]
["Total non concessional contributions ",{"$f":"IF(P73>0,\"Not yet reconciled to transactions --->\",\"\")","v":""},null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlTotNonConcessional<>IndvlInfrdNon,1,0))","v":0}]
["Total concessional contributions ","",null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlTotConcessional<>IndvlInfrdConc,1,0))","v":0}]
["Total transfers in","",null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlTotTransIn<>IndvlInfrdIn,1,0))","v":0}]
["Total insurance premiums","",null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlTotInsPrem<>IndvlInfrdInsOut,1,0))","v":0}]
["Total insurance claim receipts","",null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlTotInsClaim<>IndvlInfrdInsIn,1,0))","v":0}]
["Total benefits, insurance or transfers out ","",null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlTotPens<>IndvlInfrdOut,1,0))","v":0}]
[{"$f":"IF(CalcFinBal=FALSE,\"Closing balance\", \"Calculated closing balance (Input not needed)\")","v":"Closing balance"},{"$f":"IF(AND(CalcFinBal=FALSE,SUM(IF(IndvlEndBal<0,1,0)*IF(IndvlAccComm>0,1,0))>0),\"Negative balances not allowed\",\"\")","v":""},null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(IF(IndvlBegBal<0,1,0))","v":0}]
[null,null,null,{"$f":"IF(AND(CalcFinBal=FALSE,D79<200000),\"Final balances unusually low.  Please check  ^\",\"\")","v":"Final balances unusually low.  Please check  ^"}]
[null,null,null,{"$f":"IF(ROWS(IndvlDates)<4,\"Warning!  Transaction array must contain at least 4 rows\",\"\")","v":""}]
["Individual Account Transactions",null,null,null,"User please insert or delete rows within the shaded area below (always retain at least four rows) copy the top row of this colour shaded area to any inserted rows"]
[]
[null,null,{"$f":"INDEX(AccountNames,C106)","v":0},null,{"$f":"INDEX(AccountNames,E106)","v":0},null,{"$f":"INDEX(AccountNames,G106)","v":0},null,{"$f":"INDEX(AccountNames,I106)","v":0},null,{"$f":"INDEX(AccountNames,K106)","v":0},null,{"$f":"INDEX(AccountNames,M106)","v":0},null,{"$f":"INDEX(AccountNames,O106)","v":0},null,{"$f":"INDEX(AccountNames,Q106)","v":0},null,{"$f":"INDEX(AccountNames,S106)","v":0},null,{"$f":"INDEX(AccountNames,U106)","v":0},null,null,"Inclusive start date",{"$f":"D52","v":"2017-07-01"},"Inclusive end date",{"$f":"E52","v":"2018-06-30"},"Inclusive start date",{"$f":"D53","v":"2018-07-01"},"Inclusive end date",{"$f":"E53","v":"2018-06-30"},"Inclusive start date",{"$f":"D54","v":"2018-07-01"},"Inclusive end date",{"$f":"E54","v":"2018-06-30"},"Inclusive start date",{"$f":"D55","v":"2018-07-01"},"Inclusive end date",{"$f":"E55","v":"2018-06-30"}]
["Date",{"$f":"IF(OR(L237>0,L238>0),\"Problem with dates\",\"\")","v":""},"Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out","Contributions / transfers in","Pensions / transfers out",null,"Days","Pension product","Non pension product",null,"Days","Pension product","Non pension product",null,"Days","Pension product","Non pension product",null,"Days","Pension product","Non pension product"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF($A86>AA$84,0,MAX(0,MAX(AA$84,$A86)-MAX(Y$84,$A86)+1))","v":365},{"$f":"X86*SUM($C86:$V86*TransactionSign*PensFlag)","v":0},{"$f":"X86*SUM($C86:$V86*TransactionSign*NonPensFlag)","v":0},null,{"$f":"IF($A86>AE$84,0,MAX(0,MAX(AE$84,$A86)-MAX(AC$84,$A86)+1))","v":0},{"$f":"AB86*SUM($C86:$V86*TransactionSign*PensFlag)","v":0},{"$f":"AB86*SUM($C86:$V86*TransactionSign*NonPensFlag)","v":0},null,{"$f":"IF($A86>AI$84,0,MAX(0,MAX(AI$84,$A86)-MAX(AG$84,$A86)+1))","v":0},{"$f":"AF86*SUM($C86:$V86*TransactionSign*PensFlag)","v":0},{"$f":"AF86*SUM($C86:$V86*TransactionSign*NonPensFlag)","v":0},null,{"$f":"IF($A86>AM$84,0,MAX(0,MAX(AM$84,$A86)-MAX(AK$84,$A86)+1))","v":0},{"$f":"AJ86*SUM($C86:$V86*TransactionSign*PensFlag)","v":0},{"$f":"AJ86*SUM($C86:$V86*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X87*SUM($C87:$V87*TransactionSign*PensFlag)","v":0},{"$f":"X87*SUM($C87:$V87*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB87*SUM($C87:$V87*TransactionSign*PensFlag)","v":0},{"$f":"AB87*SUM($C87:$V87*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF87*SUM($C87:$V87*TransactionSign*PensFlag)","v":0},{"$f":"AF87*SUM($C87:$V87*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ87*SUM($C87:$V87*TransactionSign*PensFlag)","v":0},{"$f":"AJ87*SUM($C87:$V87*TransactionSign*NonPensFlag)","v":0}]
["00:00:00",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X88*SUM($C88:$V88*TransactionSign*PensFlag)","v":0},{"$f":"X88*SUM($C88:$V88*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB88*SUM($C88:$V88*TransactionSign*PensFlag)","v":0},{"$f":"AB88*SUM($C88:$V88*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF88*SUM($C88:$V88*TransactionSign*PensFlag)","v":0},{"$f":"AF88*SUM($C88:$V88*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ88*SUM($C88:$V88*TransactionSign*PensFlag)","v":0},{"$f":"AJ88*SUM($C88:$V88*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,0,0,null,1,0,0,null,1,0,0,null,1,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X90*SUM($C90:$V90*TransactionSign*PensFlag)","v":0},{"$f":"X90*SUM($C90:$V90*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB90*SUM($C90:$V90*TransactionSign*PensFlag)","v":0},{"$f":"AB90*SUM($C90:$V90*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF90*SUM($C90:$V90*TransactionSign*PensFlag)","v":0},{"$f":"AF90*SUM($C90:$V90*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ90*SUM($C90:$V90*TransactionSign*PensFlag)","v":0},{"$f":"AJ90*SUM($C90:$V90*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X91*SUM($C91:$V91*TransactionSign*PensFlag)","v":0},{"$f":"X91*SUM($C91:$V91*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB91*SUM($C91:$V91*TransactionSign*PensFlag)","v":0},{"$f":"AB91*SUM($C91:$V91*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF91*SUM($C91:$V91*TransactionSign*PensFlag)","v":0},{"$f":"AF91*SUM($C91:$V91*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ91*SUM($C91:$V91*TransactionSign*PensFlag)","v":0},{"$f":"AJ91*SUM($C91:$V91*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X92*SUM($C92:$V92*TransactionSign*PensFlag)","v":0},{"$f":"X92*SUM($C92:$V92*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB92*SUM($C92:$V92*TransactionSign*PensFlag)","v":0},{"$f":"AB92*SUM($C92:$V92*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF92*SUM($C92:$V92*TransactionSign*PensFlag)","v":0},{"$f":"AF92*SUM($C92:$V92*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ92*SUM($C92:$V92*TransactionSign*PensFlag)","v":0},{"$f":"AJ92*SUM($C92:$V92*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X93*SUM($C93:$V93*TransactionSign*PensFlag)","v":0},{"$f":"X93*SUM($C93:$V93*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB93*SUM($C93:$V93*TransactionSign*PensFlag)","v":0},{"$f":"AB93*SUM($C93:$V93*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF93*SUM($C93:$V93*TransactionSign*PensFlag)","v":0},{"$f":"AF93*SUM($C93:$V93*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ93*SUM($C93:$V93*TransactionSign*PensFlag)","v":0},{"$f":"AJ93*SUM($C93:$V93*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X94*SUM($C94:$V94*TransactionSign*PensFlag)","v":0},{"$f":"X94*SUM($C94:$V94*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB94*SUM($C94:$V94*TransactionSign*PensFlag)","v":0},{"$f":"AB94*SUM($C94:$V94*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF94*SUM($C94:$V94*TransactionSign*PensFlag)","v":0},{"$f":"AF94*SUM($C94:$V94*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ94*SUM($C94:$V94*TransactionSign*PensFlag)","v":0},{"$f":"AJ94*SUM($C94:$V94*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X95*SUM($C95:$V95*TransactionSign*PensFlag)","v":0},{"$f":"X95*SUM($C95:$V95*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB95*SUM($C95:$V95*TransactionSign*PensFlag)","v":0},{"$f":"AB95*SUM($C95:$V95*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF95*SUM($C95:$V95*TransactionSign*PensFlag)","v":0},{"$f":"AF95*SUM($C95:$V95*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ95*SUM($C95:$V95*TransactionSign*PensFlag)","v":0},{"$f":"AJ95*SUM($C95:$V95*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X96*SUM($C96:$V96*TransactionSign*PensFlag)","v":0},{"$f":"X96*SUM($C96:$V96*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB96*SUM($C96:$V96*TransactionSign*PensFlag)","v":0},{"$f":"AB96*SUM($C96:$V96*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF96*SUM($C96:$V96*TransactionSign*PensFlag)","v":0},{"$f":"AF96*SUM($C96:$V96*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ96*SUM($C96:$V96*TransactionSign*PensFlag)","v":0},{"$f":"AJ96*SUM($C96:$V96*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X97*SUM($C97:$V97*TransactionSign*PensFlag)","v":0},{"$f":"X97*SUM($C97:$V97*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB97*SUM($C97:$V97*TransactionSign*PensFlag)","v":0},{"$f":"AB97*SUM($C97:$V97*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF97*SUM($C97:$V97*TransactionSign*PensFlag)","v":0},{"$f":"AF97*SUM($C97:$V97*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ97*SUM($C97:$V97*TransactionSign*PensFlag)","v":0},{"$f":"AJ97*SUM($C97:$V97*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X98*SUM($C98:$V98*TransactionSign*PensFlag)","v":0},{"$f":"X98*SUM($C98:$V98*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB98*SUM($C98:$V98*TransactionSign*PensFlag)","v":0},{"$f":"AB98*SUM($C98:$V98*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF98*SUM($C98:$V98*TransactionSign*PensFlag)","v":0},{"$f":"AF98*SUM($C98:$V98*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ98*SUM($C98:$V98*TransactionSign*PensFlag)","v":0},{"$f":"AJ98*SUM($C98:$V98*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,365,{"$f":"X99*SUM($C99:$V99*TransactionSign*PensFlag)","v":0},{"$f":"X99*SUM($C99:$V99*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AB99*SUM($C99:$V99*TransactionSign*PensFlag)","v":0},{"$f":"AB99*SUM($C99:$V99*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AF99*SUM($C99:$V99*TransactionSign*PensFlag)","v":0},{"$f":"AF99*SUM($C99:$V99*TransactionSign*NonPensFlag)","v":0},null,0,{"$f":"AJ99*SUM($C99:$V99*TransactionSign*PensFlag)","v":0},{"$f":"AJ99*SUM($C99:$V99*TransactionSign*NonPensFlag)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF($A100>AA$84,0,MAX(0,MAX(AA$84,$A100)-MAX(Y$84,$A100)+1))","v":365},{"$f":"X100*SUM($C100:$V100*TransactionSign*PensFlag)","v":0},{"$f":"X100*SUM($C100:$V100*TransactionSign*NonPensFlag)","v":0},null,{"$f":"IF($A100>AE$84,0,MAX(0,MAX(AE$84,$A100)-MAX(AC$84,$A100)+1))","v":0},{"$f":"AB100*SUM($C100:$V100*TransactionSign*PensFlag)","v":0},{"$f":"AB100*SUM($C100:$V100*TransactionSign*NonPensFlag)","v":0},null,{"$f":"IF($A100>AI$84,0,MAX(0,MAX(AI$84,$A100)-MAX(AG$84,$A100)+1))","v":0},{"$f":"AF100*SUM($C100:$V100*TransactionSign*PensFlag)","v":0},{"$f":"AF100*SUM($C100:$V100*TransactionSign*NonPensFlag)","v":0},null,{"$f":"IF($A100>AM$84,0,MAX(0,MAX(AM$84,$A100)-MAX(AK$84,$A100)+1))","v":0},{"$f":"AJ100*SUM($C100:$V100*TransactionSign*PensFlag)","v":0},{"$f":"AJ100*SUM($C100:$V100*TransactionSign*NonPensFlag)","v":0}]
["All",null,{"$f":"SUM(C86:C100)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,null,null,{"$f":"SUM(Y86:Y100)","v":0},{"$f":"SUM(Z86:Z100)","v":0},null,null,{"$f":"SUM(AC86:AC100)","v":0},{"$f":"SUM(AD86:AD100)","v":0},null,null,{"$f":"SUM(AG86:AG100)","v":0},{"$f":"SUM(AH86:AH100)","v":0},null,null,{"$f":"SUM(AK86:AK100)","v":0},{"$f":"SUM(AL86:AL100)","v":0}]
["Discrepancy",null,{"$f":"C101-(INDEX(IndvlTotNonConcessional,C$106) + INDEX(IndvlTotConcessional,C$106) + INDEX(IndvlTotTransIn,C$106) + INDEX(IndvlTotInsClaim,C$106))","v":0},{"$f":"D101-INDEX(IndvlTotPens,D$106) - INDEX(IndvlTotInsPrem,D$106)","v":0},{"$f":"E101-(INDEX(IndvlTotNonConcessional,E$106) + INDEX(IndvlTotConcessional,E$106) + INDEX(IndvlTotTransIn,E$106) + INDEX(IndvlTotInsClaim,E$106))","v":0},{"$f":"F101-INDEX(IndvlTotPens,F$106) - INDEX(IndvlTotInsPrem,F$106)","v":0},{"$f":"G101-(INDEX(IndvlTotNonConcessional,G$106) + INDEX(IndvlTotConcessional,G$106) + INDEX(IndvlTotTransIn,G$106) + INDEX(IndvlTotInsClaim,G$106))","v":0},{"$f":"H101-INDEX(IndvlTotPens,H$106) - INDEX(IndvlTotInsPrem,H$106)","v":0},{"$f":"I101-(INDEX(IndvlTotNonConcessional,I$106) + INDEX(IndvlTotConcessional,I$106) + INDEX(IndvlTotTransIn,I$106) + INDEX(IndvlTotInsClaim,I$106))","v":0},{"$f":"J101-INDEX(IndvlTotPens,J$106) - INDEX(IndvlTotInsPrem,J$106)","v":0},{"$f":"K101-(INDEX(IndvlTotNonConcessional,K$106) + INDEX(IndvlTotConcessional,K$106) + INDEX(IndvlTotTransIn,K$106) + INDEX(IndvlTotInsClaim,K$106))","v":0},{"$f":"L101-INDEX(IndvlTotPens,L$106) - INDEX(IndvlTotInsPrem,L$106)","v":0},{"$f":"M101-(INDEX(IndvlTotNonConcessional,M$106) + INDEX(IndvlTotConcessional,M$106) + INDEX(IndvlTotTransIn,M$106) + INDEX(IndvlTotInsClaim,M$106))","v":0},{"$f":"N101-INDEX(IndvlTotPens,N$106) - INDEX(IndvlTotInsPrem,N$106)","v":0},{"$f":"O101-(INDEX(IndvlTotNonConcessional,O$106) + INDEX(IndvlTotConcessional,O$106) + INDEX(IndvlTotTransIn,O$106) + INDEX(IndvlTotInsClaim,O$106))","v":0},{"$f":"P101-INDEX(IndvlTotPens,P$106) - INDEX(IndvlTotInsPrem,P$106)","v":0},{"$f":"Q101-(INDEX(IndvlTotNonConcessional,Q$106) + INDEX(IndvlTotConcessional,Q$106) + INDEX(IndvlTotTransIn,Q$106) + INDEX(IndvlTotInsClaim,Q$106))","v":0},{"$f":"R101-INDEX(IndvlTotPens,R$106) - INDEX(IndvlTotInsPrem,R$106)","v":0},{"$f":"S101-(INDEX(IndvlTotNonConcessional,S$106) + INDEX(IndvlTotConcessional,S$106) + INDEX(IndvlTotTransIn,S$106) + INDEX(IndvlTotInsClaim,S$106))","v":0},{"$f":"T101-INDEX(IndvlTotPens,T$106) - INDEX(IndvlTotInsPrem,T$106)","v":0},{"$f":"U101-(INDEX(IndvlTotNonConcessional,U$106) + INDEX(IndvlTotConcessional,U$106) + INDEX(IndvlTotTransIn,U$106) + INDEX(IndvlTotInsClaim,U$106))","v":0},{"$f":"V101-INDEX(IndvlTotPens,V$106) - INDEX(IndvlTotInsPrem,V$106)","v":0},null,{"$f":"IF(YrStart>AA$84,0,MAX(0,MAX(AA$84,YrStart)-MAX(Y$84,YrStart)+1))","v":365},{"$f":"X102*SUM(IndvlBegBal*IndvlPensFlag)","v":0},{"$f":"X102*SUM(IndvlBegBal*IndvlNonPensFlag)","v":0},null,{"$f":"IF(YrStart>AE$84,0,MAX(0,MAX(AE$84,YrStart)-MAX(AC$84,YrStart)+1))","v":0},{"$f":"AB102*SUM(IndvlBegBal*IndvlPensFlag)","v":0},{"$f":"AB102*SUM(IndvlBegBal*IndvlNonPensFlag)","v":0},null,{"$f":"IF(YrStart>AI$84,0,MAX(0,MAX(AI$84,YrStart)-MAX(AG$84,YrStart)+1))","v":0},{"$f":"AF102*SUM(IndvlBegBal*IndvlPensFlag)","v":0},{"$f":"AF102*SUM(IndvlBegBal*IndvlNonPensFlag)","v":0},null,{"$f":"IF(YrStart>AM$84,0,MAX(0,MAX(AM$84,YrStart)-MAX(AK$84,YrStart)+1))","v":0},{"$f":"AJ102*SUM(IndvlBegBal*IndvlPensFlag)","v":0},{"$f":"AJ102*SUM(IndvlBegBal*IndvlNonPensFlag)","v":0}]
["Pension average",null,{"$f":"(SUM((CertDate+1)*C$86:C$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,C$106),IndvlDates,INDEX(IndvlPensDates,C$106)) *C$86:C$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*D$86:D$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,D$106),IndvlDates,INDEX(IndvlPensDates,D$106)) *D$86:D$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*E$86:E$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,E$106),IndvlDates,INDEX(IndvlPensDates,E$106)) *E$86:E$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*F$86:F$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,F$106),IndvlDates,INDEX(IndvlPensDates,F$106)) *F$86:F$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*G$86:G$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,G$106),IndvlDates,INDEX(IndvlPensDates,G$106)) *G$86:G$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*H$86:H$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,H$106),IndvlDates,INDEX(IndvlPensDates,H$106)) *H$86:H$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*I$86:I$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,I$106),IndvlDates,INDEX(IndvlPensDates,I$106)) *I$86:I$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*J$86:J$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,J$106),IndvlDates,INDEX(IndvlPensDates,J$106)) *J$86:J$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*K$86:K$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,K$106),IndvlDates,INDEX(IndvlPensDates,K$106)) *K$86:K$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*L$86:L$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,L$106),IndvlDates,INDEX(IndvlPensDates,L$106)) *L$86:L$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*M$86:M$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,M$106),IndvlDates,INDEX(IndvlPensDates,M$106)) *M$86:M$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*N$86:N$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,N$106),IndvlDates,INDEX(IndvlPensDates,N$106)) *N$86:N$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*O$86:O$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,O$106),IndvlDates,INDEX(IndvlPensDates,O$106)) *O$86:O$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*P$86:P$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,P$106),IndvlDates,INDEX(IndvlPensDates,P$106)) *P$86:P$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*Q$86:Q$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,Q$106),IndvlDates,INDEX(IndvlPensDates,Q$106)) *Q$86:Q$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*R$86:R$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,R$106),IndvlDates,INDEX(IndvlPensDates,R$106)) *R$86:R$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*S$86:S$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,S$106),IndvlDates,INDEX(IndvlPensDates,S$106)) *S$86:S$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*T$86:T$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,T$106),IndvlDates,INDEX(IndvlPensDates,T$106)) *T$86:T$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*U$86:U$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,U$106),IndvlDates,INDEX(IndvlPensDates,U$106)) *U$86:U$100))*DaysRecip","v":0},{"$f":"(SUM((CertDate+1)*V$86:V$100)-SUM(IF(IndvlDates>INDEX(IndvlPensDates,V$106),IndvlDates,INDEX(IndvlPensDates,V$106)) *V$86:V$100))*DaysRecip","v":0},null,null,{"$f":"Y102+Y101","v":0},{"$f":"Z102+Z101","v":0},{"$f":"Y103/(Y103+Z103)","v":{"$error":"#DIV/0!"}},null,{"$f":"AC102+AC101","v":0},{"$f":"AD102+AD101","v":0},{"$f":"AC103/(AC103+AD103)","v":{"$error":"#DIV/0!"}},null,{"$f":"AG102+AG101","v":0},{"$f":"AH102+AH101","v":0},{"$f":"AG103/(AG103+AH103)","v":{"$error":"#DIV/0!"}},null,{"$f":"AK102+AK101","v":0},{"$f":"AL102+AL101","v":0},{"$f":"AK103/(AK103+AL103)","v":{"$error":"#DIV/0!"}}]
["All average",null,{"$f":"SUM(((CertDate+1)-IndvlDates)*C$86:C$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*D$86:D$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*E$86:E$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*F$86:F$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*G$86:G$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*H$86:H$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*I$86:I$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*J$86:J$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*K$86:K$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*L$86:L$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*M$86:M$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*N$86:N$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*O$86:O$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*P$86:P$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*Q$86:Q$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*R$86:R$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*S$86:S$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*T$86:T$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*U$86:U$100)*DaysRecip","v":0},{"$f":"SUM(((CertDate+1)-IndvlDates)*V$86:V$100)*DaysRecip","v":0}]
["Inward cash after pension commencment",null,{"$f":"IF(INDEX(IndvlPensComm,C$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,C$106),C86:C100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,E$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,E$106),E86:E100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,G$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,G$106),G86:G100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,I$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,I$106),I86:I100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,K$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,K$106),K86:K100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,M$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,M$106),M86:M100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,O$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,O$106),O86:O100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,Q$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,Q$106),Q86:Q100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,S$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,S$106),S86:S100,0)))","v":0},null,{"$f":"IF(INDEX(IndvlPensComm,U$106)=0,0,SUM(IF(IndvlDates>INDEX(IndvlPensComm,U$106),U86:U100,0)))","v":0}]
[null,null,1,1,{"$f":"1+C106","v":2},{"$f":"1+D106","v":2},3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10]
["Transaction sign",null,{"$f":"IF(C106=B106,-1,1)","v":1},{"$f":"IF(D106=C106,-1,1)","v":-1},1,-1,1,-1,1,-1,1,-1,1,-1,1,-1,1,-1,1,-1,1,-1]
["Pension flag",null,{"$f":"IF(INDEX(IndvlPensComm,C106)>0,1,0)","v":0},{"$f":"IF(INDEX(IndvlPensComm,D106)>0,1,0)","v":0},0,{"$f":"IF(INDEX(IndvlPensComm,F106)>0,1,0)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
["Non Pension flag",null,{"$f":"IF(C108=1,0,1)","v":1},{"$f":"IF(D108=1,0,1)","v":1},1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
["Date calculators can be copied to dates above",null,"Weekly","Fortnightly","End of month","Day of month","Same interval as before"]
[null,null,{"$f":"YrStart","v":"2012-07-01"},{"$f":"YrStart","v":"2012-07-01"},{"$f":"YrStart","v":"2012-07-01"},{"$f":"YrStart","v":"2012-07-01"},{"$f":"YrStart","v":"2012-07-01"}]
[null,null,{"$f":"7+C111","v":"2012-07-08"},{"$f":"14+D111","v":"2012-07-15"},{"$f":"EOMONTH(E111,1)","v":"2012-08-31"},{"$f":"EOMONTH(F111,0) + DAY(F111)","v":"2012-08-01"},{"$f":"2*G111-G110","v":{"$error":"#VALUE!"}}]
[]
["Errors",null,{"$f":"IF(SUM(ErrorOccurence)=0, \"No errors\", \"Error at row \")","v":"Error at row "},{"$f":"IF(NextError <9999,INDEX(ErrorRow,NextError),\"\")","v":9},{"$f":"IF(NextError<9999,INDEX(ErrorDescription,NextError),\"\")","v":"Instructor title not recognised- please use Mr Mrs Ms Miss or Dr"}]
[null,null,{"$f":"IF(NextError = 99999,\"Use the button below to send this spreadsheet to Barton@Actuary.com.au to prepare the necessary certificate.\",SUM(ErrorOccurence) & \" remaining errors\")","v":"28 remaining errors"}]
[]
[]
["Key results",null,null,null,{"$f":"IF(CalcFinBal=TRUE,\"See row \"&ROW(E164) &  \" for individual account balances\",\"\")","v":""}]
[]
["Average unsegregated pension liabilities ",null,null,{"$f":"AvPens","v":0}]
["Average unsegregated non pension liabilities ",null,null,{"$f":"AvNonPens","v":0}]
["Tax free proportion of unsegregated income",null,null,{"$f":"PensPpn","v":0}]
[{"$f":"\"Unsegregated pension liabilities at \"&TEXT(CertDate,\"dd mmm yyyy\")","v":"Unsegregated pension liabilities at 30 Jun 2013"},null,{"$f":"IF(ABS(1240.40870876351-1240.41290092689)>0.01,\"Error\",\"\")","v":""},{"$f":"SUM(IndvlShareAdoptFinal*IndvlPensEndYr)","v":0}]
[{"$f":"\"Unsegregated non pension liabilities at \"&TEXT(CertDate,\"dd mmm yyyy\")","v":"Unsegregated non pension liabilities at 30 Jun 2013"},null,{"$f":"IF(ABS(1240.40870876351-1240.41290092689)>0.01,\"Error\",\"\")","v":""},{"$f":"SUM(IndvlShareAdoptFinal)-UnsegPens","v":0}]
["Fee payable  (Sep 2009)",null,null,{"$f":"IF(CalcFinBal=TRUE,BalFee,0) + CertFee","v":250}]
["Does instructor accept responsibility for fee? (True or False)",null,null,false,{"$f":"IF(ConfFee = FALSE, \"<---Instructor must accept responsibility for fee\", IF(AND(ConfFee<>TRUE, ConfFee<>FALSE), \"<-----Should be True or False\", \"\"))","v":"<---Instructor must accept responsibility for fee"}]
[]
["Alternative period tax free proportions"]
[{"$f":"\"From \"&TEXT(D52,\"dd Mmm yy\")&\" to \"&TEXT(E52,\"dd Mmm yy\")&\" inclusive\"","v":"From 01 Jul 17 to 30 Jun 18 inclusive"},null,null,{"$f":"IF(SUM(IF(ISBLANK($X$86:$X$100),1,0))>0, \" Need to copy formulae in range TransactionAddedPdCalc from first row\",IFERROR(AA103,\"NA\"))","v":"NA"}]
[{"$f":"\"From \"&TEXT(D53,\"dd Mmm yy\")&\" to \"&TEXT(E53,\"dd Mmm yy\")&\" inclusive\"","v":"From 01 Jul 18 to 30 Jun 18 inclusive"},null,null,{"$f":"IF(SUM(IF(ISBLANK($X$86:$X$100),1,0))>0, \" Need to copy formulae in range TransactionAddedPdCalc from first row\",IFERROR(AE103,\"NA\"))","v":"NA"}]
[{"$f":"\"From \"&TEXT(D54,\"dd Mmm yy\")&\" to \"&TEXT(E54,\"dd Mmm yy\")&\" inclusive\"","v":"From 01 Jul 18 to 30 Jun 18 inclusive"},null,null,{"$f":"IF(SUM(IF(ISBLANK($X$86:$X$100),1,0))>0, \" Need to copy formulae in range TransactionAddedPdCalc from first row\",IFERROR(AI103,\"NA\"))","v":"NA"}]
[{"$f":"\"From \"&TEXT(D55,\"dd Mmm yy\")&\" to \"&TEXT(E55,\"dd Mmm yy\")&\" inclusive\"","v":"From 01 Jul 18 to 30 Jun 18 inclusive"},null,null,{"$f":"IF(SUM(IF(ISBLANK($X$86:$X$100),1,0))>0, \" Need to copy formulae in range TransactionAddedPdCalc from first row\",IFERROR(AM103,\"NA\"))","v":"NA"}]
[]
[]
["Individual Account Details",null,null,null,"Unsegregated accounts only"]
[]
[null,null,null,"All",{"$f":"E$69","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"P$69","v":"Error count"},"Required total"]
["Net cash inflow",null,null,{"$f":"SUM(E138:N138)","v":0},{"$f":"INDEX(IndvlTotTrans,2*(E$200-1)+1)- INDEX(IndvlTotTrans,2*(E$200))","v":0},0,0,0,0,0,0,0,0,0]
["Average pension",null,null,{"$f":"SUM(E139:N139)","v":0},{"$f":"(CertDate-MAX(MIN(CertDate,INDEX(IndvlPensDates,E$200)),YrStart-1))*INDEX(IndvlBegBal,E$200) *DaysRecip+ INDEX(IndvlPensAv,2*E$200-1) -  INDEX(IndvlPensAv,2*E$200)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(AvPens>AvAll,1,0)","v":0}]
["Average non pension",null,null,{"$f":"SUM(E140:N140)","v":0},{"$f":"INDEX(IndvlAvAll,E$200)-INDEX(IndvlAvPens,E$200)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(AvPens>AvAll,1,0)","v":0}]
["Average all",null,null,{"$f":"SUM(E141:N141)","v":0},{"$f":"(CertDate-(YrStart-1))*INDEX(IndvlBegBal,E$200) *DaysRecip+ INDEX(IndvlAllAv,2*E$200-1) -  INDEX(IndvlAllAv,2*E$200)","v":0},0,0,0,0,0,0,0,0,0]
["Pension proportion",null,null,{"$f":"IF(D141<>0,D139/D141,0)","v":0},{"$f":"IF(INDEX(IndvlAvAll,E$200)<>0,INDEX(IndvlAvPens,E$200)/INDEX(IndvlAvAll,E$200),0)","v":0},0,0,0,0,0,0,0,0,0]
["Non pension proportion",null,null,{"$f":"1-D142","v":1},{"$f":"1-INDEX(IndvlPensPpn,E$200)","v":1},1,1,1,1,1,1,1,1,1]
["Proportion of pension",null,{"$f":"IF(ABS(D144-Q144)>0.00001,\"Error\",\"\")","v":"Error"},{"$f":"SUM(E144:N144)","v":0},{"$f":"IF(AvPens<>0,INDEX(IndvlAvPens,E$200)/AvPens,0)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(ABS(D144-Q144)>0.00001,1,0)","v":1},1]
["Proportion of non pension",null,{"$f":"IF(ABS(D145-Q145)>0.00001,\"Error\",\"\")","v":"Error"},{"$f":"SUM(E145:N145)","v":0},{"$f":"IF(AvNonPens<>0,INDEX(IndvlAvNonPens,E$200)/AvNonPens,0)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(ABS(D145-Q145)>0.00001,1,0)","v":1},1]
["Pension proportion of all",null,{"$f":"IF(ABS(D146-Q146)>0.00001,\"Error\",\"\")","v":""},{"$f":"SUM(E146:N146)","v":0},{"$f":"IF(AvAll<>0,INDEX(IndvlAvPens,E$200)/AvAll,0)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(ABS(D146-Q146)>0.00001,1,0)","v":0},{"$f":"D142","v":0}]
["Non pension proportion of all",null,{"$f":"IF(ABS(D147-Q147)>0.00001,\"Error\",\"\")","v":"Error"},{"$f":"SUM(E147:N147)","v":0},{"$f":"IF(AvAll<>0,INDEX(IndvlAvNonPens,E$200)/AvAll,0)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(ABS(D147-Q147)>0.00001,1,0)","v":1},{"$f":"D143","v":1}]
["Proportion of all",null,{"$f":"IF(ABS(D148-Q148)>0.00001,\"Error\",\"\")","v":"Error"},{"$f":"SUM(E148:N148)","v":0},{"$f":"IF(AvAll<>0,INDEX(IndvlAvAll,E$200)/AvAll,0)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(ABS(D148-Q148)>0.00001,1,0)","v":1},1]
["Pension flag",null,null,null,{"$f":"IF(E$71>0,1,0)","v":0},{"$f":"IF(F$71>0,1,0)","v":0},0,0,0,0,0,0,0,0]
["Non pension flag",null,null,null,{"$f":"IF(E$71>0,0,1)","v":1},{"$f":"IF(F$71>0,0,1)","v":1},1,1,1,1,1,1,1,1]
[null,null,null,"All",{"$f":"E$69","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"P$69","v":"Error count"},"Required total"]
[{"$f":"IF(CalcFinBal=TRUE,\"Beginning balance\",\"Calculation not required by client\")","v":"Calculation not required by client"},null,null,{"$f":"ROUND(SUM(E152:N152),4)","v":0},{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlBegBal,E$200),0)","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"IF(AND(CalcFinBal=TRUE,ABS(D152-Q152)>0.0015),1,0)","v":0},{"$f":"IF(CalcFinBal = TRUE,D72,0)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Net cash inflow above\",\"Calculation not required by client\")","v":"Calculation not required by client"},null,null,{"$f":"ROUND(SUM(E153:N153),4)","v":0},{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlTotTrans,2*E$200-1)- INDEX(IndvlTotTrans,2*(E$200)),0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"IF(CalcFinBal = TRUE,D73+D74+D75-D76 + D77-D78,0)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of administration\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D154-Admin)>0.001,\"See line \" & ROW(Admin),\"\")","v":""},null,{"$f":"ROUND(SUM(E154:N154),4)","v":0},{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlPpnAll,E$200)*Admin,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"Admin","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Administration tax deduction\",\"Calculation not required by client\")","v":"Calculation not required by client"},null,null,{"$f":"ROUND(SUM(E155:M155),4)","v":0},{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlNonPensPpnAll,E$200)*SuperTax *Admin,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"SuperTax * Admin * NonPensPpn","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Contributions tax\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D156-$Q156)>0.001,\"See lines \" & ROW(E74) & \" and \"& ROW(SuperTax),\"\")","v":""},null,{"$f":"ROUND(SUM(E156:N156),4)","v":0},{"$f":"IF(CalcFinBal= TRUE,SuperTax *INDEX(IndvlTotConcessional,E$200),0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"IF(CalcFinBal= TRUE,SuperTax *$D$74,0)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of franked income\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D157-$Q157)>0.001,\"See line \" & ROW(Franked),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlPpnAll,E$200)*Franked,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"Franked","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of foreign income\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D158-$Q158)>0.001,\"See line \" & ROW(Foreign),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlPpnAll,E$200)*Foreign,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"Foreign","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of other income and capital gains\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D159-$Q159)>0.001,\"See line \" & ROW(OtherIncome),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlPpnAll,E$200)*OtherIncome,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"OtherIncome","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of tax on foreign income\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D160-$Q160)>0.0001,\"See lines \" & ROW(FullForeign) & \" and \"& ROW(NonPensPpnAll),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlNonPensPpnAll,E$200)*FullForeign,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"ROUND(FullForeign*NonPensPpnAll,4)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of tax on all other income and capital gains\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D161-$Q161)>0.0001,\"See lines \" & ROW(FullTax) & \" and \"& ROW(NonPensPpnAll),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlNonPensPpnAll,E$200)*FullTax,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"ROUND(NonPensPpnAll*FullTax,4)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of franking credits\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D162-$Q162)>0.001,\"See lines \" & ROW(FrankCredit) & \" and \"& ROW(PpnAll),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlPpnAll,E$200)*FrankCredit,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"ROUND(FrankCredit,4)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Share of foreign tax credits\",\"Calculation not required by client\")","v":"Calculation not required by client"},{"$f":"IF(ABS(D163-$Q163)>0.001,\"See lines \" & ROW(ForeignTax) & \", \" & ROW(PpnNonPens) & \" and \"& ROW(ShareForeignTax),\"\")","v":""},null,0,{"$f":"IF(CalcFinBal= TRUE,INDEX(IndvlPpnNonPens,E$200)*$Q$163,0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"MIN(ShareForeignTax,ForeignTax)","v":0}]
[{"$f":"IF(CalcFinBal=TRUE,\"Calculated final balance\",\"Calculation not required by client\")","v":"Calculation not required by client"},null,null,{"$f":"SUM(E164:N164)","v":0},{"$f":"IF(CalcFinBal= TRUE,ROUND(INDEX(IndvlShareBegBal,E$200)+INDEX(IndvlShareNetInflow,E$200) +INDEX(IndvlShareFranked,E$200)+INDEX(IndvlShareForeignInc,E$200)+INDEX(IndvlShareOtherInc,E$200)  + INDEX(IndvlShareFranking,E$200)+INDEX(IndvlShareForeignCredit,E$200) - (INDEX(IndvlShareAdmin,E$200) -INDEX(IndvlShareAdminTax,E$200)+INDEX(IndvlShareContTax,E$200)+INDEX(IndvlShareForeignTax,E$200)+INDEX(IndvlShareOtherTax,E$200)),2),0)","v":0},0,0,0,0,0,0,0,0,0,null,0,{"$f":"SUM(Q152:Q153) - Q154+Q155-Q156+SUM(Q157:Q159) - SUM(Q160:Q161) + SUM(Q162:Q163)","v":0}]
["Adopted final balance",null,null,{"$f":"SUM(E165:N165)","v":0},{"$f":"IF(CalcFinBal=TRUE,INDEX(IndvlShareCalcFinal,E$200),INDEX(IndvlEndBal,E$200))","v":0},0,0,0,0,0,0,0,0,0,null,null,{"$f":"IF(CalcFinBal=TRUE,Q164,Q79)","v":0}]
[]
[]
["Other services offered by Barton Consultancy and associated companies to accountants and their clients include: -"]
[null,"Valuation of superannuation interests for family law purposes"]
[null,"Valuation of life and remainder interests and sums required for Inheritance Act (Family and Dependants Provision) purposes"]
[null,"Full service financial planning"]
[null,"Low cost origination of investments and insurances"]
[]
[]
["Control information"]
[]
["Year start","YrStart",null,{"$f":"DATE(YEAR(CertDate)-IF(MONTH(CertDate)<=6,1,0),7,1)","v":"2012-07-01"}]
["Days in year","YrDays",null,{"$f":"CertDate-YrStart+1","v":365}]
["Days reciprocal","DaysRecip",null,{"$f":"1/YrDays","v":2.7397260273972603E-3}]
["Corporate tax rate","Corp Tax",null,0.3]
["Superannuation tax rate","SuperTax",null,0.15]
["GST inclusive fee for certificate","CertFee",null,250]
["GST inclusive fee for balance calculation","BalFee",null,50]
["Franking credits","FrankCredit",null,{"$f":"CorpTax * Franked/(1-CorpTax)","v":0}]
["Default increment weeks","Dflt",null,0]
[]
[]
["Individual Account Control Information"]
[]
[null,null,null,null,{"$f":"E$69","v":0},0,0,0,0,0,0,0,0,0,null,{"$f":"P$69","v":"Error count"},"Required total"]
[]
["Inferred non concessional",null,null,null,{"$f":"ROUND(INDEX(IndvlNetCashIn,E$200)+INDEX(IndvlTotPens,E$200)+ INDEX(IndvlTotInsPrem,E$200)- INDEX(IndvlTotInsClaim,E$200)-INDEX(IndvlTotConcessional,E$200)-INDEX(IndvlTotTransIn,E$200),2)","v":0},0,0,0,0,0,0,0,0,0]
["Inferred concessional",null,null,null,{"$f":"ROUND(INDEX(IndvlNetCashIn,E$200)+INDEX(IndvlTotPens,E$200)  + INDEX(IndvlTotInsPrem,E$200)- INDEX(IndvlTotInsClaim,E$200)   -INDEX(IndvlTotNonConcessional,E$200)-INDEX(IndvlTotTransIn,E$200),2)","v":0},0,0,0,0,0,0,0,0,0]
["Inferred transfers in ",null,null,null,{"$f":"ROUND(INDEX(IndvlNetCashIn,E$200)+INDEX(IndvlTotPens,E$200) + INDEX(IndvlTotInsPrem,E$200)- INDEX(IndvlTotInsClaim,E$200)  -INDEX(IndvlTotConcessional,E$200)-INDEX(IndvlTotNonConcessional,E$200),2)","v":0},0,0,0,0,0,0,0,0,0]
["Inferred insurance out",null,null,null,{"$f":"ROUND(INDEX(IndvlTotNonConcessional,E$200)+INDEX(IndvlTotConcessional,E$200) +INDEX(IndvlTotTransIn,E$200) -  INDEX(IndvlTotPens,E$200)  + INDEX(IndvlTotInsClaim,E$200)    -INDEX(IndvlNetCashIn,E$200),2)","v":0},0,0,0,0,0,0,0,0,0]
["Inferred insurance in",null,null,null,{"$f":"ROUND(INDEX(IndvlNetCashIn,E$200)+INDEX(IndvlTotPens,E$200) + INDEX(IndvlTotInsPrem,E$200)- INDEX(IndvlTotTransIn,E$200)  -INDEX(IndvlTotConcessional,E$200)-INDEX(IndvlTotNonConcessional,E$200),2)","v":0},0,0,0,0,0,0,0,0,0]
["Inferred outflow",null,null,null,{"$f":"ROUND(INDEX(IndvlTotNonConcessional,E$200)+INDEX(IndvlTotConcessional,E$200) +INDEX(IndvlTotTransIn,E$200) -  INDEX(IndvlTotInsPrem,E$200)  + INDEX(IndvlTotInsClaim,E$200)    -INDEX(IndvlNetCashIn,E$200),2)","v":0},0,0,0,0,0,0,0,0,0]
["Pension date",null,null,null,{"$f":"IF(OR(INDEX(IndvlPensComm,E$200)>CertDate,INDEX(IndvlPensComm,E$200) =0),CertDate+1,IF(INDEX(IndvlPensComm,E$200)< YrStart,YrStart, INDEX(IndvlPensComm,E$200))-1)","v":"2013-07-01"},"2013-07-01","2013-07-01","2013-07-01","2013-07-01","2013-07-01","2013-07-01","2013-07-01","2013-07-01","2013-07-01"]
["Pension at certificate date",null,null,null,{"$f":"IF(AND(INDEX(IndvlPensComm,E$200)>0,INDEX(IndvlPensComm,E$200)<=CertDate),1,0)","v":0},0,0,0,0,0,0,0,0,0]
[null,null,null,null,1,{"$f":"1+E200","v":2},{"$f":"1+F200","v":3},4,5,6,7,8,9,10]
[]
[]
["Error situation",null,null,null,null,null,null,null,"Next error",null,{"$f":"MIN(IF(ErrorOccurence >0,ErrorSerial,99999))","v":2}]
[]
["Serial","Description",null,null,null,null,null,null,null,null,"Row","Occurrence"]
[1,"Instructor ID must be numeric",null,null,null,null,null,null,null,null,{"$f":"ROW(InstrID)","v":8},{"$f":"IF(E8<>\"\", 1, 0)","v":0}]
[{"$f":"1+A206","v":2},"Instructor title not recognised- please use Mr Mrs Ms Miss or Dr",null,null,null,null,null,null,null,null,{"$f":"ROW(InstrTitle)","v":9},{"$f":"IF(OR(ISBLANK(InstrTitle),AND(InstrTitle<>\"Mr\",InstrTitle<>\"Mrs\",InstrTitle<>\"Ms\",InstrTitle<>\"Miss\",InstrTitle<>\"Dr\")),1,0)*IF(ISNUMBER(InstrID),0,1)","v":1}]
[3,"Instructor casing incorrect",null,null,null,null,null,null,null,null,{"$f":"ROW(InstrTitle)","v":9},{"$f":"IF(InstrID>0,0,IF(InstrTitle=\"\",0,IF(OR(EXACT(InstrTitle,LOWER(InstrTitle)),EXACT(InstrTitle,UPPER(InstrTitle))),1,0)))","v":0}]
[4,"Instructor Firm ID must be numeric",null,null,null,null,null,null,null,null,{"$f":"ROW(InstrFirmID)","v":17},{"$f":"IF(E17<>\"\", 1, 0)","v":0}]
[5,"Fund ID must be numeric",null,null,null,null,null,null,null,null,{"$f":"ROW(SubjID)","v":32},{"$f":"IF(E32<>\"\", 1, 0)","v":0}]
[6,"Certificate date must be a valid date within the past 24 months",null,null,null,null,null,null,null,null,{"$f":"ROW(CertDate)","v":37},{"$f":"IF(E37<>\"\", 1, 0)","v":1}]
[7,{"$f":"\"Failure to confirm \"&A60","v":"Failure to confirm Account Based Pensions permitted by Trust Deed (True or False)"},null,null,null,null,null,null,null,null,{"$f":"ROW(ConfABP)","v":60},{"$f":"IF(ConfABP<>TRUE,1,0)","v":1}]
[8,{"$f":"\"Failure to confirm \"&A61","v":"Failure to confirm Pensions paid in accordance with Trust Deed and pension creation minutes  (True or False)"},null,null,null,null,null,null,null,null,{"$f":"ROW(ConfAccord)","v":61},{"$f":"IF(ConfAccord<>TRUE,1,0)","v":1}]
[9,{"$f":"\"Failure to confirm \"&A62","v":"Failure to confirm Unless specified otherwise, all information relates to unsegregated assets and liabilities  (True or False)"},null,null,null,null,null,null,null,null,{"$f":"ROW(ConfUnseg)","v":62},{"$f":"IF(ConfUnseg<>TRUE,1,0)","v":1}]
[10,"Unidentified instructor- missing name",null,null,null,null,null,null,null,null,{"$f":"ROW(InstrFirstName)","v":10},{"$f":"IF(InstrID>0, 0, IF(InstrFirstName<>\"\", 0, 1))","v":1}]
[11,"Unidentified instructor firm- missing name",null,null,null,null,null,null,null,null,{"$f":"ROW(InstrFirmName)","v":18},{"$f":"IF(InstrFirmID>0,0,IF(InstrFirmName<>\"\",0,1))","v":1}]
[12,"Unidentified fund- missing name",null,null,null,null,null,null,null,null,{"$f":"ROW(SubjName)","v":33},{"$f":"IF(SubjID>0,0,IF(SubjName<>\"\",0,1))","v":1}]
[13,"Unidentified trustee- missing name",null,null,null,null,null,null,null,null,{"$f":"ROW(SubjTrustee)","v":34},{"$f":"IF(SubjID>0,0,IF(SubjTrustee<>\"\",0,1))","v":1}]
[14,"Certificate date ot a valid date within the past 24 months",null,null,null,null,null,null,null,null,{"$f":"ROW(CertDate)","v":37},{"$f":"IF(E37<>\"\",1,0)","v":1}]
[15,{"$f":"A41 & \" does not reconcile with total of input closing balances\"","v":"Unsegregated superannuation liabilities does not reconcile with total of input closing balances"},null,null,null,null,null,null,null,null,{"$f":"ROW(UnsegTot)","v":41},{"$f":"IF( CalcFinBal= FALSE,IF(UnsegTot<>D79,1,0),0)","v":0}]
[16,{"$f":"A41 & \" does not reconcile with total of calculated closing balances\"","v":"Unsegregated superannuation liabilities does not reconcile with total of calculated closing balances"},null,null,null,null,null,null,null,null,{"$f":"ROW(UnsegTot)","v":41},{"$f":"IF( CalcFinBal= TRUE,IF(ABS(UnsegTot-D164)>0.005,1,0),0)","v":0}]
[17,"Administration expenses must be supplied to calculate final balances",null,null,null,null,null,null,null,null,{"$f":"ROW(Admin)","v":42},{"$f":"IF(AND(CalcFinBal=TRUE,Admin=0),1,0)","v":0}]
[18,"Income must be supplied to calculate final balances",null,null,null,null,null,null,null,null,{"$f":"ROW(Franked)","v":43},{"$f":"IF(AND(CalcFinBal=TRUE,SUM(Franked,Foreign,OtherIncome)=0),1,0)","v":0}]
[19,"Full tax must be supplied to calculate final balances",null,null,null,null,null,null,null,null,{"$f":"ROW(FullTax)","v":48},{"$f":"IF(AND(CalcFinBal=TRUE,SUM(FullForeign,FullTax)=0),1,0)","v":0}]
[20,"Accounts must commence after certificate date",null,null,null,null,null,null,null,null,{"$f":"ROW(IndvlAccComm)","v":70},{"$f":"P70","v":0}]
[21,"Pensions must commence on or after account and before certificate date and at least one pension must be recorded",null,null,null,null,null,null,null,null,{"$f":"ROW(IndvlPensComm)","v":71},1]
[22,"Beginning balances of all accounts must not be negative",null,null,null,null,null,null,null,null,{"$f":"ROW(P72)","v":72},0]
[23,"Non concessional contribution total must reconcile to inwards cashflows and other totals",null,null,null,null,null,null,null,null,73,0]
[24,"Concessional contribution total must reconcile to inwards cashflows and other totals",null,null,null,null,null,null,null,null,74,0]
[25,"Transfer in total must reconcile to inwards cashflows and other totals",null,null,null,null,null,null,null,null,75,0]
[26,"Insurance premium total must reconcile to outwards cashflows and other totals",null,null,null,null,null,null,null,null,76,0]
[27,"Insurance claim total must reconcile to inwards cashflows and other totals",null,null,null,null,null,null,null,null,77,0]
[28,"Pensions and transfer out total must reconcile to outwards cashflows",null,null,null,null,null,null,null,null,78,0]
[29,"If closing balances are not to be calculated they must not be zero or negative",null,null,null,null,null,null,null,null,{"$f":"ROW(B79)","v":79},{"$f":"IF(B79=\"\", 0, 1)","v":0}]
[30,"Size of transactions array must be at least four rows",null,null,null,null,null,null,null,null,{"$f":"ROW(A85)","v":85},{"$f":"IF(ROWS(IndvlDates)<4,1,0)","v":0}]
[31,"One or more transation dates before financial year",null,null,null,null,null,null,null,null,{"$f":"MAX(IF(IndvlDates<YrStart,ROW(IndvlDates),0))","v":100},{"$f":"SUM(IF(IndvlDates<YrStart,1,0))","v":15}]
[32,"One or more transation dates after financial year",null,null,null,null,null,null,null,null,{"$f":"MAX(IF(IndvlDates>CertDate,ROW(IndvlDates),0))","v":0},{"$f":"SUM(IF(IndvlDates>CertDate,1,0))","v":0}]
[33,"Inward cash cannot occur after pension start.  Need a new accumulation or pension account to deal with this",null,null,null,null,null,null,null,null,{"$f":"ROW(IndvlInwardcashAfterPension)","v":105},{"$f":"SUM(IF(IndvlInwardcashAfterPension>0,1,0))","v":0}]
[34,"Average pension exceeds average all",null,null,null,null,null,null,null,null,{"$f":"ROW(P139)","v":139},{"$f":"P139","v":0}]
[35,"Average non pension exceeds average all",null,null,null,null,null,null,null,null,{"$f":"ROW(P140)","v":140},{"$f":"P140","v":0}]
[36,{"$f":"A152 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,{"$f":"ROW(P152)","v":152},{"$f":"P152","v":0}]
[37,{"$f":"A153 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,{"$f":"ROW(P153)","v":153},{"$f":"P153","v":0}]
[38,{"$f":"A154 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,{"$f":"ROW(P154)","v":154},{"$f":"P154","v":0}]
[39,{"$f":"A156 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,{"$f":"ROW(P156)","v":156},{"$f":"P156","v":0}]
[40,{"$f":"A157 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,{"$f":"ROW(P157)","v":157},{"$f":"P157","v":0}]
[41,{"$f":"A158 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,{"$f":"ROW(P158)","v":158},0]
[42,"Calculation not required by client not reconciled to constituent elements",null,null,null,null,null,null,null,null,159,0]
[43,"Calculation not required by client not reconciled to constituent elements",null,null,null,null,null,null,null,null,160,0]
[44,"Calculation not required by client not reconciled to constituent elements",null,null,null,null,null,null,null,null,161,0]
[45,"Calculation not required by client not reconciled to constituent elements",null,null,null,null,null,null,null,null,162,0]
[46,"Calculation not required by client not reconciled to constituent elements",null,null,null,null,null,null,null,null,163,0]
[47,{"$f":"A$164 & \" not reconciled to constituent elements\"","v":"Calculation not required by client not reconciled to constituent elements"},null,null,null,null,null,null,null,null,164,0]
[48,"Instructor has not accepted responsibilty for fee",null,null,null,null,null,null,null,null,{"$f":"ROW(ConfFee)","v":126},{"$f":"IF(ConfFee<>TRUE,1,0)","v":1}]
[49,"Instructor has not confirmed RP",null,null,null,null,null,null,null,null,{"$f":"ROW(ConfRP)","v":63},{"$f":"IF(ConfRP<>TRUE,1,0)","v":1}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(ErrorOccurence)","v":28}]
[]
[]
[]
[]
[]
[]
["https://www.ato.gov.au/Super/Self-managed-super-funds/In-detail/SMSF-resources/SMSF-technical/Exempt-current-pension-income/?=redirected&page=3&anchor=Actuarial_certificate_requirements#Actuarial_certificate_requirements"]

{"sheet":"InstructionsAndHelp","index":1,"n_rows":37,"n_cols":9,"rows_kept":37,"cut":false,"hidden":false,"merged":[[5,8,5,14]]}
[]
[null,"Barton Consultancy"]
[]
["Client Assisted Account Based Pension Input Sheet"]
[]
["Instructions and Help",null,null,null,null,null,null,null,"Return to input worksheet"]
[]
["Copyright and conditions of use"]
["Copyright 2010 Processing and Programming Pty Ltd (08 9225 5899). "]
["Barton Consultancy is licensed to use this workbook and to provide it to its clients for use solely for data collection for Barton Consultancy"]
["to produce reports or certificates for users."]
["No responsibility is taken for any loss or damage occassioned by the use of any results evident on this workbook without certification by  "]
["Barton Consultancy Pty Ltd."]
["Users may use this workbook on behalf of their clients, but the user is responsible for fees charged by  Barton Consultancy Pty Ltd."]
[]
["Required computer settings"]
["The buttons on this worksheet will only work if macros are permitted to operate. If this is not the case, the workbook can still be used,"]
[" but must be manually emailed to Barton Consultantcy Pty Ltd."]
["This workbook will generate errors if the \"Analysis ToolPak\" add in is not installed."]
["This is because the worksheet uses the YEARFRAC function that is included in the Analysis ToolPak."]
["To see how to instal the Analysis ToolPak, invoke help and search \"YEARFRAC\""]
["For automatic emailing to work, ensure that Microsoft Outlook Object Library and Microsoft Excel Object Library are invoked in the \"Tools References\" menu of Visual Basic"]
[]
["General operation"]
["Please populate the shaded areas and any red bordered areas ONLY."]
["Incorrect or unreconciled cells are displayed with a white background."]
["Strange, but possibly correct information is displayed in orange font."]
[{"$f":"\"To change the number of accounts, invoke the button near at row \" & ROW(IndvlAccountHeader) &\".\"","v":"To change the number of accounts, invoke the button near at row 66."}]
[{"$f":"\"Any errors detected are identified at row \" & ROW(CurrErrRow) &\".\"","v":"Any errors detected are identified at row 114."}]
[{"$f":"\"Please view the results currently commmencing at row \" & ROW(ResultsAll) &\".\"","v":"Please view the results currently commmencing at row 120."}]
[{"$f":"\"Please accept responsibility for the fee currently at row \" & ROW(ConfFee) &\".\"","v":"Please accept responsibility for the fee currently at row 126."}]
[{"$f":"\"Please use the subjacent button \" & CHAR(34) & \"Send Workbook\" & CHAR(34) & \" to email the completed workbook to Barton Consultancy Pty Ltd\"","v":"Please use the subjacent button \"Send Workbook\" to email the completed workbook to Barton Consultancy Pty Ltd"}]
["for production of the necessary certificate."]
["For help, please telephone 08 9225 5899."]
[]
["Number of individual accounts"]
[{"$f":"\"To make room for fewer or more accounts, invoke the link 'Invoke menu to change number of accounts' at row \" &ROW(Input!B65) & \" on Input sheet\"","v":"To make room for fewer or more accounts, invoke the link 'Invoke menu to change number of accounts' at row 65 on Input sheet"}]