{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Instructions","Asset Entry","Forecast","Forecastdata","Forecast_Chart"],"not_rendered":{"charts":2,"images":0,"macros":1,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":1},"unopened":[{"sheet":"Forecast_Chart","index":4,"reason":"not_a_worksheet"}],"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":"Instructions","index":0,"n_rows":20,"n_cols":9,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,"In Excel, be sure to enable Macros so that the chart will update automatically!"]
[null,null,null,null,null,null,null,null,"Data entry is available in the yellow shaded cells.  Cells that are shaded grey are calculated cells that do not need data entry."]
[null,null,null,null,null,null,null,null,"Negative values will be shown in red text with parentheses.  There is no need to enter values with a negative symbol, all debits will automatically be converted to negative values"]
[null,null,null,null,null,null,null,null,"ASSET ENTRY TAB"]
[null,null,null,null,null,null,null,null,"Click the Asset Entry tab below and enter an asset and its corresponding data."]
[null,null,null,null,null,null,null,null,"At a minimum, data must be entered in columns 'Asset name', 'Estimated Rehab/Replacement Year', and 'Estimated Future Replacement/Renewal Cost', in order for calculations to be performed for that asset."]
[null,null,null,null,null,null,null,null,"There are estimates of life expectancies for some common equipment listed in this sheet.  They are very general estimates, provide your own estimates based upon your professional judgment or an expected life from the examples given."]
[]
[null,null,null,null,null,null,null,null,"FORECAST TAB"]
[null,null,null,null,null,null,null,null,"The forecast tab allows entry of financial information.  This information is used to forecast budget surplus/deficit based upon financial data entered."]
[null,null,null,null,null,null,null,null,"The asset cost information from the Asset Entry Tab will be automatically entered into the Forecast spreadsheet based upon the expected year of the needed expenditure."]
[null,null,null,null,null,null,null,null,"Note: This sheet is assuming that the asset replacement schedule and cost calculated from the 'Asset Entry' sheet will be applied as an expense in the recommended year of replacement.  If you are assuming a loan for the rehab/replacement for the calculated assets, enter that in the \"Loan or Grant Proceeds Assumed\" row in the same year as the expense.  Enter expected loan repayment costs in the \"Annual Payment on Debt\" row in subsequent years to account for that loan payoff."]
[null,null,null,null,null,null,null,null,"If your system is a for profit entity, add the expected profit in the \"Misc yearly costs\" row in addition to any other miscellaneous costs projected."]
[null,null,null,null,null,null,null,null,"The forecast is extended to 20 years in the future from the Current Date, therefore the spreadsheet must be updated at least annually."]
[null,null,null,null,null,null,null,null,"Assumptions can be made with regard to rate increase percentages for each year, loan/grant proceeds, yearly debt service, etc to help a system plan for the future."]
[null,null,null,null,null,null,null,null,"Any surplus/deficit incurred in one year is added/subtracted from the Reserve Fund for the following year."]
[null,null,null,null,null,null,null,null,"A system can vary scenarios to try to keep the reserve fund at a comfortable level for their specific needs, while adjusting other financial variables in the sheet."]
[]
[null,null,null,null,null,null,null,null,"FORECAST CHART"]
[null,null,null,null,null,null,null,null,"The forecast chart graphs the Reserve Fund Balance, Total Cash Needs and Total Revenues."]

{"sheet":"Asset Entry","index":1,"n_rows":19,"n_cols":117,"rows_kept":19,"cut":false,"hidden":false,"merged":[[1,8,1,11]]}
["Asset","Year Installed","Estimated Rehab/Replacement Year","Estimated Future Replacement/Renewal Cost ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Created by Dan Mikesic - PA DEP"]
[null,null,null,null,null,null,null,null,"Estimated Life Expectancies (yrs)"]
[null,null,null,null,null,null,null,null,"Chemical Feed Equipment",null,null,"5-10"]
[null,null,null,null,null,null,null,null,"Chlorination Equipment",null,null,"10-15"]
[null,null,null,null,null,null,null,null,"Computers",null,null,"5"]
[null,null,null,null,null,null,null,null,"Electrical/Electronic Systems",null,null,"7-10"]
[null,null,null,null,null,null,null,null,"Force Mains",null,null,"35-40"]
[null,null,null,null,null,null,null,null,"Lab Equipment",null,null,"5-7"]
[null,null,null,null,null,null,null,null,"Mobile Equipment",null,null,"10"]
[null,null,null,null,null,null,null,null,"Motors",null,null,"30-40"]
[null,null,null,null,null,null,null,null,"Office Furniture",null,null,"10"]
[null,null,null,null,null,null,null,null,"Pumps ",null,null,"30-40"]
[null,null,null,null,null,null,null,null,"SCADA/Telemetering ",null,null,"7-10"]
[null,null,null,null,null,null,null,null,"Storage Tanks",null,null,"30-60"]
[null,null,null,null,null,null,null,null,"Tools and Shop Equipment",null,null,"10-15"]
[null,null,null,null,null,null,null,null,"Valves/Hydrants",null,null,"35-40"]
[]
[null,null,null,null,null,null,null,null,"These are very general estimates, provide your own estimates "]
[null,null,null,null,null,null,null,null,"based upon your professional judgment or an expected life from the examples given."]

{"sheet":"Forecast","index":2,"n_rows":15,"n_cols":23,"rows_kept":15,"cut":false,"hidden":false,"merged":[[13,1,13,12],[14,1,14,12],[2,0,6,0],[7,0,10,0]]}
[null,"Enter dollar figures in the yellow cells.  Gray cells are calculated cells and do not require entry.  It is not necessary to enter negative values. "]
[null,null,{"$f":"Curryear","v":2023},{"$f":"Curryear+1","v":2024},{"$f":"Curryear+2","v":2025},{"$f":"Curryear+3","v":2026},{"$f":"Curryear+4","v":2027},{"$f":"Curryear+5","v":2028},{"$f":"Curryear+6","v":2029},{"$f":"Curryear+7","v":2030},{"$f":"Curryear+8","v":2031},{"$f":"Curryear+9","v":2032},{"$f":"Curryear+10","v":2033},{"$f":"Curryear+11","v":2034},{"$f":"Curryear+12","v":2035},{"$f":"Curryear+13","v":2036},{"$f":"Curryear+14","v":2037},{"$f":"Curryear+15","v":2038},{"$f":"Curryear+16","v":2039},{"$f":"Curryear+17","v":2040},{"$f":"Curryear+18","v":2041},{"$f":"Curryear+19","v":2042},{"$f":"Curryear+20","v":2043}]
["Cash Needs","1. Rehabilitation / Replacement Costs from 'Asset Entry' spreadsheet",{"$f":"IF(ISERROR(VLOOKUP(C2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(C2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(D2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(D2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(E2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(E2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(F2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(F2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(G2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(G2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(H2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(H2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(I2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(I2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(J2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(J2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(K2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(K2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(L2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(L2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(M2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(M2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(N2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(N2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(O2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(O2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(P2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(P2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(Q2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(Q2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(R2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(R2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(S2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(S2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(T2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(T2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(U2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(U2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(V2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(V2,Forecastdata!$A:$B,2,FALSE)))","v":0},{"$f":"IF(ISERROR(VLOOKUP(W2,Forecastdata!$A:$B,2,FALSE)),0,(VLOOKUP(W2,Forecastdata!$A:$B,2,FALSE)))","v":0}]
[null,"2. System O&M costs "]
[null,"8. Misc yearly costs and/or profit assumed for private entity"]
[null,"11. Annual payment on debt"]
[null,"TOTAL CASH NEEDS",{"$f":"IF((SUM(C3:C6)=0),\"\",(SUM(C3:C6)))","v":""},{"$f":"IF((SUM(D3:D6)=0),\"\",(SUM(D3:D6)))","v":""},"","","","","","","","","","","","","","","","","","",""]
["Revenue","7. Estimated Revenue from ratepayers"]
[null,"9. Estimated Miscellaneous yearly revenue"]
[null,"10. Loan or Grant Proceeds assumed"]
[null,"TOTAL REVENUE",{"$f":"IF((SUM(C8:C10)=0),\"\",(SUM(C8:C10)))","v":""},{"$f":"IF((SUM(D8:D10)=0),\"\",(SUM(D8:D10)))","v":""},"","","","","","","","","","","","","","","","","","",""]
[null,"13. Surplus / Deficit (will be applied to reserve fund next year)",{"$f":"IF(ISERROR(SUM(-C7,C11)),\"\",IF((SUM(-C7,C11)=0),\"\",(SUM(-C7,C11))))","v":""},{"$f":"IF(ISERROR(SUM(-D7,D11)),\"\",IF((SUM(-D7,D11)=0),\"\",(SUM(-D7,D11))))","v":""},{"$f":"IF(ISERROR(SUM(-E7,E11)),\"\",IF((SUM(-E7,E11)=0),\"\",(SUM(-E7,E11))))","v":""},"","","","","","","","","","","","","","","","","",""]
[null,{"$f":"\"14. Reserve Fund Balance (Enter balance for start of \"& Curryear &\".)\"","v":"14. Reserve Fund Balance (Enter balance for start of 2023.)"},null,{"$f":"IF((SUM(C12,C13)=0),\"\",(SUM(C12,C13)))","v":""},{"$f":"IF((SUM(D12,D13)=0),\"\",(SUM(D12,D13)))","v":""},"","","","","","","","","","","","","","","","","",""]
[null,"Note: This sheet is assuming that the asset replacement schedule and cost calculated from the 'Asset Entry' sheet will be applied as an expense in the recommended year of replacement.  If are assuming a loan for the rehab/replacement for the calculated assets, enter that in the loan or Grant Proceeds Assumed row in the same year as the expense.  Enter expected loan repayment costs in the \"Annual Payment on Debt\" row in subsequent years to account for that loan payoff."]
[null,"If your system is a for profit entity, add the expected profit in Misc yearly costs row in addition to any other miscellaneous costs projected."]

{"sheet":"Forecastdata","index":3,"n_rows":4,"n_cols":14,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
["Sum of Estimated Future Replacement/Renewal Cost ",null,null,null,null,null,null,null,null,null,null,null,"Current Year",{"$f":"YEAR(TODAY())","v":2023}]
["Estimated Rehab/Replacement Year","Total"]
["(blank)"]
["Grand Total"]