(Solved): Please see attached. Thank you greatly in advance for your assistance.   Memorandum To: Boa ...

Please see attached. Thank you greatly in advance for your assistance.

 

Memorandum To: Board of Directors From: [Click here and type name] Date: 9/20/2022 Re: Financial Performance of North Central Water Company Paragraph 1: Describe in at least 4-6 sentences, minimum, which revenues and expenses most significantly contributed to the change in financial performance for the Company between 2022 and 2021. As part of your description, include descriptions of changes in accounts in terms of dollar differences and changes in percentages of water revenue. Hint: also describe the changes in the number of customers and the sales amount per customer and how these two items might be driving the financial performance of the water company. Paragraph 2: Give your recommendation about how North Central's management might improve the financial performance in 2023 versus 2022 based on your initial budget projections for 2023 (3-5 sentences minimum). What will happen in \( \mathbf{2 0 2 3} \) based on what you see in your projections? Consider how management might specifically address the trends in its revenue and expenses to increase net income. Paragraph 3: In 2020 (not shown), North Central incurred a loss. The Board of directors of Northern Central is required by law to raise customer water rates if the Company experiences three straight years of losses. Because of the losses in 2020-2022, the Board must raise rates. However, the Board cannot agree on how to raise rates. The Board has three members; two of the board members prefer to make a significant one-time rate increase that might be sufficient to stabilize the Utility's financial performance for the next five years. The Utility will invest any excess funds and use the earnings to effectively save the Utility's customers more money than it would if it raised rates yearly. On the other hand, the third board member prefers to raise rates yearly over the next five years, arguing that it is not ethical to subject the Utility's lower-income customers to higher water rates with inflation currently at high levels. However, this plan will not generate as much extra income during the next five years as the other directors' plan. The board members cannot agree and have asked for your opinion. Which alternative seems best to you and why? Is one more ethical than the other? Do you have an alternative plan? Describe in 4-6 sentences minimum. cel Analysis (Please submit your answers with the excel file provided in Ilearn entitled th Central Financial Results- Student Copy." I on the financial results provided above, complete the excel spreadsheet file provided a and submit your file in Ilearn. Please include your name in the filename. You must ormulas in the excel spreadsheet rather than typing-in calculated numbers to get full t. You will run into rounding errors unless you use formulas. The spreadsheet already des some formulas and calculated numbers to help you. YOU SHOULD HAVE AN WER 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 revenue's 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 Company's 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 Utility's 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 \( \langle \) loss \( \rangle \) Variance 2023 vs \( 2022=\langle \$ 104,758\rangle \) Total Income \( \langle \) loss \( \rangle \) Variance 2022 vs \( 2021=\langle \$ 98,050\rangle \) Total Income \( \langle \) loss \( > \) as a percentage of total revenue in \( 2023=-17.44 \% \) Total Income 4 loss \( > \) as a percentage of total revenue in 2022= \( -12.29 \% \) II. Business Memo: In the second part of the project, you will need to write a small memo detailing the difference in the financial results between 2022 and 2021. To complete this part of the project, download and open the word file called "North Central Business Memo." In this memo, you should describe how management might be able to increase net income in 2023 based on your budget projections. You will also be asked to offer your opinion about an ethical situation faced by Company's management. Your memo should be single-spaced, and you should use times new roman style with a font size of 12 . When finished, upload your word file into Ilearn. Please include your name in the filename. If you have not signed up for your free Grammarly account, you should do so before completing this project. For the link, go back and check your College of Business emails from the beginning semester. III. Grading: The project is worth 50 points. The business memo is worth 20 points based on Grammarly and the teacher's scores. The excel file is worth 30 points, of which 15 points are for accurate and consistent formatting, and 15 points are for formula accuracy.