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%

8. You are tasked with trying to restore the biodiversity in a national park. How might your understanding of keystone species affect your management plans?

9. Earth can support more people who are vegetarians than people who regularly consume meat. Why do you think this is?

10. If only 10% of the energy available in a plant is turned into body tissue of a cow, what happens to the other 90%?

11. Which do you think would be a more stable ecosystem: one where each species has only one connection to another species, or one where each species has five connections to other species? Explain your answer.

12. Which community would support the greatest diversity of species—a community composed of species with broad ecological niches or a community composed of species with narrow, specialized ecological niches? Explain your answer.

You have been recruited by a former classmate, Susanna Wu, to join the finance team of a company that she founded recently. The company produces a unique product line of hypoallergenic cosmetics and relies for its success on an aggressive marketing program. The company is in a start-up phase and therefore has no significant history of expenses and revenues upon which to rely for budgeting and planning purposes. Given the restriction on available funds (most of the available capital has been used for new-product development and to recruit a management team), the control of costs, including marketing costs, is thought by the management team to be essential for the short-term viability of the company.

You have held a number of intensive discussions with Susanna and John Thompson, director of marketing for the firm. They have asked you to prepare an estimated budget for marketing expenses for a month of operations.

You are provided with the following data, which represent average actual monthly costs over the past three months:

Your discussions with John and Susanna indicate the following assumptions and anticipated changes regarding monthly marketing expenses for the coming year:

• Sales volume, because of aggressive marketing, should increase by 14%.
• To meet competitive pressures, sales prices are expected to decrease by 7%.
• Sales commissions are based on a percentage of sales revenue.
• Sales staff salaries, because of a new hire, will increase by 14%, regardless of sales volume.
• Because of recent industrywide factors, rates for telephone and mailing costs, as well as delivery charges, are expected to increase by 10%. However, both of these categories of costs are variable with sales volume.
• Rent on the office building is based on a 2-year lease, with 18 months remaining on the original lease.
• Gas utility costs are largely independent of changes in sales volume. However, because of industrywide disruptions in supply, these costs are expected to increase by 15%, regardless of changes in sales volume.
• Depreciation on the office furniture used by members of the sales staff should increase because of new equipment that will be acquired. The planned cost for this equipment is $25,200, which will be depreciated using the straight-line (SL) method, with no salvage value, over a 3-year useful life.
• Because of competitive pressure, the company plans to increase the cost of marketing consultants by $5,000 per month.

Required:

1. Based on the preceding information, what is the percentage change, by line item and in total, for items in your budget?

2. The management team is worried about the short-term financial position of the new company. Given the strain on available cash, the president has expressed a desire to keep marketing expenses over the next few months to a maximum of $382,000. Discussions with the marketing department indicate that telephone and mailing costs are the only category, in the short run, that can reasonably bear the planned-for reduction in marketing costs. The budget you have prepared includes an assumed 10% increase in telephone and mailing costs. What must this percentage change (positive or negative) be in order to achieve targeted monthly marketing costs? (Hint: The Goal Seek function in Excel can be used to calculate the percentage changes, which can be found under Data, then What-If Analysis.)

Assume the economy has a GDP of $11,500 billion. The unemployment rate is at 7.3% and has been slowly rising for the last 6 months. Inflation was at 2.3% one year ago but has since dropped to near 0%. The MPC in the economy is .80 and the Natural Rate of Unemployment is 4.5%. The target rate for inflation is 2%.

1. Why would it be better for government to solve the problem using government purchases (part c above) rather than taxes (part d above) to solve the problem?
2. Assuming that government does decide to use the tax policy, what could happen to cause the policy to be ineffective?

Trace metals found in wells affect the taste of drinking water, and high concentrations can pose a health risk. Researchers measured the concentration of zinc (in milligrams/liter) near the top and the bottom of 7 randomly selected wells in a large region. The data are provided in the following table. Construct and interpret a 90% confidence interval for the true mean difference (Bottom – Top) in the zinc concentrations of the wells in this region.

A. The electric potential at a position located a distance of 19.2 mm from a positive point charge of 7.80×10^-9C and 13.0 mm from a second point charge is 1.14 kV. Calculate the value of the second charge.

B. The potential difference between two parallel conducting plates in vacuum is 590 V. An alpha particle with mass of 6.50×10^-27 kg and charge of 3.20×10^-19 C is released from rest near the positive plate. What is the kinetic energy of the alpha particle when it reaches the other plate? The distance between the plates is 45.0 cm.

Closing is subject to buyer’s inspection of property by him and his designated specialist and complete satisfaction of specialist with property condition. Aherns’s specialist inspected the property and said he thought the roof would need to be replaced in a few years. He also felt some wood near the front of the cabin should be replaced. Aherns demanded that the Johnsons fix these items or he would not close. The Johnsons maintain they did not agree to any repairs; the condition was for Aherns’ protection so that he could opt out of the agreement. Aherns has threatened to sue for breach of contract. Who should win?

Determine the chimney height required to preserve design flow in a natural recirculation boiling water reactor channel with the following characteristics:

System pressure: 7.136 MPa

Reactor height 1.83 m

Feedwater temperature 260 Celsius

Combined pressure in losses in the channel, chimney, and downcomer: 1344.5 Pa

Average channel density in the core: 642.9 kg / m^3

Two-phase friction losses in the chimney 137.9 Pa

Friction losses in the downcomer: 68.9 Pa

Friction drop in channel: 606.7 Pa

Average exit void fraction: 44%

Feedwater added near the top of chimney