CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you will produce a spreadsheet that models the college town's financial situation. Then, in Assignment 2, you will use the spreadsheet to gather decision support data about possible financial scenarios and write a memorandum that documents your analysis and findings. First, you will create the spreadsheet model of the decision. The model covers the three years from 2020 to 2022. This section helps you set up each of the following spreadsheet components before entering cell formulas: . Constants Inputs . Summary of Key Results Calculations Income and Cash Flow Statements Debt Owed A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use, it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 7, and then select College TownBudget.xlsx. Constants Section Your spreadsheet should include the constants shown in Figure 7-1. An explanation of the line items follows the figure. . Minimum Cash Needed to Start YearThe city council wants to have at least $3 million cash at the beginning of each year. Assume that the town could borrow from a friendly banker at the end of a year in order to begin the new year with that amount. Number of EmployeesThe city's baseline number of employees is expected to be 250, but that number would increase, depending on added apartments. Avg Gallons of Water Used per HouseholdA household (actual house or apartment) is expected to use an average of 100 gallons a year. B D E A 1 The College Town Budget 2 3 CONSTANTS 4 MINIMUM CASH NEEDED TO START YEAR 5 NUMBER OF EMPLOYEES 6 AVG GALLONS OF WATER USED PER HOUSEHOLD 7 AVG KILOWATT HOURS USED PER HOUSEHOLD 8 PARKING FEES 9 REALTY TRANSFER FEES 10 EXPECTED INCREASE IN EMPLOYEE COST 11 EXPECTED INCREASE IN WATER COST 12 EXPECTED INCREASE IN KILOWATT COST 13 APARTMENT PERMIT FEE 14 EXPECTED CAPITAL EXPENDITURES 15 EXPECTED INTEREST RATE ON DEBT 2012 NA NA NA NA NA NA NA NA NA NA NA NA 2020 2021 2022 $ 3.000.000 $ 3.000.000 $ 3.000.000 250 250 250 100 100 100 42.5 42.5 42.5 $ 3.000.000 $3,000,000 $ 3.000.000 $ 1.500,000 $ 1.500,000 $ 1.500.000 196 1% 1% 296 296 2 296 294 296 $ 100 $ 110$ 120 $ 3.000.000 $ 3.500,000 $ 4.000.000 8% 8% 896 FIGURE 7-1 Constants section Avg Kilowatt Hours Used per HouseholdA household (actual house or apartment) is expected to use an average of 42.5 kilowatt hours a year. Parking FeesThe city earns money from parking lots and on-street meters. This amount is expected to be $3 million a year. expected to be $3 million a year. Realty Transfer FeesThe city earns a fee when real estate changes hands. This amount is expected to be $1.5 million a year. Expected Increase in Employee CostThe expected average cost of salary, wages, and benefits is expected to rise 1 percent each year. Expected Increase in Water CostThe city buys water, which it then resells to citizens in town. The cost of the water purchased by the city is expected to increase 2 percent each year. Expected Increase in Kilowatt CostThe city buys electricity, which it then resells to citizens in town. The cost of the electricity purchased by the city is expected to increase 2 percent each year. Apartment Permit FeeA real estate developer must pay the city $100 for each apartment put in place in 2020. The fee will increase in 2021 and 2022. This is a one-time payment for each apartment. Expected Capital ExpendituresThe council expects to spend money each year on city infrastructure, as shown. Expected Interest Rate on Debt-The notes are expected to carry an 8 percent interest rate. Inputs Section Your spreadsheet include the following inputs the years 2020 to 2022, as shown in Figure 7-2. - Increase in Water Selling PriceThe city would like to increase the selling price per gallon. If the expected price per gallon increase in a year is 1 percent, enter 01 in this cell; or, if you format these cells for percentage, you can enter a 1. Increase in Kilowatt Selling PriceThe city would like to increase the selling price per kilowatt. If the expected price per kilowatt increase in a year is 1 percent, enter 01 in this cell; or, if you format these cells for percentage, you can enter a 1. Property Tax IncreaseThe city would like to raise property taxes. If the tax increase in a year is 1 percent, enter 01 in this cell; or, if you format these cells for percentage, you can enter a 1. Accelerated Apartment Construction (Y/N)Enter a Y if the city will opt for 500 new apartments a year, otherwise, enter an N. 2020 D 2021 E 2022 A INPUTS 18 INCREASE IN WATER SELLING PRICE 19 INCREASE IN KILOWATT SELLING PRICE 20 PROPERTY TAX INCREASE 21 ACCELERATED APARTMENT CONSTRUCTION (Y/N) B 2019 NA NA NA NA NA NA FIGURE 7-2 Inputs section You must observe certain rules of thumb that the city council has learned in bruising budget battles of yore: Water and electricity rates can be raised in the same year. Water and electricity rates cannot be raised in a year in which property taxes are raised . The property tax rate, once raised, cannot be raised in either of the next two years. These rules need not be encoded in your formulas, but you must keep them in mind when making manual entries Summary of Key Results Section Your spreadsheet should include the results shown in Figure 7-3. 2020 D 2021 E 2022 B 2010 NA NA 23 SUMMARY OF KEY RESULTS 24 NET INCOME 25 END-OF-THE-YEAR CASH ON HAND 26 END-OF-THE-YEAR DEBT OWED NA FIGURE 7-3 Summary of Key Results section For each year, your spreadsheet should compute net income, cash on hand at the end of the year, and debt owed at the end of the year. These values are computed elsewhere in the spreadsheet and should be echoed here Calculations Section You should calculate intermediate results (see Figure 7-4) that will be used in the income and cash flow statements that follow. When called for, use absolute referencing properly Values must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA," which stands for "not applicable." An explanation of each item in this section follows the figure. C 2020 D 2021 E 2022 A 28 CALCULATIONS 29 NUMBER OF EMPLOYEES 30 AVERAGE PROPERTY TAX PER UNIT 31 AVERAGE EMPLOYMENT COST PER EMPLOYEE 32 NUMBER OF APARTMENTS BUILT IN YEAR 33 NUMBER OF HOUSING UNITS 34 COST OF A GALLON OF WATER 35 COST OF A KILOWATT HOUR 36 SELLING PRICE OF A GALLON OF WATER 37 SELLING PRICE OF A KILOWATT HOUR 38 NUMBER OF GALLONS OF WATER SOLD 39 NUMBER OF KILOWATTS SOLD 40 CONTRACTUAL AND OTHER COSTS B 2019 NA S 650.00 $ 125.000 NA 10,000 $ 4.00 $ 60.00 $ 9.00 s 120.00 NA NA $ 9,000,000 FIGURE 7-4 Calculations section Number of EmployeesIf apartments are built on an accelerated basis, two employees will have to be added to the baseline number of employees, which is a value from the Constants section. Otherwise, the city council thinks they can get by with just the baseline number of employees in the year. Average Property Tax per UnitThe average property tax per unit (house or apartment) in a year will be a function of the prior year's tax per unit and the tax rate increase expected. The expected tax increase in a year is a value from the Inputs section. For example, if a 1 percent increase was expected for 2020, the tax per unit would be 1.01 times the 2019 tax. Average Employment Cost per Employee-The average employment cost in a year will be a function of the prior year's cost and the increase expected in the year. The expected increase is a value from the Constants section. For example, if a 1 percent increase was expected for 2020, the average cost would be 1.01 times the 2019 cost. Number of Apartments Built in YearIf accelerated construction is expected, 500 units will be added per year, otherwise, 250 will be added per year. Number of Housing UnitsThe number of housing units in town is a function of the number of units in town in a prior year and the number of apartments added in the year. For example, if 500 apartment units are added in 2020, the number of housing units in 2020 will be the existing number in 2019 plus 500 . Cost of a Gallon of WaterThe cost of a gallon of water to the city in a year will be a function of the prior year's cost and the increase expected in the year. The expected increase is a value from the Constants section. . Cost of a Kilowatt HourThe cost of a kilowatt hour to the city in a year will be a function of . Cost of a Kilowatt HourThe cost of a kilowatt hour to the city in a year will be a function of the prior year's cost and the increase expected in the year. The expected increase is a value from the Constants section. Selling Price of a Gallon of Water-The selling price of a gallon of water in a year will be a function of the prior year's selling price and the increase expected in the year. The expected selling price increase is a value from the Inputs section. Selling Price of a kilowatt HourThe selling price of a kilowatt hour in a year will be a function of the prior year's selling price and the increase expected in the year. The expected selling price increase is a value from the Inputs section. Number of Gallons of Water Sold-The number of gallons sold is a function of the number of housing units in a year (a value from elsewhere in the Calculations section) and the average gallons of water used by a household in the year (a value from the Constants section). Number of Kilowatts SoldThe number of kilowatts of electricity sold is a function of the numberof housing units in a year (a value from elsewhere in the Calculations section) and the average kilowatts used by a household in the year (a value from the Constants section). Contractual and Other CostsIf construction occurs on an accelerated basis, contractual and other costs are expected to increase 3 percent over the prior year's cost. Otherwise, contractual and other costs are expected to increase 1 percent over the prior year's cost. Income and Cash Flow Statements The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This value is followed by the income statement and the calculation of cash on hand at year's end. For readability, format cells in this section as currency with zero decimals. Values must be computed by cell formula; hard-code numbers in formulas only if you are told to do so. Cell formulas should not reference a cell with a value of"NA." Your spreadsheets should look like those shown in Figures 7-5 and 7-6. A discussion of each item in the section follows each figure. B C D E 2020 2021 2022 2019 NA INCOME STATEMENT AND 42 CASH FLOW STATEMENT 43 BEGINNING-OF-THE-YEAR CASH ON HAND 44 45 REVENUE 46 WATER REVENUE 47 ELECTRIC REVENUE 48 PROPERTY TAX REVENUE 49 PARKING REVENUE 50 REALTY TRANSFER FEES 51 APARTMENT PERMITS . or S2 TOTAL REVENUE 53 SA EXPENSES: 55 COST OF WATER 56 COST OF ELECTRICITY 57 EMPLOYMENT COST 58 CONTRACTUAL AND OTHER COSTS 59 INTEREST ON DEBT OWED 60 TOTAL EXPENSES 61 62. NET INCOME NA NA NA NA NA NA NA NA NA NA NA NA FIGURE 7-5 Income and Cash Flow statements section Beginning-of-the-Year Cash on HandThis value is the cash on hand at the end of the prior year. Water RevenueWater revenue is a function of the gallons sold and the selling price per gallon, both of which are values from the Calculations section. Electric Revenue-Electricity revenue is a function of the number of kilowatts sold and the selling price per kilowatt, both of which are values from the Calculations section. Property Tax RevenueProperty tax revenue is a function of the number of housing units and the property tax per housing unit, both of which are values from the Calculations section. Parking RevenueThis value is recorded in the Constants section and can be echoed here. Realty Transfer FeesThis value is recorded in the Constants section and can be echoed here. Apartment PermitsThis value is a function of the number of apartment units added in the year (a value from the Calculations section) and the per-unit permit fee (from the Constants section) Total RevenueThis amount is the sum of water, electricity, property tax, parking, transfer fee, and permit revenues. . Cost of Water-This cost is a function of the number of gallons sold and the cost per gallon, both of which are values from the Calculations section. . Cost of ElectricityThis cost is a function of the number of kilowatts sold and the cost per kilowatt, both of which are values from the Calculations section. Employment CostThis cost is a function of the number of employees in the year and the average employee cost in the year, both of which are values from the Calculations section. . Contractual and Other CostsThis value is recorded in the Calculations section and can be echoed here. Interest on Debt OwedThis amount is the product of debt owed at the beginning of the year and the interest rate, which is a value from the Constants section. Total ExpensesThis amount is the sum of the costs of water, electricity, employment, contractual and other, and interest on debt owed. Net Income-This amount is the difference between total revenue and total expenses. (Note that no taxes are paid by this governmental unit.) Line items for the year-end cash calculation are shown in Figure 7-6. In the figure, column B represents 2019, column C is for 2020, and so on. Year 2019 values are NA except for end-of- year cash on hand, which is $3 million. Values must be computed by cell formula; hard-code numbers in formulas only when you are told to do so. Cell formulas should not reference a cell with a value of "NA." An explanation of each item follows the figure. C D E 8 NA NA 64 EXPECTED CAPITAL EXPENDITURES 65 REPAYMENT TO NOTE HOLDERS NET CASH POSITION (NCP) BEFORE 66 BORROWING 67 ADD: BORROWING FROM BANK 68 EQUALS: END-OF-THE-YEAR CASH ON HAND NA NA $ 3,000,000 FIGURE 7-6 End-of-year cash on hand section Expected Capital ExpendituresThese outlays are shown in the Constants section. They reduce cash on hand. Repayment to Note Holders$5 million in notes is owed to the banks at the end of 2019. The agreement is that 10 percent of the notes outstanding at the end of the prior year will be repaid in the current year. You can hard-code the value "10" as a denominator in your formula. Repayment of debt reduces cash on hand. Net Cash Position (NCP) Before BorrowingThis amount equals cash at the beginning of the year plus the year's net income, minus scheduled capital expenditures and minus repayments to note holders Add: Borrowing from Bank-Assume that the city can borrow from their friendly bankers at the end of the year to reach the minimum cash needed to start the next year, this minimum is a value from the Constants section. If the NCP is less than this minimum amount, the city will borrow enough to start the next year with the minimum. Borrowing increases cash on hand, of course. .Equals: End-of-the-Year Cash on HandThis amount is the NCP plus any bank borrowing. Debt Owed Section This section shows a calculation of debt owed at the end of a year, as shown in Figure 7-7. Year 2019 values are NA except for end-of-year debt owed, which is $5 million. Values must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA." An explanation of each item follows the figure. B D 70 DEBT OWED 71 BEGINNING-OF-THE-YEAR DEBT OWED 72 ADD: BORROWING FROM BANK 73 LESS: REPAYMENT TO NOTE HOLDERS 74 EQUALS: END-OF-THE-YEAR DEBT OWED NA NA NA $ 5,000,000 FIGURE 7-7 Debt Owed section Beginning-of-the-Year Debt Owed-Debt owed at the beginning of a year equals the debt owed at the end of the prior year. Add: Borrowing from BankThis amount has been calculated elsewhere and can be echoed to this section. Borrowing increases the amount of debt owed. Less: Repayment to Note HoldersThis amount has been calculated elsewhere and can be echoed to this section. Repayments reduce the amount of debt owed. Equals: End-of-the-Year Debt OwedThis is the ?H??H?01/?{-HI?1??l?uring the year. CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you will produce a spreadsheet that models the college town's financial situation. Then, in Assignment 2, you will use the spreadsheet to gather decision support data about possible financial scenarios and write a memorandum that documents your analysis and findings. First, you will create the spreadsheet model of the decision. The model covers the three years from 2020 to 2022. This section helps you set up each of the following spreadsheet components before entering cell formulas: . Constants Inputs . Summary of Key Results Calculations Income and Cash Flow Statements Debt Owed A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use, it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 7, and then select College TownBudget.xlsx. Constants Section Your spreadsheet should include the constants shown in Figure 7-1. An explanation of the line items follows the figure. . Minimum Cash Needed to Start YearThe city council wants to have at least $3 million cash at the beginning of each year. Assume that the town could borrow from a friendly banker at the end of a year in order to begin the new year with that amount. Number of EmployeesThe city's baseline number of employees is expected to be 250, but that number would increase, depending on added apartments. Avg Gallons of Water Used per HouseholdA household (actual house or apartment) is expected to use an average of 100 gallons a year. B D E A 1 The College Town Budget 2 3 CONSTANTS 4 MINIMUM CASH NEEDED TO START YEAR 5 NUMBER OF EMPLOYEES 6 AVG GALLONS OF WATER USED PER HOUSEHOLD 7 AVG KILOWATT HOURS USED PER HOUSEHOLD 8 PARKING FEES 9 REALTY TRANSFER FEES 10 EXPECTED INCREASE IN EMPLOYEE COST 11 EXPECTED INCREASE IN WATER COST 12 EXPECTED INCREASE IN KILOWATT COST 13 APARTMENT PERMIT FEE 14 EXPECTED CAPITAL EXPENDITURES 15 EXPECTED INTEREST RATE ON DEBT 2012 NA NA NA NA NA NA NA NA NA NA NA NA 2020 2021 2022 $ 3.000.000 $ 3.000.000 $ 3.000.000 250 250 250 100 100 100 42.5 42.5 42.5 $ 3.000.000 $3,000,000 $ 3.000.000 $ 1.500,000 $ 1.500,000 $ 1.500.000 196 1% 1% 296 296 2 296 294 296 $ 100 $ 110$ 120 $ 3.000.000 $ 3.500,000 $ 4.000.000 8% 8% 896 FIGURE 7-1 Constants section Avg Kilowatt Hours Used per HouseholdA household (actual house or apartment) is expected to use an average of 42.5 kilowatt hours a year. Parking FeesThe city earns money from parking lots and on-street meters. This amount is expected to be $3 million a year. expected to be $3 million a year. Realty Transfer FeesThe city earns a fee when real estate changes hands. This amount is expected to be $1.5 million a year. Expected Increase in Employee CostThe expected average cost of salary, wages, and benefits is expected to rise 1 percent each year. Expected Increase in Water CostThe city buys water, which it then resells to citizens in town. The cost of the water purchased by the city is expected to increase 2 percent each year. Expected Increase in Kilowatt CostThe city buys electricity, which it then resells to citizens in town. The cost of the electricity purchased by the city is expected to increase 2 percent each year. Apartment Permit FeeA real estate developer must pay the city $100 for each apartment put in place in 2020. The fee will increase in 2021 and 2022. This is a one-time payment for each apartment. Expected Capital ExpendituresThe council expects to spend money each year on city infrastructure, as shown. Expected Interest Rate on Debt-The notes are expected to carry an 8 percent interest rate. Inputs Section Your spreadsheet include the following inputs the years 2020 to 2022, as shown in Figure 7-2. - Increase in Water Selling PriceThe city would like to increase the selling price per gallon. If the expected price per gallon increase in a year is 1 percent, enter 01 in this cell; or, if you format these cells for percentage, you can enter a 1. Increase in Kilowatt Selling PriceThe city would like to increase the selling price per kilowatt. If the expected price per kilowatt increase in a year is 1 percent, enter 01 in this cell; or, if you format these cells for percentage, you can enter a 1. Property Tax IncreaseThe city would like to raise property taxes. If the tax increase in a year is 1 percent, enter 01 in this cell; or, if you format these cells for percentage, you can enter a 1. Accelerated Apartment Construction (Y/N)Enter a Y if the city will opt for 500 new apartments a year, otherwise, enter an N. 2020 D 2021 E 2022 A INPUTS 18 INCREASE IN WATER SELLING PRICE 19 INCREASE IN KILOWATT SELLING PRICE 20 PROPERTY TAX INCREASE 21 ACCELERATED APARTMENT CONSTRUCTION (Y/N) B 2019 NA NA NA NA NA NA FIGURE 7-2 Inputs section You must observe certain rules of thumb that the city council has learned in bruising budget battles of yore: Water and electricity rates can be raised in the same year. Water and electricity rates cannot be raised in a year in which property taxes are raised . The property tax rate, once raised, cannot be raised in either of the next two years. These rules need not be encoded in your formulas, but you must keep them in mind when making manual entries Summary of Key Results Section Your spreadsheet should include the results shown in Figure 7-3. 2020 D 2021 E 2022 B 2010 NA NA 23 SUMMARY OF KEY RESULTS 24 NET INCOME 25 END-OF-THE-YEAR CASH ON HAND 26 END-OF-THE-YEAR DEBT OWED NA FIGURE 7-3 Summary of Key Results section For each year, your spreadsheet should compute net income, cash on hand at the end of the year, and debt owed at the end of the year. These values are computed elsewhere in the spreadsheet and should be echoed here Calculations Section You should calculate intermediate results (see Figure 7-4) that will be used in the income and cash flow statements that follow. When called for, use absolute referencing properly Values must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA," which stands for "not applicable." An explanation of each item in this section follows the figure. C 2020 D 2021 E 2022 A 28 CALCULATIONS 29 NUMBER OF EMPLOYEES 30 AVERAGE PROPERTY TAX PER UNIT 31 AVERAGE EMPLOYMENT COST PER EMPLOYEE 32 NUMBER OF APARTMENTS BUILT IN YEAR 33 NUMBER OF HOUSING UNITS 34 COST OF A GALLON OF WATER 35 COST OF A KILOWATT HOUR 36 SELLING PRICE OF A GALLON OF WATER 37 SELLING PRICE OF A KILOWATT HOUR 38 NUMBER OF GALLONS OF WATER SOLD 39 NUMBER OF KILOWATTS SOLD 40 CONTRACTUAL AND OTHER COSTS B 2019 NA S 650.00 $ 125.000 NA 10,000 $ 4.00 $ 60.00 $ 9.00 s 120.00 NA NA $ 9,000,000 FIGURE 7-4 Calculations section Number of EmployeesIf apartments are built on an accelerated basis, two employees will have to be added to the baseline number of employees, which is a value from the Constants section. Otherwise, the city council thinks they can get by with just the baseline number of employees in the year. Average Property Tax per UnitThe average property tax per unit (house or apartment) in a year will be a function of the prior year's tax per unit and the tax rate increase expected. The expected tax increase in a year is a value from the Inputs section. For example, if a 1 percent increase was expected for 2020, the tax per unit would be 1.01 times the 2019 tax. Average Employment Cost per Employee-The average employment cost in a year will be a function of the prior year's cost and the increase expected in the year. The expected increase is a value from the Constants section. For example, if a 1 percent increase was expected for 2020, the average cost would be 1.01 times the 2019 cost. Number of Apartments Built in YearIf accelerated construction is expected, 500 units will be added per year, otherwise, 250 will be added per year. Number of Housing UnitsThe number of housing units in town is a function of the number of units in town in a prior year and the number of apartments added in the year. For example, if 500 apartment units are added in 2020, the number of housing units in 2020 will be the existing number in 2019 plus 500 . Cost of a Gallon of WaterThe cost of a gallon of water to the city in a year will be a function of the prior year's cost and the increase expected in the year. The expected increase is a value from the Constants section. . Cost of a Kilowatt HourThe cost of a kilowatt hour to the city in a year will be a function of . Cost of a Kilowatt HourThe cost of a kilowatt hour to the city in a year will be a function of the prior year's cost and the increase expected in the year. The expected increase is a value from the Constants section. Selling Price of a Gallon of Water-The selling price of a gallon of water in a year will be a function of the prior year's selling price and the increase expected in the year. The expected selling price increase is a value from the Inputs section. Selling Price of a kilowatt HourThe selling price of a kilowatt hour in a year will be a function of the prior year's selling price and the increase expected in the year. The expected selling price increase is a value from the Inputs section. Number of Gallons of Water Sold-The number of gallons sold is a function of the number of housing units in a year (a value from elsewhere in the Calculations section) and the average gallons of water used by a household in the year (a value from the Constants section). Number of Kilowatts SoldThe number of kilowatts of electricity sold is a function of the numberof housing units in a year (a value from elsewhere in the Calculations section) and the average kilowatts used by a household in the year (a value from the Constants section). Contractual and Other CostsIf construction occurs on an accelerated basis, contractual and other costs are expected to increase 3 percent over the prior year's cost. Otherwise, contractual and other costs are expected to increase 1 percent over the prior year's cost. Income and Cash Flow Statements The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This value is followed by the income statement and the calculation of cash on hand at year's end. For readability, format cells in this section as currency with zero decimals. Values must be computed by cell formula; hard-code numbers in formulas only if you are told to do so. Cell formulas should not reference a cell with a value of"NA." Your spreadsheets should look like those shown in Figures 7-5 and 7-6. A discussion of each item in the section follows each figure. B C D E 2020 2021 2022 2019 NA INCOME STATEMENT AND 42 CASH FLOW STATEMENT 43 BEGINNING-OF-THE-YEAR CASH ON HAND 44 45 REVENUE 46 WATER REVENUE 47 ELECTRIC REVENUE 48 PROPERTY TAX REVENUE 49 PARKING REVENUE 50 REALTY TRANSFER FEES 51 APARTMENT PERMITS . or S2 TOTAL REVENUE 53 SA EXPENSES: 55 COST OF WATER 56 COST OF ELECTRICITY 57 EMPLOYMENT COST 58 CONTRACTUAL AND OTHER COSTS 59 INTEREST ON DEBT OWED 60 TOTAL EXPENSES 61 62. NET INCOME NA NA NA NA NA NA NA NA NA NA NA NA FIGURE 7-5 Income and Cash Flow statements section Beginning-of-the-Year Cash on HandThis value is the cash on hand at the end of the prior year. Water RevenueWater revenue is a function of the gallons sold and the selling price per gallon, both of which are values from the Calculations section. Electric Revenue-Electricity revenue is a function of the number of kilowatts sold and the selling price per kilowatt, both of which are values from the Calculations section. Property Tax RevenueProperty tax revenue is a function of the number of housing units and the property tax per housing unit, both of which are values from the Calculations section. Parking RevenueThis value is recorded in the Constants section and can be echoed here. Realty Transfer FeesThis value is recorded in the Constants section and can be echoed here. Apartment PermitsThis value is a function of the number of apartment units added in the year (a value from the Calculations section) and the per-unit permit fee (from the Constants section) Total RevenueThis amount is the sum of water, electricity, property tax, parking, transfer fee, and permit revenues. . Cost of Water-This cost is a function of the number of gallons sold and the cost per gallon, both of which are values from the Calculations section. . Cost of ElectricityThis cost is a function of the number of kilowatts sold and the cost per kilowatt, both of which are values from the Calculations section. Employment CostThis cost is a function of the number of employees in the year and the average employee cost in the year, both of which are values from the Calculations section. . Contractual and Other CostsThis value is recorded in the Calculations section and can be echoed here. Interest on Debt OwedThis amount is the product of debt owed at the beginning of the year and the interest rate, which is a value from the Constants section. Total ExpensesThis amount is the sum of the costs of water, electricity, employment, contractual and other, and interest on debt owed. Net Income-This amount is the difference between total revenue and total expenses. (Note that no taxes are paid by this governmental unit.) Line items for the year-end cash calculation are shown in Figure 7-6. In the figure, column B represents 2019, column C is for 2020, and so on. Year 2019 values are NA except for end-of- year cash on hand, which is $3 million. Values must be computed by cell formula; hard-code numbers in formulas only when you are told to do so. Cell formulas should not reference a cell with a value of "NA." An explanation of each item follows the figure. C D E 8 NA NA 64 EXPECTED CAPITAL EXPENDITURES 65 REPAYMENT TO NOTE HOLDERS NET CASH POSITION (NCP) BEFORE 66 BORROWING 67 ADD: BORROWING FROM BANK 68 EQUALS: END-OF-THE-YEAR CASH ON HAND NA NA $ 3,000,000 FIGURE 7-6 End-of-year cash on hand section Expected Capital ExpendituresThese outlays are shown in the Constants section. They reduce cash on hand. Repayment to Note Holders$5 million in notes is owed to the banks at the end of 2019. The agreement is that 10 percent of the notes outstanding at the end of the prior year will be repaid in the current year. You can hard-code the value "10" as a denominator in your formula. Repayment of debt reduces cash on hand. Net Cash Position (NCP) Before BorrowingThis amount equals cash at the beginning of the year plus the year's net income, minus scheduled capital expenditures and minus repayments to note holders Add: Borrowing from Bank-Assume that the city can borrow from their friendly bankers at the end of the year to reach the minimum cash needed to start the next year, this minimum is a value from the Constants section. If the NCP is less than this minimum amount, the city will borrow enough to start the next year with the minimum. Borrowing increases cash on hand, of course. .Equals: End-of-the-Year Cash on HandThis amount is the NCP plus any bank borrowing. Debt Owed Section This section shows a calculation of debt owed at the end of a year, as shown in Figure 7-7. Year 2019 values are NA except for end-of-year debt owed, which is $5 million. Values must be computed by cell formula. Cell formulas should not reference a cell with a value of "NA." An explanation of each item follows the figure. B D 70 DEBT OWED 71 BEGINNING-OF-THE-YEAR DEBT OWED 72 ADD: BORROWING FROM BANK 73 LESS: REPAYMENT TO NOTE HOLDERS 74 EQUALS: END-OF-THE-YEAR DEBT OWED NA NA NA $ 5,000,000 FIGURE 7-7 Debt Owed section Beginning-of-the-Year Debt Owed-Debt owed at the beginning of a year equals the debt owed at the end of the prior year. Add: Borrowing from BankThis amount has been calculated elsewhere and can be echoed to this section. Borrowing increases the amount of debt owed. Less: Repayment to Note HoldersThis amount has been calculated elsewhere and can be echoed to this section. Repayments reduce the amount of debt owed. Equals: End-of-the-Year Debt OwedThis is the ?H??H?01/?{-HI?1??l?uring the year