{"text_format":"1","workbook":"xlsx","n_sheets":17,"sheets":["Data Input","Subscrptn Split","Asset Adj","Implicit Rate info","How to Enable Macros","Example - PV Calc","SBITA 1 - PV Calc","SBITA 2 - PV Calc","SBITA 3 - PV Calc","SBITA 4 - PV Calc","SBITA 5 - PV Calc","SBITA 6 - PV Calc","SBITA 7 - PV Calc","SBITA 8 - PV Calc","SBITA 9 - PV Calc","SBITA 10 - PV Calc","Drop Down List"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":1,"defined_names":40,"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":"Data Input","index":0,"n_rows":245,"n_cols":12,"rows_kept":245,"cut":false,"hidden":false,"merged":[[198,10,199,10],[198,11,199,11],[207,0,208,0],[207,1,209,1],[207,2,209,2],[198,5,199,5],[198,6,199,6],[198,7,199,7],[198,8,199,8],[198,9,199,9],[198,0,199,0],[198,1,199,1],[198,2,199,2],[198,3,199,3],[198,4,199,4],[207,3,209,3],[207,4,209,4],[207,5,209,5],[207,6,209,6],[207,7,209,7],[207,8,209,8],[207,9,209,9],[207,10,209,10],[207,11,209,11],[170,7,172,7],[178,10,185,10],[178,11,185,11],[181,0,182,0],[188,0,190,0],[191,0,195,0],[188,1,195,1],[188,2,195,2],[188,3,195,3],[188,4,195,4],[188,5,195,5],[188,6,195,6],[188,7,195,7],[188,8,195,8],[188,9,195,9],[188,10,195,10],[188,11,195,11],[178,5,185,5],[178,6,185,6],[178,7,185,7],[178,8,185,8],[178,9,185,9],[178,1,185,1],[184,0,185,0],[156,5,160,5],[178,3,185,3],[178,4,185,4],[170,1,172,1],[170,2,172,2],[170,3,172,3],[170,4,172,4],[170,5,172,5],[170,6,172,6],[178,2,185,2],[141,9,142,9],[170,8,172,8],[170,9,172,9],[170,10,172,10],[170,11,172,11],[170,0,171,0],[156,8,160,8],[156,9,160,9],[156,10,160,10],[156,11,160,11],[164,0,165,0],[163,1,166,1],[163,2,166,2],[163,3,166,3],[163,4,166,4],[163,5,166,5],[163,6,166,6],[163,7,166,7],[163,8,166,8],[163,9,166,9],[163,10,166,10],[163,11,166,11],[156,3,160,3],[156,4,160,4],[131,9,133,9],[156,6,160,6],[156,7,160,7],[158,0,160,0],[156,1,160,1],[156,2,160,2],[141,10,142,10],[141,11,142,11],[150,0,152,0],[149,1,152,1],[149,2,152,2],[149,3,152,3],[149,4,152,4],[149,5,152,5],[149,6,152,6],[149,7,152,7],[149,8,152,8],[149,9,152,9],[149,10,152,10],[149,11,152,11],[141,5,142,5],[141,6,142,6],[141,7,142,7],[141,8,142,8],[119,8,121,8],[141,0,142,0],[141,1,142,1],[141,2,142,2],[141,3,142,3],[141,4,142,4],[131,10,133,10],[131,11,133,11],[136,0,137,0],[136,1,137,1],[136,2,137,2],[136,3,137,3],[136,4,137,4],[136,5,137,5],[136,6,137,6],[136,7,137,7],[136,8,137,8],[136,9,137,9],[136,10,137,10],[136,11,137,11],[131,5,133,5],[131,6,133,6],[131,7,133,7],[131,8,133,8],[105,7,112,7],[131,0,132,0],[131,1,133,1],[131,2,133,2],[131,3,133,3],[131,4,133,4],[119,9,121,9],[119,10,121,10],[119,11,121,11],[123,1,124,1],[123,2,124,2],[123,3,124,3],[123,4,124,4],[123,5,124,5],[123,6,124,6],[123,7,124,7],[123,8,124,8],[123,9,124,9],[123,10,124,10],[123,11,124,11],[119,4,121,4],[119,5,121,5],[119,6,121,6],[119,7,121,7],[51,2,52,2],[123,0,124,0],[119,0,121,0],[119,1,121,1],[119,2,121,2],[119,3,121,3],[105,9,112,9],[105,10,112,10],[105,11,112,11],[114,0,116,0],[113,1,116,1],[113,2,116,2],[113,3,116,3],[113,4,116,4],[113,5,116,5],[113,6,116,6],[113,7,116,7],[113,8,116,8],[113,9,116,9],[113,10,116,10],[113,11,116,11],[105,4,112,4],[105,5,112,5],[105,6,112,6],[55,8,56,8],[105,8,112,8],[108,0,109,0],[110,0,111,0],[105,1,112,1],[105,2,112,2],[105,3,112,3],[0,0,0,1],[1,0,1,1],[2,0,2,1],[3,0,3,1],[4,0,4,1],[9,0,10,0],[9,1,15,1],[18,0,19,0],[20,0,21,0],[22,0,24,0],[25,0,26,0],[27,0,28,0],[27,1,28,1],[30,0,31,0],[32,0,33,0],[34,0,35,0],[39,0,40,0],[64,10,65,10],[55,9,56,9],[55,10,56,10],[55,11,56,11],[58,0,59,0],[58,1,59,1],[58,2,59,2],[58,3,59,3],[58,4,59,4],[58,5,59,5],[58,6,59,6],[58,7,59,7],[58,8,59,8],[58,9,59,9],[58,10,59,10],[58,11,59,11],[55,0,56,0],[55,1,56,1],[55,2,56,2],[55,3,56,3],[55,4,56,4],[55,5,56,5],[55,6,56,6],[55,7,56,7],[71,4,72,4],[64,11,65,11],[67,0,68,0],[67,1,68,1],[67,2,68,2],[67,3,68,3],[67,4,68,4],[67,5,68,5],[67,6,68,6],[67,7,68,7],[67,8,68,8],[67,9,68,9],[67,10,68,10],[67,11,68,11],[64,5,65,5],[64,6,65,6],[64,7,65,7],[64,8,65,8],[64,9,65,9],[64,0,65,0],[64,1,65,1],[64,2,65,2],[64,3,65,3],[64,4,65,4],[71,10,72,10],[71,11,72,11],[73,0,74,0],[93,0,94,0],[89,1,95,1],[89,2,95,2],[89,3,95,3],[89,4,95,4],[89,5,95,5],[89,6,95,6],[89,7,95,7],[89,8,95,8],[89,9,95,9],[89,10,95,10],[89,11,95,11],[71,5,72,5],[71,6,72,6],[71,7,72,7],[71,8,72,8],[71,9,72,9],[71,0,72,0],[71,1,72,1],[71,2,72,2],[71,3,72,3],[212,10,215,10],[212,11,215,11],[218,0,219,0],[217,1,219,1],[217,2,219,2],[217,3,219,3],[217,4,219,4],[217,5,219,5],[217,6,219,6],[217,7,219,7],[217,8,219,8],[217,9,219,9],[217,10,219,10],[217,11,219,11],[212,1,215,1],[212,2,215,2],[212,3,215,3],[212,4,215,4],[212,5,215,5],[212,6,215,6],[212,7,215,7],[212,8,215,8],[212,9,215,9],[224,0,225,0],[226,0,227,0],[228,0,229,0],[228,1,229,1],[228,2,229,2],[228,3,229,3],[228,4,229,4],[228,5,229,5],[228,6,229,6],[228,7,229,7],[228,8,229,8],[228,9,229,9],[228,10,229,10],[228,11,229,11],[226,1,227,1],[226,2,227,2],[226,3,227,3],[226,4,227,4],[226,5,227,5],[226,6,227,6],[226,7,227,7],[226,8,227,8],[226,9,227,9],[226,10,227,10],[226,11,227,11],[242,9,244,9],[242,10,244,10],[242,11,244,11],[242,0,243,0],[242,1,244,1],[242,2,244,2],[242,3,244,3],[242,4,244,4],[242,5,244,5],[242,6,244,6],[242,7,244,7],[242,8,244,8]]}
["STATE OF ARIZONA"]
["CLOSING PACKAGE"]
["O - GASB 96 SBITAs - Form 80a"]
["Collection Form for FY2024 SBITAs"]
["(Subscription-Based Information Technology Arrangements)"]
[" ----- Due Date 8/22/2025 -----"]
["This FILLABLE form is designed to capture your agency's GASB 96 SBITA information."]
[]
["Reporting background:   "]
["Please complete this form for every SBITA contract that qualifies as a GASB 96 SBITA AND has a start date during FY2025\n(July 1, 2024 - June 30, 2025).","Closing Package Section O:  Form 80 - SBITA Decision Form"]
[]
["For example:"]
["- Meets scope requirements and is not listed as specifically EXCLUDED,"]
["- Total SBITA payments + capitalizable implementation costs are greater than the Materiality Threshold of $5,000,000, and"]
["- Has a Maximum Possible Subscription Term of more than 12 months."]
["You should have completed the \"SBITA Decision Form\" first to verify qualifications (Closing Package Section O - link at right):"]
[]
["STEPS:"]
["1.  Collect the SBITA Contracts and other information for all qualifying SBITAs identified on Decision Form and use that\n      information to fill out this Collection Form."]
[]
["2.  Save a copy of this Collection Form and rename with your Agency's 3 letter ID.  For example -\n     \"ADA FY2025 SBITA Collection Form\""]
[]
["3.  Complete the following sections by entering in the applicable information for each subscription within a separate column\n      (starting with column C).  There are 10 columns provided.  If you have more than 10 SBITAs, duplicate the form & label\n      as needed (1 of 2, 2 of 2, or 1 of 3, 2 of 3, 3 of 3, etc)."]
[]
[]
["4.  Maintain all documentation and supporting workpapers to support your conclusion for audit purposes in a permanent\n      file (i.e. SBITA Contracts, Decision Form, and this SBITA Collection Form)."]
[]
["5.  Return a copy of this SBITA Collection Form along with the related SBITA contracts and pricing spreadsheets (if separate)\n      to the ACFR e-mail address at right by the Due Date listed above.","ACFR@azdoa.gov "]
[]
[]
["Note 1 - The Teal highlighted cells are for manual or drop-down input.  The Orange highlighted cells contain formulas and will automatically fill.  Please do not change the Orange cells."]
[]
["Note 2 - Do not insert new columns or rows.  As noted above, if you have more than 10 SBITAs, duplicate the form instead of inserting new columns."]
[]
["Note 3 - Several sections below mention the criteria, 'Reasonably Certain'. To be reasonably certain means that there is a greater than probable chance an event will take place."]
[]
[]
["The Decision Form includes an overview of GASB 96.  The full text of GASB Statement 96 can be found here:","GASB 96 - SBITAs"]
[]
["For additional questions on any of the above steps (including filling out this form), please send questions to the ACFR e-mail address above."]
[]
[]
["I.  Agency Information"]
["(Only need to fill out this section once for entire worksheet)"]
["Agency three letter ID in AZ360 (Example: Arizona Department of Administration = ADA):"]
["Preparer Name:"]
["Preparer E-mail:"]
["Preparer Phone:"]
["Date Completed:"]
["Approved by:"]
[]
["How many qualifying GASB 96 SBITAs were identified on the SBITA Decision Form?"]
["If more than 10, the agency will need to fill out multiple forms (label them 1 of 2, 2 of 2,  or 1 of 3, 2 of 3, 3 of 3, etc)"]
[]
["II.  General Information","Example (complex)","SBITA # 1","SBITA # 2","SBITA # 3","SBITA # 4","SBITA # 5","SBITA # 6","SBITA # 7","SBITA # 8","SBITA # 9","SBITA # 10"]
["1a.  Contract # if specific to this SBITA (If the contract is a Statewide Contract used for multiple SBITAs, please create your\n        own rational numbering system, i.e. agency code, 2 digit FY and a 2 digit #)","ADA2501"]
[]
[]
["1b.  If using a Statewide Contract used for multiple SBITAs, please provide the Statewide Contract # here\n        (N/A if not applicable)","CTR124578"]
[]
[]
["2.  Name of the SBITA Vendor (Vendor where payments are sent)","A+ Software & Hosting, Inc"]
[]
["3.  Name &/or Short Description of Software Subscription or Other IT-related Arrangement"]
["Please provide name of software and/or a short description of any other related cloud infrastructure access (i.e. vendor server, cloud-based computing such as SaaS, PaaS or IaaS, etc)","A+ Hosting"]
[]
[]
["4.  Is this SBITA a NEW SBITA (entered into between 7/1/2024 - 6/30/2025) or an Existing SBITA which previously did not meet\n      the reporting threshold but now qualifies due to a Significant Modification?","New (FY2025)"]
[]
[]
["5.  Is the entire Subscription Payment paid from One Source of funding or Divided between Funds or Agencies?","Divided - Fill out Subscrptn Split Tab"]
["If the payment is divided, then please switch to the \"Subscrptn Split\" Tab to fill out the payment split info (click on hyperlink to the right for correct corresponding SBITA # column).","Example - Subscrptn Split","SBITA # 1 - Subscrptn Split","SBITA # 2 - Subscrptn Split","SBITA # 3 - Subscrptn Split","SBITA # 4 - Subscrptn Split","SBITA # 5 - Subscrptn Split","SBITA # 6 - Subscrptn Split","SBITA # 7 - Subscrptn Split","SBITA # 8 - Subscrptn Split","SBITA # 9 - Subscrptn Split","SBITA # 10 - Subscrptn Split"]
[]
["If the funding is all from One Source, please provide the following Chart of Accounts (COA) Elements below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each SBITA."]
[]
["Agency *","see Subscription Split Tab"]
["Fund *"]
["Object *"]
["Appropriation"]
["Unit"]
["Task"]
["Sub Task"]
["Program"]
["Phase"]
["Program Period"]
["Dept Object"]
[]
["III.  Asset Information"]
["6.  Identify Asset type (Software or Other IT Arrangement)"]
["All SBITAs will be capitalized as a \"SBITA\", but useful life calculations require a little deeper classification.","Other IT Arrangement"]
["- A subscription for \"Software\" could be identified in a contract as \"Software\" or as a \"SaaS\" (Software as a Service)"]
["- Software useful life is based on cost more than (>) or less than (<) $10 million."]
["- The total SBITA cost should include both the Implementation Costs as well as total Subscription Costs."]
["- A subscription for an \"Other IT Arrangement\" could include Hosting, Data Usage, or other activities identified in a\n   contract (i.e. PaaS (Platform as a Service) or IaaS (Infrastructure as a Service) arrangements)."]
[]
["- Other IT Arrangements do not have a defined useful life and will therefore use the SBITA Term for useful life."]
[]
["Useful life"]
["7.  Useful life per Capital Asset conventions for the State (will automatically fill)",{"$f":"VLOOKUP(B90,'Drop Down List'!$A3:$B5,2,FALSE)","v":"Use SBITA term"},{"$f":"VLOOKUP(C90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(D90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(E90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(F90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(G90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(H90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(I90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(J90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(K90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(L90,'Drop Down List'!$A3:$B5,2,FALSE)","v":{"$error":"#N/A"}}]
[]
["8.  Total (Remaining) Subscription Term Term per #18 (will automatically fill once completed below)",{"$f":"B147","v":84},{"$f":"C147","v":1},1,{"$f":"E147","v":1},1,1,1,1,1,1,1]
[]
["9.  Useful Life for SBITA Asset based on answers above (will automatically fill)",{"$f":"IF(B99<B101,B99,B101)","v":84},{"$f":"IF(C99<C101,C99,C101)","v":{"$error":"#N/A"}},{"$error":"#N/A"},{"$f":"IF(E99<E101,E99,E101)","v":{"$error":"#N/A"}},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"}]
[]
["10.  Are there any adjustments for measurement of the SBITA Asset?"]
["Per GASB 96, the SBITA Asset cost = ","Yes - Fill out on Asset Adj Tab"]
["+ Initial SBITA Liability (calculated in PV calc tab)"]
["Plus/Minus Asset Adjustments:"]
["- Incentives from SBITA Vendor received at or before Go-Live Date (i.e. to buy out a preexisting obligation or reimburse\n   certain implementation costs)"]
[]
["+ Payments made to SBITA Vendor at or before Go-Live Date (i.e. downpayment or subscription payments before\n    Go-Live Date)"]
[]
["+ Capitalizable Implementation Costs (includes staff time, consultation costs and any other costs related to implementation)"]
["If Yes, then please read Asset Adjustment instructions carefully in Asset Adj tab and fill out within the corresponding column:","Example - Asset Adj","SBITA # 1 - Asset Adj","SBITA # 2 - Asset Adj","SBITA # 3 - Asset Adj","SBITA # 4 - Asset Adj","SBITA # 5 - Asset Adj","SBITA # 6 - Asset Adj","SBITA # 7 - Asset Adj","SBITA # 8 - Asset Adj","SBITA # 9 - Asset Adj","SBITA # 10 - Asset Adj"]
["Note:  If SBITA previously did not meet the materiality threshold but now qualifies due to a Significant Modification AND that Significant Modification includes Implementation Costs for an Upgrade or additional functionality (new Module), will need to fill out the Asset Adj tab for those Capitalizable Implementation Costs."]
[]
[]
[]
["11.  Software capitalized in AZ360?"]
["Because the calculation for the SBITA Asset includes the Subscripton Term, Present Value of the Liability AND Capitalizable Implementation Costs, this calculation will be performed manually by the GAAP Group outside of AZ360 and should NOT be entered into AZ360.  If this was done in error, please indicate \"Yes\" here.","No"]
[]
[]
[]
["12.  If the above question is answered Yes, please indicate the Asset # in AZ360 (You will be contacted by the GAAP Group with\n        instructions on how to correct this).","N/A"]
[]
[]
["IV.  SBITA Term (Subscription and Implementation)"]
["13.  Contract Start Date"]
["Enter the Effective Contract Start Date.  This may or may not be the same as the signed date.","2024-09-01"]
[]
["14.  Date Put Into Service/Go-Live Date (may be different than above)"]
["- If there will be a period needed to implement the software or other IT-related subscription, enter the Date the Software\n   was/will be Put Into Service, or Go-Live Date (if still in the implementation phase, enter the EXPECTED Go-Live Date).","2025-09-01"]
[]
["- If put into service immediately, then enter the Contract Start Date."]
[]
["15.  Contracted Subscription Term End Date"]
["Enter the date the contracted SBITA term ends, unless the agency is Reasonably Certain to exercise a Termination Clause, in which case, enter the date the agency plans to terminate the subscription.","2030-08-31"]
[]
["Subscription Term in months (will automatically fill) - HIDE THIS LINE IN FINAL VERSION",{"$f":"ROUND(DATEDIF(B132,B137,\"M\"),0)+1","v":60},{"$f":"ROUND(DATEDIF(C132,C137,\"M\"),0)+1","v":1},{"$f":"ROUND(DATEDIF(D132,D137,\"M\"),0)+1","v":1},1,1,1,1,1,1,1,1]
[]
["16.  Options to Extend Term"]
["If subscription term extensions are available in the contract, AND the agency is reasonably certain to exercise it, enter # of months available to extend the subscription.",24]
[]
[]
["17.  Subscription End Date including Extensions (will automatically fill).",{"$f":"EDATE(B137,B142)","v":"2032-08-31"},{"$f":"EDATE(C137,C142)","v":"00:00:00"},{"$f":"EDATE(D137,D142)","v":"00:00:00"},"00:00:00","00:00:00","00:00:00","00:00:00","00:00:00","00:00:00",{"$f":"EDATE(K137,K142)","v":"00:00:00"},"00:00:00"]
[]
["18.  Total Subscription Term including Extensions in Months (will automatically fill)",{"$f":"B139+B142","v":84},1,1,1,1,1,1,1,1,{"$f":"K139+K142","v":1},1]
[]
["19.  Frequency of Subscription Payments"]
["Enter how often subscription payments are made (Monthly, Quarterly, Semi-Annually, Annually).","Semi-annually"]
["- If payments are Less Often than Annually (e.g. every 5 years), choose Annually from the dropdown list here (frequency\n   must be at least Annually for Present Value Calculation to work).  In the PV Calc tab, there are special instructions (Step #1f)\n   and a space to describe the payment frequency (Step #1g) if Less Often than Annually."]
[]
[]
["# of pmts/yr - HIDE THIS LINE IN FINAL VERSION",{"$f":"IF(B150=\"Weekly\",52,IF(B150=\"Monthly\",12,IF(B150=\"Quarterly\",4,IF(B150=\"Semi-Annually\",2,1))))","v":2},1,{"$f":"IF(D150=\"Weekly\",52,IF(D150=\"Monthly\",12,IF(D150=\"Quarterly\",4,IF(D150=\"Semi-Annually\",2,1))))","v":1},1,1,1,1,1,1,1,1]
[]
["20.  How many payments from Date Put Into Service/Go-Live Date (#14) to Subscription End Date including Extensions (#17)?"]
["-  If payments are monthly, this answer will most likely be the same as the calculated number of months (#18).",14]
["-  Calculate the number of payments carefully if payments are not monthly."]
["-  If payments are Less Often than Annually, list the # of years of the lease term rather than the total # of payments (e.g. payment\n   frequency every 5 years with a 20 year term would have a total of 4 payments, but enter 20 here instead of 4).  Again, this is\n   necessary for the Present Value Calculation to work.  If this is the case, read PV Calc instructions (Steps #1f & 1g) carefully."]
[]
[]
[]
["21.  Was the first subscription payment made at the beginning or at the end of the first payment period?"]
["- The payment period is the length of time between payments - see frequency of payments in #19)?","Begin"]
["- For example, subscription began on 4/1 with monthly payments and first payment was due 4/30, then it would be the end\n   of the first payment period."]
[]
["- For a quarterly period, a payment due within the first 45 days of the quarter would be beginning of the period."]
["Coding for Formulas - Begin = 1, End = 0 - HIDE THIS LINE IN FINAL VERSION",{"$f":"IF(B164=\"Begin\",1,0)","v":1},{"$f":"IF(C165=\"Begin\",1,0)","v":0},{"$f":"IF(D165=\"Begin\",1,0)","v":0},0,0,0,0,0,0,0,0]
[]
["22.  When is the first subscription payment due AFTER the Go-Live Date?"]
["Per GASB 96, the Subscription Term does not begin until after the SBITA is put into service (Go-Live Date).  Therefore, the first payment for the SBITA Liability Present Value Calculation does not occur until AFTER Go-Live.","2024-09-01"]
[]
["Note:  Subscription Payments made BEFORE the Go-Live Date should be reported in the Asset Adj tab (#2)."]
[]
["V.  SBITA Liability"]
[]
["SBITA Payments - PLEASE SEPARATE PAYMENTS TO BE MADE BETWEEN #23, #24 & #25 as much as possible:"]
["23.  Fixed Subscription Payment - Enter FIRST Fixed Payment Amount here (first payment AFTER Go-Live Date)"]
["To calculate the Fixed Subscription Payment:",450000]
["a.  Start with the fixed subscription periodic payments stated in the contract."]
["b.  In addition, add \"Fixed In-Substance Fees\" to the above amount (see definition below under variable payments)."]
["c.  For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate),\n      the payment should be initially measured using the index or rate published as of the start of the subscription term."]
[]
["d.  EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 below)"]
["Because payments may not remain the same throughout the subscription term, fill out the Payment Schedule on the PV Calc tab.\nA space is provided (Step #1g) to describe how often payments increase, formulas, etc."]
[]
[]
["24.  Variable Usage Fees over and above Fixed In-Substance Fees (do NOT include variable fees on PV Calc tab)"]
["a.  Variable usage fees include any part of the fee calculated on a usage basis.  For example, fees calculated as a quantity of\n       users (cost per user) or total gigs of data used, etc.  Per user fees are very common and could eliminate a SBITA entirely (if\n       all fees are based on # of users, then there is no fixed cost), so please review pricing details very carefully.","$50 per user fee"]
[]
[]
["b.  \"Fixed in-Substance Fees\" are the Minimum Usage Costs of a variable usage fee and should be included in the fixed payment\n       portion above.  For Example, if a software subscription includes up to 5 gigs of data used for a set fee, but charges extra for\n       every additional gig, the minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that\n       minimum would be the variable fees.  The \"Fixed In-Substance Fee\" should be added to the fixed payments above, and the\n       variable fee would be described here as \"$XX/gig used over 5 gigs\".  Please include how the fee is determined."]
[]
[]
[]
[]
[]
["25.  Support or Maintenance Service Component of subscription agreement (do NOT include these service fees on PV Calc tab)"]
["a.  A service component of a subscription agreement could be, for example, IT Support, Maintenance, Helpline,\n      Troubleshooting, etc.  List amount of services periodic payment here (if it changes, list the first payment amount)",{"$f":"B179*0.025+1200","v":12450}]
[]
[]
["b.  Please briefly describe the additional charges.","Troubleshooting/Help Services through phone or chat"]
[]
["26.  Termination penalties:"]
["a.  Is there a termination penalty if the subscription is terminated before the end of the subscription term?","No"]
[]
["b.  If so, is the agency reasonably certain to terminate the subscription early?"]
["Answer Yes only if the agency believes that a fiscal funding clause or termination clause will be exercised and there will be a penalty assessed if it is.","N/A"]
[]
["If Yes, then enter the termination penalty on the PV Calc tab."]
[]
["27.  Subscription incentives (during subscription term):"]
["a.  Does the subscription agreement contain incentives that will occur AFTER the start of the subscription term?","No"]
["i.  For example, Abated or Free Subscription periods,"]
["ii.  Reductions in subscription payments,"]
["iii.  Bonus amounts paid from the SBITA Vendor to the State, etc?"]
[]
["b.  If Yes, please describe:","N/A"]
["If applicable, read instructions on PV Calc tab carefully on how to include subscription incentives in the Payment Schedule\n(Steps #1d & 1e)."]
[]
[]
["VI.  Interest Rate"]
["28.  Enter the interest rate for the SBITA using one of the three methods listed below:"]
["a.  If the interest rate is specifically identified in the contract (explicit rate), use this rate."]
["b.  If the explicit rate is unknown, there may be an implicit rate (please see tab, \"Implicit Rate info\" for additional information\n      and a place to enter the information to calculate it)."]
[]
["c.  If both the explicit interest rate and implicit rate are unknown, identify the interest rate in the table below using the\n     subscription term (total subscription term in months is copied here from #18 above for ease of reference).",{"$f":"B147","v":84},1,1,1,1,1,1,1,1,1,1]
[]
["For example: If initial contract term was 3 years (36 months) plus a 2-year (24 month) extension,  for a total of 5 years (60 months) use the Mid-Term rate below.",3.3606999999999998E-2]
[]
[]
["Short-Term in months = 0 - 59 months"]
["Mid-Term in months = 60 - 107 months"]
["Long-Term in months = 108 months or longer"]
[]
["The interest rates have been updated for"]
["FY2025 & will be reassesed each year."]
[]
["29.  Periodic Interest Rate - based on frequency of payments (will automatically fill)",{"$f":"ROUND(B229/B154,6)","v":1.6803999999999999E-2},0,0,0,0,0,0,0,0,0,0]
[]
["VII.  Calculate the SBITA Liability & Asset"]
["30.  Fill out the Subscription Payment Schedule on the \"PV Calc\" tab corresponding to the correct SBITA #."]
["The Present Value of the SBITA Liability, Amortization Table and SBITA Asset Cost will be calculated in each respective PV Calc tab.  These are calculated with macros, and are not required to be completed by the agencies.","Example - PV Calc","SBITA # 1 - PV Calc","SBITA # 2 - PV Calc","SBITA # 3 - PV Calc","SBITA # 4 - PV Calc","SBITA # 5 - PV Calc","SBITA # 6 - PV Calc","SBITA # 7 - PV Calc","SBITA # 8 - PV Calc","SBITA # 9 - PV Calc","SBITA # 10 - PV Calc"]
[]
["- At a minimum, however, the Subscription Payment Schedule must be completed."]

{"sheet":"Subscrptn Split","index":1,"n_rows":143,"n_cols":12,"rows_kept":143,"cut":false,"hidden":false,"merged":[[14,11,16,11],[14,0,15,0],[0,0,0,1],[1,0,1,1],[3,0,4,1],[5,0,7,1],[8,0,9,1],[14,6,16,6],[14,7,16,7],[14,8,16,8],[14,9,16,9],[14,10,16,10],[14,1,16,1],[14,2,16,2],[14,3,16,3],[14,4,16,4],[14,5,16,5],[100,0,101,0],[115,0,116,0],[130,0,131,0],[22,0,23,0],[37,0,38,0],[52,0,53,0],[67,0,68,0],[85,0,86,0]]}
["GASB 96 SBITA Collection Form - Subscription Split Info - for SBITAs paid from multiple sources"]
["If a SBITA has more than one funding source for the payments during the subscription term, please complete the information below. "]
[]
["Note 1 - The Teal highlighted cells are for manual or drop-down input. The Orange highlighted cells contain formulas and will automatically fill. Please do not change the Orange cells."]
[]
["Note 2 - If the Agency divides out the SBITA payment with a Cost Allocation Function (or divides it out to more than the 4 lines provided), then ONLY provide the breakout for the 3 REQUIRED COA Elements to reduce the number of lines needed to be reported.  The other COA elements may be important for agency or unit internal reporting, but is not necessary for reporting at the ACFR level."]
[]
[]
["Note 3 - Only provide split information for the Fixed Subscription amount to be paid (#23 on Data Input tab).  Exclude the COAs (if different) for any variable payments (#24) or payments for services, maintenance, etc (#25) that are identified separately within the SBITA contract."]
[]
[]
["Use the SAME COLUMN here as was used in the \"Data Input\" tab.","Example","SBITA # 1","SBITA # 2","SBITA # 3","SBITA # 4","SBITA # 5","SBITA # 6","SBITA # 7","SBITA # 8","SBITA # 9","SBITA # 10"]
["Contract # (#1a from Data Input tab)",{"$f":"'Data Input'!B56","v":"ADA2501"},{"$f":"'Data Input'!C56","v":0},{"$f":"'Data Input'!D56","v":0},{"$f":"'Data Input'!E56","v":0},{"$f":"'Data Input'!F56","v":0},{"$f":"'Data Input'!G56","v":0},{"$f":"'Data Input'!H56","v":0},{"$f":"'Data Input'!I56","v":0},{"$f":"'Data Input'!J56","v":0},{"$f":"'Data Input'!K56","v":0},{"$f":"'Data Input'!L56","v":0}]
[]
["Is the subscription payment divided by a Percentage (%), or a Specific Dollar ($) Amount (i.e. 50% paid by one Source, and 50% paid by another Source; or first $1,000 paid by a Grant, the remainder paid by another Source)?","Percentage (%)"]
[]
["Based on your answer, please fill out the proper section below (Percentage or Dollar Amount Split)"]
[]
["Percentage (%) Split:"]
["How many Sources are there (how many funding lines in your SBITA payment)?",2]
[]
["1.  Percentage of funding from First Source",0.7]
["Please provide COA for First Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["1a.  Agency *","ADA"]
["1b.  Fund *","AD1234"]
["1c.  Object *",7160]
["1d.  Appropriation"]
["1e.  Unit"]
["1f.  Task"]
["1g.  Sub Task"]
["1h.  Program"]
["1i.  Phase"]
["1j.  Program Period"]
["1k.  Dept Object"]
[]
["2.  Percentage of funding from Second Source",0.3]
["Please provide COA for Second Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["2a.  Agency *","ADA"]
["2b.  Fund *",1000]
["2c.  Object *",7160]
["2d.  Appropriation"]
["2e.  Unit "]
["2f.  Task "]
["2g.  Sub Task"]
["2h.  Program"]
["2i.  Phase"]
["2j.  Program Period"]
["2k.  Dept Object"]
[]
["3.  Percentage of funding from Third Source"]
["Please provide COA for Third Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["3a.  Agency *"]
["3b.  Fund *"]
["3c.  Object *"]
["3d.  Appropriation"]
["3e.  Unit "]
["3f.  Task "]
["3g.  Sub Task"]
["3h.  Program"]
["3i.  Phase"]
["3j.  Program Period"]
["3k.  Dept Object"]
[]
["4.  Percentage of funding from Fourth Source"]
["Please provide COA for Fourth Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["4a.  Agency *"]
["4b.  Fund *"]
["4c.  Object *"]
["4d.  Appropriation"]
["4e.  Unit "]
["4f.  Task "]
["4g.  Sub Task"]
["4h.  Program"]
["4i.  Phase"]
["4j.  Program Period"]
["4k.  Dept Object"]
[]
["Specific Dollar ($) Amount Split:"]
["How many Sources are there (how many funding lines in your SBITA payment)?"]
[]
["1.  Amount of funding from First Source"]
["Please provide COA for First Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["1a.  Agency *"]
["1b.  Fund *"]
["1c.  Object *"]
["1d.  Appropriation"]
["1e.  Unit"]
["1f.  Task"]
["1g.  Sub Task"]
["1h.  Program"]
["1i.  Phase"]
["1j.  Program Period"]
["1k.  Dept Object"]
[]
["2.  Amount of funding from Second Source"]
["Please provide COA for Second Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["2a.  Agency *"]
["2b.  Fund *"]
["2c.  Object *"]
["2d.  Appropriation"]
["2e.  Unit "]
["2f.  Task "]
["2g.  Sub Task"]
["2h.  Program"]
["2i.  Phase"]
["2j.  Program Period"]
["2k.  Dept Object"]
[]
["3.  Amount of funding from Third Source"]
["Please provide COA for Third Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["3a.  Agency *"]
["3b.  Fund *"]
["3c.  Object *"]
["3d.  Appropriation"]
["3e.  Unit "]
["3f.  Task "]
["3g.  Sub Task"]
["3h.  Program"]
["3i.  Phase"]
["3j.  Program Period"]
["3k.  Dept Object"]
[]
["4.  Amount of funding from Fourth Source"]
["Please provide COA for Fourth Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
[]
["4a.  Agency *"]
["4b.  Fund *"]
["4c.  Object *"]
["4d.  Appropriation"]
["4e.  Unit "]
["4f.  Task "]
["4g.  Sub Task"]
["4h.  Program"]
["4i.  Phase"]
["4j.  Program Period"]
["4k.  Dept Object"]

{"sheet":"Asset Adj","index":2,"n_rows":183,"n_cols":12,"rows_kept":183,"cut":false,"hidden":false,"merged":[[47,3,48,3],[47,4,48,4],[47,10,48,10],[47,11,48,11],[47,5,48,5],[47,6,48,6],[47,7,48,7],[47,8,48,8],[47,9,48,9],[7,5,11,5],[7,6,11,6],[7,7,11,7],[7,8,11,8],[7,9,11,9],[7,10,11,10],[7,11,11,11],[30,1,31,1],[32,0,33,0],[32,1,33,1],[54,1,55,1],[54,2,55,2],[54,3,55,3],[54,4,55,4],[0,0,0,1],[1,0,1,1],[35,0,35,1],[36,0,36,1],[10,0,11,0],[6,0,7,0],[8,0,9,0],[26,1,27,1],[26,0,27,0],[28,0,29,0],[28,1,29,1],[30,0,31,0],[7,2,11,2],[7,3,11,3],[7,4,11,4],[39,0,39,1],[37,0,38,1],[47,0,48,0],[47,1,48,1],[47,2,48,2],[60,2,61,2],[60,3,61,3],[60,4,61,4],[60,5,61,5],[54,10,55,10],[54,11,55,11],[57,0,58,0],[57,1,58,1],[57,2,58,2],[57,3,58,3],[57,4,58,4],[57,5,58,5],[57,6,58,6],[57,7,58,7],[57,8,58,8],[57,9,58,9],[57,10,58,10],[57,11,58,11],[54,5,55,5],[54,6,55,6],[54,7,55,7],[54,8,55,8],[54,9,55,9],[54,0,55,0],[65,0,67,0],[82,0,83,0],[84,0,85,0],[84,1,85,1],[84,2,85,2],[60,11,61,11],[62,0,63,0],[62,1,63,1],[62,2,63,2],[62,3,63,3],[62,4,63,4],[62,5,63,5],[62,6,63,6],[62,7,63,7],[62,8,63,8],[62,9,63,9],[62,10,63,10],[62,11,63,11],[60,6,61,6],[60,7,61,7],[60,8,61,8],[60,9,61,9],[60,10,61,10],[60,1,61,1],[84,8,85,8],[84,9,85,9],[84,10,85,10],[84,11,85,11],[89,1,90,1],[89,2,90,2],[89,3,90,3],[89,4,90,4],[89,5,90,5],[89,6,90,6],[89,7,90,7],[89,8,90,8],[89,9,90,9],[89,10,90,10],[89,11,90,11],[84,3,85,3],[84,4,85,4],[84,5,85,5],[84,6,85,6],[84,7,85,7],[114,0,115,0],[114,1,115,1],[114,2,115,2],[114,3,115,3],[114,4,115,4],[91,10,92,10],[91,11,92,11],[94,0,95,0],[108,0,109,0],[110,0,113,0],[91,5,92,5],[91,6,92,6],[91,7,92,7],[91,8,92,8],[91,9,92,9],[91,0,92,0],[91,1,92,1],[91,2,92,2],[91,3,92,3],[91,4,92,4],[121,4,122,4],[114,10,115,10],[114,11,115,11],[119,1,120,1],[119,2,120,2],[119,3,120,3],[119,4,120,4],[119,5,120,5],[119,6,120,6],[119,7,120,7],[119,8,120,8],[119,9,120,9],[119,10,120,10],[119,11,120,11],[114,5,115,5],[114,6,115,6],[114,7,115,7],[114,8,115,8],[114,9,115,9],[140,4,142,4],[121,10,122,10],[121,11,122,11],[124,0,125,0],[128,1,129,1],[128,2,129,2],[128,3,129,3],[128,4,129,4],[128,5,129,5],[128,6,129,6],[128,7,129,7],[128,8,129,8],[128,9,129,9],[128,10,129,10],[128,11,129,11],[121,5,122,5],[121,6,122,6],[121,7,122,7],[121,8,122,8],[121,9,122,9],[121,0,122,0],[121,1,122,1],[121,2,122,2],[121,3,122,3],[140,10,142,10],[140,11,142,11],[144,0,146,0],[144,1,146,1],[148,1,149,1],[144,2,146,2],[144,3,146,3],[144,4,146,4],[144,5,146,5],[144,6,146,6],[144,7,146,7],[144,8,146,8],[144,9,146,9],[144,10,146,10],[144,11,146,11],[140,5,142,5],[140,6,142,6],[140,7,142,7],[140,8,142,8],[140,9,142,9],[140,0,141,0],[140,1,142,1],[140,2,142,2],[140,3,142,3],[151,3,152,3],[151,4,152,4],[151,5,152,5],[148,7,149,7],[148,8,149,8],[148,9,149,9],[148,10,149,10],[148,11,149,11],[148,2,149,2],[148,3,149,3],[148,4,149,4],[148,5,149,5],[148,6,149,6],[156,0,159,0],[178,0,181,0],[182,0,184,0],[7,1,11,1],[151,11,152,11],[153,0,154,0],[153,1,154,1],[153,2,154,2],[153,3,154,3],[153,4,154,4],[153,5,154,5],[153,6,154,6],[153,7,154,7],[153,8,154,8],[153,9,154,9],[153,10,154,10],[153,11,154,11],[151,6,152,6],[151,7,152,7],[151,8,152,8],[151,9,152,9],[151,10,152,10],[151,1,152,1],[151,2,152,2]]}
["GASB 96 SBITA Collection Form - Asset Adjustments"]
["If a SBITA has additional costs or incentives at or before the In-Service (or Go-Live) Date, the Subscription Asset will need to be adjusted."]
[]
["Per GASB 96, the SBITA Asset cost ="]
["+ Initial SBITA Liability (calculated in PV calc tab)"]
["Plus/Minus Asset Adjustments:"]
["- Incentives from SBITA Vendor received at or before Go-Live Date (i.e. to buy out a preexisting obligation or\n   reimburse certain implementation costs)"]
[null,"Example - Asset Adj\nPlease fully read instructions to the left","SBITA # 1 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 2 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 3 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 4 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 5 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 6 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 7 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 8 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 9 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below","SBITA # 10 - Asset Adj\nPlease fully read instructions to the left before filling in requested information below"]
["+ Payments made to SBITA Vendor at or before Go-Live Date (i.e. downpayment or subscription payments\n    before Go-Live Date)"]
[]
["+ Capitalizable Implementation Costs (includes staff time, consultation costs and any other costs related to\n    implementation)"]
[]
[]
["There are 3 phases to software implementation, but not all phases are capitalizable:"]
["1.  Preliminary Project Stage","EXPENSE"]
["Includes - The conceptual formulation and evaluation of alternatives,"]
["- The determination of the existence of needed technology, and"]
["- The final selection of alternatives for the SBITA (including contract negotiations)"]
["2.  Initial Implementation Stage","CAPITALIZE"]
["Includes - Ancillary charges related to designing the chosen path (configuration, coding, testing & installation), and"]
["- Other charges necessary to place the subscription asset into service"]
["Types of costs that might occur in this stage -  Consulting fees from the SBITA Vendor &/or a third party consultant,"]
["- Internal Staff Time related to the activities described above"]
["This stage is complete when the SBITA Asset is placed into service (Go-Live Date)"]
["EXCLUSIONS from Capitalizable Implementation Costs:  TRAINING","EXPENSE"]
["Always expense training costs, whether during the initial implementation stage or operational stage"]
["3.  Operation & Additional Implementation Stage","Expense or Capitalize\n(see below)"]
[]
["a.  Maintenance, troubleshooting, & other activities associated with the government's ongoing access to the\n      underlying IT assets &/or SBITA asset.","EXPENSE"]
[]
["b.  Additional implementation activities such as costs related to additional Modules or Upgrades for the software \n      not yet in service or upgrades","CAPITALIZE"]
[]
["Note:  If possible, the State would prefer to track separate modules as separate SBITAs, but will add to the original asset if it is not possible to determine how to split the contract into those separate components.","Contact the GAAP Group to discuss if relavent"]
[]
[]
["Note 1 - The Teal highlighted cells are for manual or drop-down input. The Orange highlighted cells contain formulas and will automatically fill. Please do not change the Orange cells."]
["Note 2 - If there are any amounts reported on this tab, you MUST attach Documentation (i.e. reports from AZ360) totaling the adjustments reported below for the current fiscal year.  The backup should include the required COA elements listed below as well as the Date & FY of the transactions.\nIf not recorded in AZ360, may attach invoices from the vendor for additional costs, or evidence of incentives received if paid to a third party."]
["Note 3 - Object 8485 - SBITA Implementation (a Capital Outlay acct) has been created to track Capitalizable Implementatiom Costs paid to the SBITA Vendor or a 3rd party consultant."]
[]
["**Object 8485 - SBITA Implementation is suggested, but not required, for recording Capitalizable Implementation Costs for SBITAs**"]
[]
["Use the SAME COLUMN here as was used in the \"Data Input\" tab.","Example","SBITA # 1","SBITA # 2","SBITA # 3","SBITA # 4","SBITA # 5","SBITA # 6","SBITA # 7","SBITA # 8","SBITA # 9","SBITA # 10"]
["Contract # (#1a from Data Input tab)",{"$f":"'Data Input'!B56","v":"ADA2501"},{"$f":"'Data Input'!C56","v":0},{"$f":"'Data Input'!D56","v":0},{"$f":"'Data Input'!E56","v":0},{"$f":"'Data Input'!F56","v":0},{"$f":"'Data Input'!G56","v":0},{"$f":"'Data Input'!H56","v":0},{"$f":"'Data Input'!I56","v":0},{"$f":"'Data Input'!J56","v":0},{"$f":"'Data Input'!K56","v":0},{"$f":"'Data Input'!L56","v":0}]
[]
["Are there any adjustments for measurement of the SBITA Asset? (#10 from Data Input tab)",{"$f":"'Data Input'!B106","v":"Yes - Fill out on Asset Adj Tab"},{"$f":"'Data Input'!C106","v":0},{"$f":"'Data Input'!D106","v":0},{"$f":"'Data Input'!E106","v":0},{"$f":"'Data Input'!F106","v":0},{"$f":"'Data Input'!G106","v":0},{"$f":"'Data Input'!H106","v":0},{"$f":"'Data Input'!I106","v":0},{"$f":"'Data Input'!J106","v":0},{"$f":"'Data Input'!K106","v":0},{"$f":"'Data Input'!L106","v":0}]
[]
["Contract Start Date (#13 from Data Input tab)"]
["It is possible to have adjustments to the SBITA Asset even without an implementation period as not all adjustment items listed above are related to implementation.",{"$f":"'Data Input'!B129","v":"2024-09-01"},{"$f":"'Data Input'!C129","v":"00:00:00"},{"$f":"'Data Input'!D129","v":"00:00:00"},{"$f":"'Data Input'!E129","v":"00:00:00"},{"$f":"'Data Input'!F129","v":"00:00:00"},{"$f":"'Data Input'!G129","v":"00:00:00"},{"$f":"'Data Input'!H129","v":"00:00:00"},{"$f":"'Data Input'!I129","v":"00:00:00"},{"$f":"'Data Input'!J129","v":"00:00:00"},{"$f":"'Data Input'!K129","v":"00:00:00"},{"$f":"'Data Input'!L129","v":"00:00:00"}]
[]
[]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)"]
["If SBITA is not yet in service, this is the ESTIMATED Go-Live Date per the contract",{"$f":"'Data Input'!B132","v":"2025-09-01"},{"$f":"'Data Input'!C132","v":"00:00:00"},{"$f":"'Data Input'!D132","v":"00:00:00"},{"$f":"'Data Input'!E132","v":"00:00:00"},{"$f":"'Data Input'!F132","v":"00:00:00"},{"$f":"'Data Input'!G132","v":"00:00:00"},{"$f":"'Data Input'!H132","v":"00:00:00"},{"$f":"'Data Input'!I132","v":"00:00:00"},{"$f":"'Data Input'!J132","v":"00:00:00"},{"$f":"'Data Input'!K132","v":"00:00:00"},{"$f":"'Data Input'!L132","v":"00:00:00"}]
[]
["1.  Incentives from the SBITA Vendor"]
["a.  Per the description above, were any incentives received from the SBITA Vendor AT or BEFORE the Go-Live Date\n      (could be during the implementation period)?","No"]
[]
[]
["b.  If YES, please describe [if incentive from SBITA Vendor was paid to a 3rd party (i.e. to buy out a preexisting\n      obligation), please provide details here]","N/A"]
[]
[]
["c.  Amount received (or deemed received) DURING FY2025 (7/1/2024 - 6/30/2025)",0]
["- Please enter COA info below"]
["d.  Amount EXPECTED TO BE received (or deemed received) in a future year (7/1/2025 or later) but before\n       Go-Live Date",0]
[]
[]
["e.  Please provide COA for any incentives received from the SBITA Vendor in 1c. above (received during FY2025).\n       REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments\n       made for each SBITA."]
[]
[]
["Note:  If incentive was paid directly to a 3rd party AND was not recorded into AZ360, please explain here","N/A"]
[]
["1ei.  Agency *"]
["1eii.  Fund *"]
["1eiii.  Revenue or Object (please indicate which) *"]
["1eiv.  Appropriation"]
["1ev.  Unit"]
["1evi.  Task"]
["1evii.  Sub Task"]
["1eviii.  Program"]
["1eix.  Phase"]
["1ex.  Program Period"]
["1exi.  Dept Object"]
[]
["2.  Payments made to the SBITA Vendor at or before the Go-Live Date NOT related to\n     implementation (i.e. Downpayment or Subscription Payments made before Go-Live Date)"]
[]
["a.  Per the description above, were any payments made to the SBITA Vendor AT or BEFORE the Go-Live Date\n      (could be during the implementation period)?","Yes"]
[]
[]
["b.  If YES, please describe the nature of the payments","1st Subscription Payment was during implementation period"]
[]
["c.  Amount paid DURING FY2025 (7/1/2024 - 6/30/2025)",450000]
["- Please enter COA info below"]
["d.  Amount EXPECTED TO BE paid in a future year (7/1/2025 or later) but before Go-Live Date (i.e. additional\n       subscription payments due before expected Go-Live Date).",0]
[]
[]
["e.  Please provide COA for amounts listed in 2c. above (paid during FY2025).  REQUIRED elements are starred (*).\n      Use any other COA indicated below that will help identify the payments made for each SBITA."]
[]
["2ei.  Agency *","ADA"]
["2eii.  Fund *","AD1234"]
["2eiii.  Object *",7160]
["2eiv.  Appropriation"]
["2ev.  Unit"]
["2evi.  Task"]
["2evii.  Sub Task"]
["2eviii.  Program"]
["2eix.  Phase"]
["2ex.  Program Period"]
["2exi.  Dept Object"]
[]
["3.  Payments made to the SBITA Vendor and/or a 3rd party at or before the Go-Live Date related\n     to Capitalizable Implementation Costs"]
[]
["Note:  If SBITA was previously excluded due to not meeting the materiality threshold, but now qualifies due to a significant modification AND this modification includes an UPGRADE or New MODULE, please include the Capitalizable Implementation Costs of the Upgrade here (described in Phase 3b. of the instructions above)."]
[]
[]
[]
["a.  Per the description above, were any payments made to the SBITA Vendor or a 3rd party consultant that qualify as Capitalizable Implementation Costs?","Yes - to 3rd party consultant"]
[]
[]
["b.  If YES, please describe the nature of the payments & name of vendor if a 3rd party consultant","Implementation consultants - Conversion Wizards, Inc"]
[]
["c.  Amount paid DURING FY2025 (7/1/2024 - 6/30/2025)",1200000]
["- Please enter COA info below"]
["d.  If implementation is not yet complete, amount EXPECTED TO BE PAID in the future (7/1/2025 or later) but\n      BEFORE Go-Live Date",500000]
[]
[]
["e.  Please provide COA for amounts listed in 3c. above (paid during FY2025).  REQUIRED elements are starred (*).\n      Use any other COA indicated below that will help identify the payments made for each SBITA."]
[]
["3ei.  Agency *","ADA"]
["3eii.  Fund *","AD1234"]
["3eiii.  Object *",8485]
["(Object 8485 - SBITA Implementation has been set up for this, but please provide actual acct used)"]
["3eiv.  Appropriation"]
["3ev.  Unit"]
["3evi.  Task"]
["3evii.  Sub Task"]
["3eviii.  Program"]
["3eix.  Phase"]
["3ex.  Program Period"]
["3exi.  Dept Object"]
[]
["4.  Internal Staff Costs that qualify as Capitalizable Implementation Costs"]
["a.  Per the description above, did any staff time qualify as capitalizable implementation costs and, if so, was\n      that time tracked?","Yes & was tracked"]
[]
["Note:  DO NOT Include any Staff Training Time in the calculations for this item"]
[]
["b.  If Yes & it was tracked, please describe the specific method used for tracking the time (i.e. specific coding\n      by staff when reporting on their time sheets, tracking manually on a spreadsheet, etc)","Used Program 1111 on all implementation-related time"]
[]
[]
[]
["c.  If Yes & it was NOT tracked, can a reasonable estimate be made?","N/A"]
["- If Yes, please provide estimate below.  Also, include calculations & methodology in the documentation."]
[]
["d.  Internal Staff Implementation Costs paid DURING FY2025 (7/1/2024 - 6/30/2025)",25480]
["- Please enter COA info below (if possible)"]
["e.  If implementation is not yet complete, Estimated Staff Costs EXPECTED TO BE INCURRED in the future\n      (7/1/2025 or later) but BEFORE Go-Live Date",5000]
[]
[]
["f.  If staff time was tracked with a special code in AZ360, please provide the COA for amounts listed in 4d. above\n     (paid during FY2025).  REQUIRED elements are starred (*). Use any other COA indicated below that will help\n     identify the the staff costs.  If there are multiple COAs related to staff time (payroll), please ensure that the\n     COAs are included in the backup documentation and notate it here with \"Various - see backup\"."]
[]
[]
[]
["4fi.  Agency *","ADA"]
["4fii.  Fund *",1000]
["4fiii.  Object *","Various PR accts - see backup"]
["4fiv.  Appropriation"]
["4fv.  Unit"]
["4fvi.  Task"]
["4fvii.  Sub Task"]
["4fviii.  Program","Program 1111 was used to ID all staff time related to implementation"]
["4fix.  Phase"]
["4fx.  Program Period"]
["4fxi.  Dept Object"]
[]
[]
["5.  Total Asset Adjustment Costs/Incentives paid/received DURING FY2025",{"$f":"-B61+B90+B120+B152","v":1675480},0,0,0,0,0,0,0,0,0,0]
["6.  Total ESTIMATED Asset Adjustment Costs/Incentives YET TO BE PAID (FY2026 to the end of implementation period)",{"$f":"-B63+B92+B122+B154","v":505000},0,0,0,0,0,0,0,0,0,0]
[]
["7.  Based on Go-Live Date, is the Total as of 6/30/2025 the Final Adj Amount or still Development in Process?",{"$f":"IF(B52<B178,\"FINAL Adj Amount\",\"Development in Progress\")","v":"Development in Progress"},{"$f":"IF(C52<$B178,\"FINAL Adj Amount\",\"Development in Progress\")","v":"FINAL Adj Amount"},"FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount","FINAL Adj Amount"]
["Last day of current FY - Hide in final version","2025-06-30"]
["Note:  If Implementation Period is not yet complete, then only a Development in Progress (DIP) Asset will be recognized at this point.  The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put in Service/Go-Live Date.  If this is the case, please continue to track all Implementation Costs.  This info will be requested in future years through the annual Activity Form."]
[]
[]
[]
["In addition, please DO NOT ENTER ANY SBITA DEVELOPMENT IN PROGRESS (DIP) or IMPLEMENTATION COSTS INTO AZ360 as Fixed Assets.  Because a SBITA Asset includes the asset adjustments listed above PLUS the Present Value of the Fixed Payments (Liability), it all needs to be calculated manually."]

{"sheet":"Implicit Rate info","index":3,"n_rows":34,"n_cols":12,"rows_kept":34,"cut":false,"hidden":false,"merged":[[13,0,14,1],[0,0,0,1],[2,0,3,1],[5,0,6,1],[8,0,9,1],[11,0,11,1]]}
["GASB 96 SBITA Collection Form - Calculating an Implicit Rate on a SBITA"]
[]
["If an interest rate is not explicitely stated within the SBITA contract, there may be an implicit rate, which can be calculated if the information requested below is available."]
[]
[]
["In addition to the information provided within the Data Input tab (see orange auto-filled cells below), check the SBITA contract for availability of the 3 additional data items requested below to determine the Implicit Rate."]
[]
[]
["All 3 items requested below are needed to calculate the Implicit Rate.  If this information is unavailable, then use the Discount Rates provided on the Data Input tab (yellow highlighted table)."]
[]
[]
["If ALL of the information requested below is available for a SBITA, please enter the information within the correct SBITA # column below."]
[]
["Note - The Teal highlighted cells are for manual input. The Orange highlighted cells contain formulas and will automatically fill. Please do not change the Orange cells."]
[]
[]
["Use the SAME COLUMN here as was used in the \"Data Input\" tab.","Example","SBITA # 1","SBITA # 2","SBITA # 3","SBITA # 4","SBITA # 5","SBITA # 6","SBITA # 7","SBITA # 8","SBITA # 9","SBITA # 10"]
["Contract # (Section II, #1a of Data Input Tab)",{"$f":"'Data Input'!B56","v":"ADA2501"},{"$f":"'Data Input'!C56","v":0},{"$f":"'Data Input'!D56","v":0},{"$f":"'Data Input'!E56","v":0},{"$f":"'Data Input'!F56","v":0},{"$f":"'Data Input'!G56","v":0},{"$f":"'Data Input'!H56","v":0},{"$f":"'Data Input'!I56","v":0},{"$f":"'Data Input'!J56","v":0},{"$f":"'Data Input'!K56","v":0},{"$f":"'Data Input'!L56","v":0}]
["Cash Flows at Beginning or End (Section IV, #21 of Data Input tab)",{"$f":"'Data Input'!B164","v":"Begin"},{"$f":"'Data Input'!C165","v":0},{"$f":"'Data Input'!D165","v":0},{"$f":"'Data Input'!E165","v":0},{"$f":"'Data Input'!F165","v":0},{"$f":"'Data Input'!G165","v":0},{"$f":"'Data Input'!H165","v":0},{"$f":"'Data Input'!I165","v":0},{"$f":"'Data Input'!J165","v":0},{"$f":"'Data Input'!K165","v":0},{"$f":"'Data Input'!L165","v":0}]
["Number of payments (Section IV, #20 of Data Input tab)",{"$f":"'Data Input'!B157","v":14},{"$f":"'Data Input'!C157","v":0},{"$f":"'Data Input'!D157","v":0},{"$f":"'Data Input'!E157","v":0},{"$f":"'Data Input'!F157","v":0},{"$f":"'Data Input'!G157","v":0},{"$f":"'Data Input'!H157","v":0},{"$f":"'Data Input'!I157","v":0},{"$f":"'Data Input'!J157","v":0},{"$f":"'Data Input'!K157","v":0},{"$f":"'Data Input'!L157","v":0}]
["Frequency of payments (Section IV, #19 of Data Input tab)",{"$f":"'Data Input'!B150","v":"Semi-annually"},{"$f":"'Data Input'!C150","v":0},{"$f":"'Data Input'!D150","v":0},{"$f":"'Data Input'!E150","v":0},{"$f":"'Data Input'!F150","v":0},{"$f":"'Data Input'!G150","v":0},{"$f":"'Data Input'!H150","v":0},{"$f":"'Data Input'!I150","v":0},{"$f":"'Data Input'!J150","v":0},{"$f":"'Data Input'!K150","v":0},{"$f":"'Data Input'!L150","v":0}]
["Date of first payment (Section IV, #22 of Data Input tab)",{"$f":"'Data Input'!B171","v":"2024-09-01"},{"$f":"'Data Input'!C171","v":"00:00:00"},{"$f":"'Data Input'!D171","v":"00:00:00"},{"$f":"'Data Input'!E171","v":"00:00:00"},{"$f":"'Data Input'!F171","v":"00:00:00"},{"$f":"'Data Input'!G171","v":"00:00:00"},{"$f":"'Data Input'!H171","v":"00:00:00"},{"$f":"'Data Input'!I171","v":"00:00:00"},{"$f":"'Data Input'!J171","v":"00:00:00"},{"$f":"'Data Input'!K171","v":"00:00:00"},{"$f":"'Data Input'!L171","v":"00:00:00"}]
["Date Put Into Service/Go-Live Date (Section IV, #14 of Data Input tab)",{"$f":"'Data Input'!B132","v":"2025-09-01"},{"$f":"'Data Input'!C132","v":"00:00:00"},{"$f":"'Data Input'!D132","v":"00:00:00"},{"$f":"'Data Input'!E132","v":"00:00:00"},{"$f":"'Data Input'!F132","v":"00:00:00"},{"$f":"'Data Input'!G132","v":"00:00:00"},{"$f":"'Data Input'!H132","v":"00:00:00"},{"$f":"'Data Input'!I132","v":"00:00:00"},{"$f":"'Data Input'!J132","v":"00:00:00"},{"$f":"'Data Input'!K132","v":"00:00:00"},{"$f":"'Data Input'!L132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17 of Data Input tab)",{"$f":"'Data Input'!B145","v":"2032-08-31"},{"$f":"'Data Input'!C145","v":"00:00:00"},{"$f":"'Data Input'!D145","v":"00:00:00"},{"$f":"'Data Input'!E145","v":"00:00:00"},{"$f":"'Data Input'!F145","v":"00:00:00"},{"$f":"'Data Input'!G145","v":"00:00:00"},{"$f":"'Data Input'!H145","v":"00:00:00"},{"$f":"'Data Input'!I145","v":"00:00:00"},{"$f":"'Data Input'!J145","v":"00:00:00"},{"$f":"'Data Input'!K145","v":"00:00:00"},{"$f":"'Data Input'!L145","v":"00:00:00"}]
[]
["1.  Fair value of the software at commencement "]
[]
["2.  Expected fair value of the software at the end of the subscription term"]
[]
["3.  Any initial direct costs that the Software Vendor incurs"]
[]
["IF THE IMPLICIT RATE INFO IS FILLED OUT ABOVE, please enter \"See Implicit Rate Info tab\" within the interest rate cell"]
["(Section VI, #28 of the Data Input tab)."]
["If information is provided above, the GAO office will calculate the Implicit Rate and the Present Value of the Lease."]

{"sheet":"How to Enable Macros","index":4,"n_rows":5,"n_cols":1,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["YOUR EXCEL TRUST CENTER SETTINGS MUST BE MACRO-ENABLED TO RUN THE MACROS IN THIS FILE."]
["(This file was created in Excel Version 2016)"]
["To enable settings:"]
["Go to File > Options > Trust Center > Trust Center Settings… "]
["Check \"Enable all macros\" and \"Trust access to the VBA project object model\" as shown below."]

{"sheet":"Example - PV Calc","index":5,"n_rows":156,"n_cols":16,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - Example (column B)","Contract #",{"$f":"'Data Input'!B56","v":"ADA2501"},null,"Present Value Table",null,null,null,null,"Amortization Table"]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!B239","v":1.6803999999999999E-2},null,null,"Period","Payment Amount","Present Value",null,null,"Period","Date","Beginning Balance","Payment","Principal","Interest","Ending Balance"]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!B164","v":"Begin"},"Important:",null,1,450000,{"$f":"F3 / (((1+$B$2)) ^ (E3-1))","v":450000},null,null,1,{"$f":"B6","v":"2024-09-01"},{"$f":"B25","v":6265627.9256252656},450000,{"$f":"M3-O3","v":450000},0,{"$f":"L3-N3","v":5815627.9256252656}]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!B157","v":14},"Do not insert/delete/move",null,2,450000,442563.16851625289,null,null,2,{"$f":"EDATE($B$6,6)","v":"2025-03-01"},{"$f":"P3","v":5815627.9256252656},450000,352274.19,{"$f":"ROUND(L4*$B$2,2)","v":97725.81},5463353.7356252652]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!B150","v":"Semi-annually"},"cells B2:B8",null,3,450000,435249.24028254492,null,null,3,{"$f":"EDATE($B$6,12)","v":"2025-09-01"},5463353.7356252652,450000,358193.8,91806.2,5105159.9356252654]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!B171","v":"2024-09-01"},"(macros depend on them)",null,4,450000,428056.18416385551,null,null,4,{"$f":"EDATE($B$6,18)","v":"2026-03-01"},5105159.9356252654,450000,364212.89,85787.11,4740947.0456252657]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!B132","v":"2025-09-01"},null,null,5,500000,467757.78065810498,null,null,5,{"$f":"EDATE($B$6,24)","v":"2026-09-01"},4740947.0456252657,500000,420333.13,79666.87,4320613.9156252658]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!B145","v":"2032-08-31"},null,null,6,500000,460027.47890262527,null,null,6,{"$f":"EDATE($B$6,30)","v":"2027-03-01"},4320613.9156252658,500000,427396.4,72603.600000000006,3893217.5156252659]
[null,null,null,null,7,500000,452424.93037264328,null,null,7,{"$f":"EDATE($B$6,36)","v":"2027-09-01"},3893217.5156252659,500000,434578.37,65421.63,3458639.1456252658]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments",null,8,500000,444948.02378102689,null,null,8,{"$f":"EDATE($B$6,42)","v":"2028-03-01"},3458639.1456252658,500000,441881.03,58118.97,3016758.115625266]
["Payment Amount #1 & # of Payments at this amount",450000,4,null,9,500000,437594.6827323917,null,null,9,{"$f":"EDATE($B$6,48)","v":"2028-09-01"},3016758.115625266,500000,449306.4,50693.599999999999,2567451.7156252661]
["Payment Amount #2 & # of Payments at this amount",500000,6,null,10,500000,430362.86514647043,null,null,10,{"$f":"EDATE($B$6,54)","v":"2029-03-01"},2567451.7156252661,500000,456856.54,43143.46,2110595.175625266]
["Payment Amount #3 & # of Payments at this amount",550000,4,null,11,550000,465575.61896011163,null,null,11,{"$f":"EDATE($B$6,60)","v":"2029-09-01"},2110595.175625266,550000,514533.56,35466.44,1596061.615625266]
["Payment Amount #4 & # of Payments at this amount",0,0,null,12,550000,457881.38024645025,null,null,12,{"$f":"EDATE($B$6,66)","v":"2030-03-01"},1596061.615625266,550000,523179.78,26820.22,1072881.835625266]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0},null,13,550000,450314.29876992042,null,null,13,{"$f":"EDATE($B$6,72)","v":"2030-09-01"},1072881.835625266,550000,531971.29,18028.71,540910.54562526592]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":7000000},{"$f":"SUM(C11:C15)","v":14},null,14,550000,442872.27309286786,null,null,14,{"$f":"EDATE($B$6,78)","v":"2031-03-01"},540910.54562526592,550000,540910.54,9089.4599999999991,5.625265883281827E-3]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!B208","v":"N/A"}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":7000000}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":6265627.9256252656},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!B174","v":1675480}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!B175","v":505000},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":2180480},{"$f":"'Asset Adj'!B177","v":"Development in Progress"}]
[]
[]
["SBITA Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":"Not yet determined"}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!B218","v":"N/A"}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
["Implementation Period not yet complete.  No Liability or RTU Asset until FY2025 when subscription period begins.  Only capitalize Development in Process for FY2024."]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 1 - PV Calc","index":6,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 1 (column C)","Contract #",{"$f":"'Data Input'!C56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!C239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!C165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!C157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!C150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!C171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!C132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!C145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!C208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!C174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!C175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!C177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!C218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 2 - PV Calc","index":7,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 2 (column D)","Contract #",{"$f":"'Data Input'!D56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!D239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!D165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!D157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!D150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!D171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!D132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!D145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!D208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!D174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!D175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!D177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!D218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 3 - PV Calc","index":8,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 3 (column E)","Contract #",{"$f":"'Data Input'!E56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!E239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!E165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!E157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!E150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!E171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!E132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!E145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!E208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!E174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!E175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!E177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!E218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 4 - PV Calc","index":9,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 4 (column F)","Contract #",{"$f":"'Data Input'!F56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!F239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!F165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!F157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!F150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!F171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!F132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!F145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!F208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!F174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!F175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!F177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!F218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 5 - PV Calc","index":10,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 5 (column G)","Contract #",{"$f":"'Data Input'!G56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!G239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!G165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!G157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!G150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!G171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!G132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!G145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!G208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!G174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!G175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!G177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!G218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 6 - PV Calc","index":11,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 6 (column H)","Contract #",{"$f":"'Data Input'!H56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!H239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!H165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!H157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!H150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!H171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!H132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!H145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!H208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!H174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!H175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!H177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!H218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 7 - PV Calc","index":12,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 7 (column I)","Contract #",{"$f":"'Data Input'!I56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!I239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!I165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!I157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!I150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!I171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!I132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!I145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!I208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!I174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!I175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!I177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!I218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 8 - PV Calc","index":13,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 8 (column J)","Contract #",{"$f":"'Data Input'!J56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!J239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!J165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!J157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!J150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!J171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!J132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!J145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!J208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!J174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!J175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!J177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!J218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 9 - PV Calc","index":14,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 9 (column K)","Contract #",{"$f":"'Data Input'!K56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!K239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!K165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!K157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!K150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!K171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!K132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!K145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!K208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!K174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!K175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!K177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!K218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"SBITA 10 - PV Calc","index":15,"n_rows":156,"n_cols":3,"rows_kept":156,"cut":false,"hidden":false,"merged":[[59,2,62,2],[71,0,73,2]]}
["Present Value Calculations tab - SBITA # 10 (column L)","Contract #",{"$f":"'Data Input'!L56","v":0}]
["Periodic Interest Rate (Section VI, #28-29 from Data Input tab)",{"$f":"'Data Input'!L239","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!L165","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!L157","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!L150","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!L171","v":"00:00:00"},"(macros depend on them)"]
["Date Put Into Service/Go-Live Date (#14 from Data Input tab)",{"$f":"'Data Input'!L132","v":"00:00:00"}]
["Subscription End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!L145","v":"00:00:00"}]
[]
["Fixed Subscription Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below (if needed)",{"$f":"B102","v":0},{"$f":"C102","v":0}]
["Total Subscription Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations:",null,"Answer per Data Input Tab"]
["Termination Penalty (Section V, #26)",0,{"$f":"'Data Input'!L208","v":0}]
[]
["Total Other Payment Considerations",{"$f":"SUM(B19:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B21","v":0}]
[]
[null,null,"Important:"]
["Liability Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
["Asset Adjustments (from Asset Adj tab):","Asset Adj Amount"]
["Total Asset Adjustments during Current Year",{"$f":"'Asset Adj'!L174","v":0}]
["EXPECTED Future Asset Adjustments (if there is an amt here, s/b DIP)",{"$f":"'Asset Adj'!L175","v":0},"DIP or Final?"]
["Total Asset Adjustment",{"$f":"SUM(B29:B30)","v":0},{"$f":"'Asset Adj'!L177","v":"FINAL Adj Amount"}]
[]
[]
["Software Subscription Asset Cost (calculated after Macros below run)",{"$f":"IF(C31=\"FINAL Adj Amount\",B25+B31,\"Not yet determined\")","v":0}]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Teal Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  The Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Fixed Subscription Payment Schedule\" above (starting on row 10)."]
["a - To calculate the Fixed Subscription Payment:"]
["i -  Start with the fixed subscription periodic payments stated in the contract."]
["ii - In addition, add \"Fixed In-Substance Fees\" to the above amount.  \"Fixed In-Substance Fees\" are the Minimum Usage Costs of a variable"]
["usage fee.  For Example, if a subscription includes up to 5 gigs of data used for a set fee, but charges extra for every additional gig, the"]
["minimum fee for the first 5 gigs is the \"Fixed In-Substance Fee\", and the per gig fees above that minimum would be the variable fees."]
["iii - For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate), the payment should be"]
["initially measured using the index or rate published as of the start of the subscription term."]
["iv - EXCLUDE any variable usage fees and/or support/maintenance service fees included in the payment (see #24 & 25 in Data Input tab)"]
["b - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), then"]
["move to line #2 and list the next payment amount & # of payments at that amount, etc."]
["c - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the fixed"]
["payment amount just prior to the date of extension for those unknown amounts."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the SBITA contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!L218","v":0}]
["Make sure to put these skipped payments in the correct order chronologically.  For example, if there"]
["is a skipped payment for payment 13, list the first 12 payments on the first line, then list line #2 as"]
["$0 (col B) and 1 (col C), then line #3 as the continuing payments after the skipped payment."]
["e - If a Bonus payment from the SBITA Vendor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing the 1st payment on the 1st row  & a 1 in"]
["col C, then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of subscription term in"]
["cell B4)."]
["g - Use the Teal box below to add any extra details, how payments change over time (For example, (a) Payment increases 5% every 10 yrs;"]
["(b) Payment recalculated every 5 yrs based on CPI, etc.), or describe payment frequency if Less Often than Annually."]
[]
[]
[]
["h - If there are not enough lines in the Payment Schedule above, use the table below to input the additional payment amounts, and the"]
["totals from the additional table will automatically be added to the Payment Schedule above.  Contact the GAAP Group if you need more"]
["than 25 lines."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Subscription Payment Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Subscription Payments (will automatically be added to the Schdl above)",{"$f":"(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)+(B88*C88)+(B89*C89)+(B90*C90)+(B91*C91)+(B92*C92)+(B93*C93)+(B94*C94)+(B95*C95)+(B96*C96)+(B97*C97)+(B98*C98)+(B99*C99)+(B100*C100)+(B101*C101)","v":0},{"$f":"SUM(C81:C101)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Termination Penalty line should only have an amount listed if a termination clause is deemed Reasonably Certain to be exercised"]
["based on decisions by the Agency (response from the Data Input tab is shown in col C) AND this termination will cause a Penalty to be due."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts  listed"]
["in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to miss any Skipped Payment periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Termination Penalty listed for $50,000, then the final payment in the Table should be $70,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Subscription End Date in cell B8.  Make sure the final"]
["payment date is reasonable in comparison to the subscription End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 + rows), then check the dates the amounts change in the"]
["Amort Table (col K & M) to make sure the payments change on the correct dates as per the SBITA Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 by reviewing the SBITA Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B8) - the corresponding question #s are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the SBITA contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the SBITA Liability and the SBITA Asset Cost in cell B34."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]
["Note:  If Implementation Period is not yet complete, then only a Development in Process Asset will be recognized at this point (cell B34 will"]
["return a value of \"Not yet determined\" if implementation is still in process & will also be noted as \"Development in Process\" in cell C31)."]
["The SBITA Liability & SBITA Asset will not be recognized until the SBITA is Put Into Service/Go-Live Date.  If this is the case, please continue"]
["to track all Implementation Costs.  This info will be requested in the following year's Closing Package Activity Form."]
[]
["Please DO NOT add SBITA implementation costs into the AZ360 Fixed Assets System.  This will be done manually as the SBITA Asset"]
["includes not only the implementation costs, but also the Present Value of the Subscription Fixed Payments (Liability)."]

{"sheet":"Drop Down List","index":16,"n_rows":77,"n_cols":2,"rows_kept":77,"cut":false,"hidden":false,"merged":[]}
[]
["Asset Type","Useful Life in Months"]
["Software <$10M",60]
["Software >$10M",120]
["Other IT Arrangement","Use SBITA term"]
[]
[]
["Payment System"]
["AFIS"]
["APP"]
[]
["Yes / No / N/A"]
["Yes"]
["No"]
["N/A"]
[]
[]
["Payment frequency"]
["Monthly"]
["Quarterly"]
["Semi-annually"]
["Annually"]
[]
["Payment B or E"]
["Begin"]
["End"]
[]
["Payments Equal / Residual Value?"]
["Yes"]
["No - Fill out on PV Calc Tab"]
["N/A"]
[]
["Termination Penalty?"]
["Yes - Fill out on PV Calc Tab"]
["No"]
["N/A"]
[]
["One source / divided"]
["One Source"]
["Divided - Fill out Subscrptn Split Tab"]
[]
["Type of Division"]
["Percentage (%)"]
["Specific $ amount"]
[]
["Planned Cancellation"]
["Yes - use Planned End Date for Lease End Date"]
["No"]
[]
["Asset Capitalized"]
["Yes - in a prior year"]
["Yes - during current FY"]
["No"]
[]
["Existing or New"]
["New (FY2025)"]
["Existing w/Significant Modification"]
[]
["Asset Cost Adj"]
["Yes - Fill out on Asset Adj Tab"]
["No"]
[]
["Asset Adj - Vendor or 3rd party"]
["Yes - to SBITA Vendor"]
["Yes - to 3rd party consultant"]
["Yes - to SBITA Vendor & 3rd party consultant"]
["No"]
[]
["Staff Time"]
["Yes but was not tracked"]
["Yes & was tracked"]
["No"]
[]
["Estimated implementation staff costs"]
["Yes - estimate included below"]
["No"]
["N/A"]