Question

The North Central Water Company has finalized its financial statements for the 2019 financial year. The Company's board of directors has asked you, their cost accountant, to look at the financial results and to compare the financial performance for the 2019 fiscal year to the results of the 2018 financial year. The board would also like you to project the revenues and expenses for the 2020 financial year based on several key assumptions. They have asked you to submit an excel file containing the financial results and budget projections as well as a one page memorandum of your findings.

Financial Results:

Total Number of Customers       26,000 25,000

2019    % of Total Revenues   2018    % of Total Revenues

Revenues:

Water Sales $1,162,000 ? $1,200,000 ?

Late Fees 87,000 ? 68,000 ?

Fire Hydrant Fees    114,500 ? 122,000 ?

  Total Revenues $1,363,500 100% $1,390,000 100%

Expenses:

Cost of Water Sold $512,000 ? $278,000 ?

Payroll Expense 608,000 ? 450,000 ?

Overhead Expense 292,050 ?    200,000 ?

Miscellaneous Expenses 64,075. ? 78,000    ?

   Total Expenses $1,476,125 ? $1,006,000    ?

Net Income (Loss) <$112,625> ?    $ 384,000 ?

I. Excel Analysis (Please submit your answers with the excel file provided for you in Ilearn entitled "North Central Financial Results- Student Copy".

Based on the financial results provided above, complete the excel spreadsheet file provided to you and submit your file in Ilearn. Please include include your name in the filename.

.

Required:

1. Calculate each revenue and expense item as a percentage of total revenues in 2019 and 2018 (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 spreadsheet).

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

3. Calculate the company's budgeted financial performance for 2020 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 2019 and 2018. Then calculate the water sales per customer for 2020 just as you did for 2019 and 2018- see examples in spreadsheet.

4. Finally, calculate the differences in each revenue and expense item between 2020 and 2019, and 2019 and 2018- see example in spreadsheet. This will provide you with some insight about the year-to-year changes and help you with your business memo which is the second part of this project.

You must use formulas in the excel spreadsheet rather typing-in calculated numbers to get

full credit. You will also run into rounding errors unless you use formulas. Some formulas

and calculated numbers have already been included in the spreadsheet to help you. YOU

SHOULD HAVE AN ANSWER WHEREVER YOU SEE A QUESTION MARK (?)

ASSUMPTIONS:

Assume that the water company expects that in 2020:

a. The number of customers will increase by 5%.

b. Water sales will increase by 4% and late fees will increase by 1% due to increased customer demand.

c. Hydrant fees will decrease by 1% because several older hydrants will be taken out of service.

d. The cost of water sales will increase by 8% because of higher chemical costs.

e. Payroll expenses will increase by 5.5% due to wage increases and higher medical

insurance expenses.

f. Overhead expense will decrease by 4% because of efforts to reduce costs.

g. Miscellaneous expenses are expected to double because of the purchase of building supplies in anticipation of a major waterline project in 2020.

Here are some check figures to help you out:

2020 Total Revenue=1,409,705

2020 Total Expense=1,602,918

2020 Water Sales per customer= $44.27

Total Income<loss> 2020 vs 2019= <$80,588>

Total Income<loss> 2019 vs 2018=<$496,625>

Total Income <loss> as a percentage of total revenue in 2020=-13.71% Total Income <loss> as a percentage of total revenue in 2019=-8.26%

Answer 1

Answer to Part 1.

Total number of customers 26000 25000

2019 % of total revenues 2018 % of total revenues

REVENUE:

Water sales 11,62,000 85.22% 12,00,000 86.33%

Late fees 87,000 6.38% 68,000 4.89%  

Fire hydrant fees 1,14,500 8.40% 122,000 8.78%

Total Revenues 13,63,500 100%   13,90,000 100%

EXPENSES:

Cost of water sold   5,12,000 34.68%    278,000 27.63%

Payroll expenses   6,08,000 41.19%   4,50,000 44.73%

Overhead expenses 2,92,050 19.78% 2,00,000 19.88%

Misc.expenses 64,075. 4.35%   78,000 7.76%

Total Expenses 14,76,125 100% 10,06,000 100%

NET INCOME /LOSS       1,12,625 8.26% 3,90,000 28.08%   

ANSWER TO PART 2: 2019 2018

Water sales   1,162,000 1,200,000

Total number of customers 26,000 25,000

Water sales per customer 44.69 48

ANSWER TO PART 3:

Total number of customers 26000 25000

2019 2018 Difference

REVENUE:

Water sales 11,62,000 12,00,000 -3.27%

Late fees 87,000   68,000 21.84%

Fire hydrant fees 1,14,500    122,000 -6.55%

Total Revenues 13,63,500 13,90,000 -1.94%

EXPENSES:

Cost of water sold   5,12,000     278,000 45.70%

Payroll expenses   6,08,000 4,50,000 25.99%

Overhead expenses 2,92,050   2,00,000 31.53%

Misc.expenses 64,075.    78,000 -21.73%

Total Expenses 14,76,125    10,06,000 31.85%

NET INCOME /LOSS       1,12,625 8.26% 3,90,000 28.08%