The North Central Water Company has finalized its financial statements for the 2022 financial year. The Companys board of directors has asked you, their cost accountant, to look at the financial results and to compare the financial performance for the 2022 fiscal year to the results of the 2021 financial year. The board would also like you to project the budget revenues and expenses for the 2023 financial year based on several key assumptions. They have asked you to submit an excel file containing the financial results and budget projections and a one-page memorandum of your findings. The board members are especially concerned about rising costs because of inflation and the growing number of customers.

Financial Results:

Total Number of Customers 31,215 26,500

% of Total % of Total

2022 Revenues 2021 Revenues

Revenues:

Water Sales $1,384,000 ? $ 1,187,000 ?

Late Fees 47,900 ? 49,000 ?

Fire Hydrant Fees 119,050 ? 117,500 ?

Total Revenues $1,550,950 100% $1,353,500 100%

Expenses:

Cost of Water Sold $687,000 ? $505,000 ?

Payroll Expense 701,250 ? 602,000 ?

Overhead Expense 274,250 ? 276,000 ?

Miscellaneous Expenses 79,000 ? 63,000 ?

Total Expenses $1,741,500 ? $1,446,000 ?

Net Income (Loss) $ <$190,550> ? $ < 92,500> ?

I. Excel Analysis Please include your name in the filename. You must use formulas in the excel spreadsheet rather than typing-in calculated numbers to get full credit. You will run into rounding errors unless you use formulas. The spreadsheet already includes some formulas and calculated numbers to help you. YOU SHOULD HAVE AN ANSWER WHEREVER YOU SEE A QUESTION MARK (?).

Required:

1. Calculate each revenue and expense item as a percentage of total revenues in 2022 and 2021 (show percentages out to TWO decimal places for all revenues and expenses, but round total revenues percentage to ZERO decimal places- see examples in the spreadsheet).

2. Calculate the water sales per customer for 2022 and 2021 (show the number out to TWO decimal places- see example in the spreadsheet).

3. Calculate the Companys budgeted amounts for 2023 based on the assumptions listed below for each revenue and expense item. Then calculate each item as a percentage of total revenues, just like you did for 2022 and 2021. Then calculate the water sales per customer for 2023, just as you did for 2022 and 2021- see examples in the spreadsheet.

4. Finally, calculate the variance differences in each revenue and expense item between 2023 and 2022 and 2022 and 2021- see the example in the spreadsheet. You will also need to indicate whether the variance difference between 2021 and 2022 is favorable (F) or unfavorable (U) for each revenue and expense item. This will provide insight into the year-to-year changes and help you with your business memo, which is the second part of this project.

2023 BUDGET ASSUMPTIONS:

Assume that the water company expects that in 2023:

a. The number of customers will increase by 8% as more people move into the local community.

b. Water sales will increase by 10% as home building accelerates to accommodate the growing local population.

c. Late fees will increase by 20% as the Utility expects low-income customers to be more likely to run behind on their water bills because of inflationary pressures on family budgets.

d. Hydrant fees will decrease by 5% because the Utility plans to take several older hydrants out of service.

e. The cost of water sold will increase by 18% because of higher chemical, electricity, and fuel costs.

f. Payroll expenses will increase by 16% because the Utility is finding it hard to find new employees to replace older employees that retired because of the Covid pandemic. Furthermore, the Utilitys insurance carrier informed the Utility that it plans to raise the cost of employee health insurance significantly in 2023.

g. Overhead expense will increase by 6% because of inflation.

h. Miscellaneous expenses are expected to decrease by 7% because of efforts to control costs.

Here are some check figures to help you out:

2023 Total Revenue=1,692,978

2023 Total Expense=1,988,285

Total Income Variance 2023 vs 2022= <$104,758>

Total Income Variance 2022 vs 2021=<$98,050>

Total Income as a percentage of total revenue in 2023= -17.44%

Total Income as a percentage of total revenue in 2022= -12.29%