Please give the excel formulas and steps details

Fatima Hopkins, the CEO of Central Adventures, is having difficulties with all three of her top management level employees. With one manager making questionable decisions, another threatening to leave, and the third likely ‘in the red’, Fatima is hoping there is a simple answer to all her difficulties. She is asking you (her accountant) for some advice on how to proceed.

Central Adventures owns and operates three amusement parks in Michigan: Funland, Waterworld, and Treetops. Central Adventures has a decentralized organizational structure, where each park is run as an investment center. Park managers meet with the CEO at least once annually to review their performance, where each park manager’s performance is measured by their park’s return on investment (ROI). The park manager then receives a bonus equal to 10% of their base salary for every ROI percentage point above the cost of capital.

Fatima’s first difficulty is with the Funland park. Funland is an outdoor theme park, with twelve roller coaster rides and several other attractions. This park has first opened 1965, and most of the rides have been in operation for 20+ years. Attendance at this park has been relatively stable over the past ten years. The park manager of Funland, Janet Lieberman, recently shared with Fatima a proposal to replace one of their older rides with a new roller coaster, a hybrid steel and wood roller coaster with a 90 degree, 200 foot drop and three inversions. The proposal indicated that the ride would cost $8,000,000 with an estimated life of 20 years. In addition, this new style of coaster would require additional maintenance and insurance, costing $125,000 each year. However, it projected that this new attraction would boost attendance, earning the park an additional $1,190,000 per year in revenues. Janet ultimately decided not to invest in this new attraction. Fatima (doing a quick mental calculation) saw that the investment had a payback period of eight years—much shorter than the life of the roller coaster—and is perplexed at Janet’s decision.

The second dilemma concerns the Waterworld park. Waterworld is an indoor water park, operating year-round. Run by park manager David Copperfield, Waterworld was built in 2016 and has increased attendance by 20% every year since. David recently sent you an email complaining that, based on the current bonus payout schedule, Janet Lieberman’s bonus last year was significantly higher than his. He points to the increasing attendance, and says that his park is being punished for having opened so recently (his park assets are much more recent than the roller coasters at Funland). He currently has an employment offer from another company at the same base pay rate, which he says he will accept if his performance is not appropriately acknowledged. Fatima needs to look at the relative performance across parks to determine how to proceed with David.

Central Treetops includes a high ropes course and has a series of ziplines that criss-cross over the Chippewa River. For many years, it was a popular venue for corporate team-building activities, so it is equipped with a main indoor facility with cafeteria and overnight guest rooms. This park has lost popularity in recent years, and has been ‘in the red’ for the past two years. If the park is not profitable this year, you will need to decide whether to close it - permanently. Included in the ‘Fixed COGS’ for Treetops is a $86,000 mortgage payment on the land and buildings for the park, which would still need to be paid by Central Adventures if the park is closed. Incidentally, you recently had a conversation with the regional head of the YMCA, who would like to open a summer camp in the central Michigan region. If you decided to close Treetops, you are fairly certain that you could lease that land to the YMCA for $250,000 annually.

A partial report of this year’s financial results for Central Adventures shows the following:

The ‘Selling and administrative costs’ are all incurred directly by each park, and are determined at the beginning of each year (that is, they do not change with the number of tickets sold). In addition to the information above, there are $2,542,920 in corporate costs, which are currently allocated evenly between the three parks. These costs are primarily due to employee benefits costs, which are billed at the corporate level. If the Treetops park is closed, the allocated corporate costs would decrease by $12,000. Central Adventures has a cost of capital of 12 percent (and Fatima uses the cost of capital as their required rate of return) and are subject to 18% income taxes.

Fatima needs to evaluate this year’s performance results before she can make any decisions. Is David’s complaint about the performance evaluation metrics valid? Is that also affecting management decisions in the form of Janet’s rejection of the proposed new rollercoaster? And is the company better off without Treetops? She sets off to the company accountant’s office to help get some answers.

Required:

Write your response in the form of a 1-2 page memo to Fatima Hopkins, from the perspective of the company accountant. Be sure to include all your financial analyses, clearly showing your calculations, to support your conclusions. Be sure to include the following points in your memo, and provide the appropriate financial analysis(es) to support your conclusions.

a. Evaluate Janet Lieberman’s (the Funland park manager) decision. Explain why it was/was not in Central Adventure’s overall best interest for Funland to reject the new rollercoaster.

B. Evaluate the validity of David Copperfield’s (the Waterworld park manager) complaint. Explain why it is (or is not valid), and what further information would be necessary.

Fluoride concentration in city water is heavily regulated to ensure there are no adverse long-term health effects. Suppose that a city decides to regulate its fluoride concentration at a mean level 1.0 mg/L, and the concentration of fluoride X is normally distributed with a standard deviation of 0.3 mg/L.

(a) [2 pts] Express the parameters of the distribution of X in proper notation.

(b) [2 pts] What is the probability of observing a fluoride concentration less than 0.7 mg/L?

(c) [4 pts] If ten randomly selected water samples are taken from the city’s water supply, what distribution would the sample mean fluoride concentration follow? In addition to identifying the distribution, specify any parameters for the distribution, check any conditions necessary, and explain why x ̄ follows this distribution.

(d) [2 pts] What is the probability of observing a sample mean fluoride concentration less than 0.7 mg/L in a sample of ten randomly selected water samples?

(e) [3 pts] If a sample of ten water samples results in a sample mean fluoride concentration of 1.2 mg/L, should officials be concerned that there is more fluoride in the water than they want? Explain why or why not.

The following are regression results where Car Price is the dependent variable:

Regression Statistics

?2=0.446R2=0.446 Adjusted ?2=0.441R2=0.441 Observations = 804

Car Price is measured in dollars. The independent variables are:

• Mileage: number of miles the car has been driven
• Cylinder: number of cylinders in the engine
• Liter: a more specific measure of engine size
• Doors: number of doors
• Cruise: dummy variable representing whether the car has cruise control (1 = cruise, 0 = no cruise)
• Sound: dummy variable representing whether the car has upgraded speakers (1 = upgraded, 0 = standard)
• Leather: dummy variable representing whether the car has leather seats (1 = leather, 0 = cloth)

Question 17

Not yet answered

Marked out of 1.00

Flag question

Question text

This model (set of independent variables) explains approximately how much of the variation in car prices in this dataset?

Select one:

a. 80.480.4

b. 44.144.1

c. 1−0.441=55.91−0.441=55.9

d. 44.644.6

Question 18

Not yet answered

Marked out of 1.00

Flag question

Question text

What is true about the estimated coefficients?

Select one:

a. The "Mileage" coefficient is unexpectedly small compared to the others, suggesting that miles driven is unimportant in the selling price of a used car.

b. The "Sound" coefficient is unexpectedly negative, suggesting that cars with upgraded speakers are associated with a lower selling price.

c. The negative "Door" coefficient indicates that more doors on a car reduce the car's mileage.

d. The "Mileage" coefficient is unexpectedly negative, since higher miles driven should be associated with a higher selling price.

Question 19

Not yet answered

Marked out of 1.00

Flag question

Question text

The results from the ?t-statistics and ?p-values suggests that

Select one:

a. Mileage, Liter, Doors, and Sound are all insignificant since the ?t-stats are negative.

b. Only the coefficient for "Liter" is statistically insignificant. All of the other coefficients are statistically significant at the 1% level.

c. Mileage, Cylinder, Doors, Cruise, and Leather are all insignificant since the ?p-values are zero, meaning unrelated to car price.

d. "Liter" is the only statistically significant estimate since it's ?p-value is 36.4%.

Question 20

Not yet answered

Marked out of 1.00

Flag question

Question text

Which of the following statements is correct, based on the regression results above?

Select one:

a. Every additional mile driven increases the price of the car by $0.17

b. Since the "Liter" coefficient is insignificant, the true effect is actually +787.22 and not -787.22

c. Having four doors instead of two is associated with more than a $3,000 lower price, everything else equal.

d. Cars with cruise control have about 6,300 fewer miles on them than cars without cruise control, everything else equal.

Steve is a contract carrier for the United States Postal Service. He has been hauling mail for nearly thirty years. His current contract is to haul mail between 20 cities in the eleven western states. Steve currently has a fleet of 16 tractors and employs over 20 drivers. He does not own any trailers as all of the trailers are owned by the Postal Service. Steve’s drivers drive scheduled routes between the cities.

The Postal Service has just awarded Steve an additional contract that will require Steve to purchase six new tractors. He competed aggressively for the contract and spent a total of $45,000 in costs to prepare and submit the bid. Steve has narrowed his decision of which truck to buy down to two choices. He can purchase Volvo tractors or Kenworth tractors. The Volvo tractors would cost $285,000 each where the Kenworth would only cost $255,000 each. Both models would be depreciated to zero over 5 years using straight-line depreciation.

The Postal Service contract pays Steve $2.85 per mile. Costs associated with each new tractor include wages for the drivers at $80,000 per truck per year and regular service and maintenance at a cost of $1,750 per month per truck. Fuel costs vary as Volvo is more fuel-efficient than the Kenworth. Assume the tractors will be driven 105,000 miles per year and diesel costs will average about $3.25 per gallon. The Volvo is expected to get 3.6 miles per gallon while the Kenworth will get 3.3 miles per gallon. Insurance and licensing is expected to cost $6,000 per truck per year and is the same for both trucks.

Both models will require a complete engine overhaul at 300,000 miles and Steve estimates that this will be during the third year of ownership. The cost of an overhaul on the Volvo is estimated at $45,000 per truck while the cost on the Kenworth is estimated at $52,000 per truck. All other maintenance costs are believed to be the same for each tractor.

Steve expects to keep the trucks for six years after which time he will sell them. He will not overhaul the tractors in year 6 as it will not increase their value. He predicts that he will be able to sell the Volvo’s for $60,000 each, but the Kenworth will be worth only $50,000 each. Steve’s cost of capital is 14%. The company is in the 34% tax bracket.

When Steve got started in the business his first truck was a Volvo. While they cost more Steve believes that they are a better truck and he love’s the sleek and powerful look of a Volvo. Because of this he is leaning towards buying the Volvo tractors. But after hearing that you have learned about capital budgeting in your Finance class at UVU he wants to take advantage of your expertise. Steve has asked you to analyze his choices and give him some advice on what he should do.

Prepare an analysis and professional report for Steve that includes the following items:

1.         Determine the cash flows associated with the different trucks for each year of the project.

2.         Calculate the PB period, Discounted PB, IRR, and NPV for the two alternatives. Explain to Steve what the different methods mean and how he can use them to help him make a decision.

INFO 564 Homework Assignment 5 (100 pts)

This work must be done completely in EXCEL. Answer each question on a separate tab. Label each tab appropriately. You can copy and paste the data given into an Excel worksheet.

South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years.

Question 1 (5 pts)

Plot this data with quarters from years 1-5 on the horizontal axis. What components do you see in this time series?

Question 2 (20 pts)

Ignore any trend or seasonality in the data.

1. Suppose the company uses moving averages to make forecasts. Make forecasts all the way through Q4 Year 5. Assume the company uses (i) 3-quarterly moving averages and (ii) 4-quarterly moving averages.
2. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – 3 quarterly moving average or 4 quarterly moving average?
3. On a line chart plot the time series along with the forecasts from the method you select in (b).

Question 3 (20 pts)

Ignore any trend or seasonality in the data.

1. Suppose the company uses weighted moving averages to make forecasts. Make forecasts all the way through Q4 Year 5. Assume the company uses (i) 3-quarterly moving averages with weights 0.6, 0.3, and 0.1 and (ii) 4-quarterly moving averages with weights 0.4, 0.3, 0.2, and 0.1. In both cases the most weight is given to the most recent quarter and the least to the oldest quarter in the moving average.
2. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – 3 quarterly weighted moving average or 4 quarterly weighted moving average?
3. On a line chart plot the time series along with the forecasts from the method you select in (b).

Question 4 (20 pts)

Again ignore any trend or seasonality in the data.

1. Suppose the company uses exponential smoothing to make forecasts. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming (i) alpha = 0.3 and (ii) alpha = 0.7? In both cases assume that the forecast for Q1 Year 1 was 25 units.
2. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – alpha of 0.3 or alpha of 0.7?
3. On a line chart plot the time series along with the forecasts from the method you select in (b)

Question 5 (20 pts)

Now make adjustments for trend and seasonality.

1. Quantify the trend in the time series. What does the trend equation tell you?
2. Quantify the seasonality in the time series by calculating seasonality indexes. What do these indexes tell you?
3. Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5.
4. Calculate the Mean Absolute Percent Deviation for the forecasts in (c).
5. On a line chart plot the time series along with the forecasts from (c).

Question 6 (15 pts)

Using the most accurate method of all of the above,

1. Make forecasts for the four quarters of Year 6.
2. Plot these forecasts on the same line chart as the time series.
3. Summarize in a few lines your findings from your answers to Q1 through Q6b. I really need answer to question 4 and 5 please. Thank you.

Case:

Rent Relief Caravans4Hire Ltd1 provides short-term rental of caravans to tourists for camping holidays throughout Australia. Caravans4Hire Ltd leases several large properties in Adelaide, Perth and Sydney, which it needs to park its caravans when not in use. Due to border restrictions, travel restrictions, localised lockdowns and Government advice to stay home, Caravans4Hire Ltd has suffered a significant loss of revenue and cash flow. On 1 May 2020 the National Hotel and Tourism Industry Association which is a non-government, not-for-profit industry association. It supports its members, who are businesses operating in the hospitality and tourism industry awarded Caravans4Hire Ltd a grant of $360 000 in total for rent relief for the three months ended 31 July 2020. The grant was received in cash on 1 May 2020. Caravans4Hire Ltd is under no obligation to repay the money received. REQUIRED All questions should be answered from the perspective of Caravans4Hire Ltd. The word lengths are a suggestion only, i.e., they are NOT strict word limits for each part.

a) What is the main accounting policy issue(s) that need to be resolved to account for the grant from the National Hotel and Tourism Industry Association? (20%) (part a) 15 – 50 words)

b) i) Identify one principle that is relevant to the accounting policy issue that you identified in part a) by providing a reference for that principle (e.g., Conceptual Framework, Chapter X, para. x.xx) AND explain why you chose that principle. (20%)

ii) identify another principle that is relevant to the accounting policy issue that you identified in part a) by providing a reference for that principle.(10%) (part b) 50 – 100 words).

c) Describe an accounting policy to account for the grant from the National Hotel and Tourism Industry Association. Do not justify your policy. Just describe it. (50%) (part c) 20 - 80 words)

FINANCIAL LEVERAGE EFFECTS

The Neal Company wants to estimate next year's return on equity (ROE) under different financial leverage ratios. Neal's total capital is $12 million, it currently uses only common equity, it has no future plans to use preferred stock in its capital structure, and its federal-plus-state tax rate is 40%. The CFO has estimated next year's EBIT for three possible states of the world: $5.7 million with a 0.2 probability, $2.5 million with a 0.5 probability, and $0.3 million with a 0.3 probability. Calculate Neal's expected ROE, standard deviation, and coefficient of variation for each of the following debt-to-capital ratios. Do not round intermediate calculations. Round your answers to two decimal places at the end of the calculations.

Debt/Capital ratio is 0.

Debt/Capital ratio is 10%, interest rate is 9%.

Debt/Capital ratio is 50%, interest rate is 11%.

Debt/Capital ratio is 60%, interest rate is 14%.

The Neal Company wants to estimate next year's return on equity (ROE) under different financial leverage ratios. Neal's total capital is $14 million, it currently uses only common equity, it has no future plans to use preferred stock in its capital structure, and its federal-plus-state tax rate is 40%. The CFO has estimated next year's EBIT for three possible states of the world: $4.4 million with a 0.2 probability, $2.8 million with a 0.5 probability, and $0.3 million with a 0.3 probability. Calculate Neal's expected ROE, standard deviation, and coefficient of variation for each of the following debt-to-capital ratios. Do not round intermediate calculations. Round your answers to two decimal places at the end of the calculations.

Debt/Capital ratio is 0.

Debt/Capital ratio is 10%, interest rate is 9%.

Debt/Capital ratio is 50%, interest rate is 11%.

Debt/Capital ratio is 60%, interest rate is 14%.

The Neal Company wants to estimate next year's return on equity (ROE) under different financial leverage ratios. Neal's total capital is $14 million, it currently uses only common equity, it has no future plans to use preferred stock in its capital structure, and its federal-plus-state tax rate is 40%. The CFO has estimated next year's EBIT for three possible states of the world: $5.7 million with a 0.2 probability, $1.5 million with a 0.5 probability, and $0.3 million with a 0.3 probability. Calculate Neal's expected ROE, standard deviation, and coefficient of variation for each of the following debt-to-capital ratios. Do not round intermediate calculations. Round your answers to two decimal places at the end of the calculations.

Debt/Capital ratio is 0.

RÔE = %

σ = %

CV =

Debt/Capital ratio is 10%,interest rate is 9%.

RÔE = %

σ = %

CV =

Debt/Capital ratio is 50%,

interest rate is 11%.

RÔE = %

σ = %

CV =

Debt/Capital ratio is 60%, interest rate is 14%.

RÔE = %

σ = %

CV =

Continue without saving