Discounted Cash Flow Analysis (Model) Excel Case:

Your employer is considering a capital project that involves installing a new manufacturing facility(to manufacture a new product) at a cost of $30,800,000. The facility will be built on land that was purchased in 2018 for $1,250,000. If the facility is not built on this land, the land will remain unused. The new manufacturing facility, if built, will be depreciated on a straight-line basis over five years, to a salvage value of $2,000,000. If the facility is built, the production there will cause an immediate increase in Inventory of $1,300,000. It will also cause immediate increases in Accounts Receivable of $5,900,000, Accounts Payable of $850,000, and Long-Term Debt of $15.2million.

If built and produced, the new product is expected to generate annual sales of $20,375,000 by the end of the first year. Sales are expected to increase 8% per year. COGS expense is expected to be of $9,780,000 during the first year. Thereafter, COGS is expected to remain at a constant percentage of Sales. Because operating efficiency is expected to improve each year, SG&A expense is expected to remain at $3,750,000 for each of the five years of the project. At the end of the project’s five-year life, production will cease, and the manufacturing facility will be sold for an estimated $4,500,000. At that time, Inventory, Accounts Receivable and Accounts Payable will return to their pre-project levels.

If the project is implemented, it will likely increase sales of the company’s existing complimentary products. The net impact of those sales is expected to be a $2,225,000 annual increase in pre-tax profits.

Your employer’s tax rate is 21%. The firm has 5 million shares of common stock outstanding. The firm requires a 11% rate of return on capital projects of this risk.

Prepare a discounted cash flow analysis to determine whether your employer should implement this capital project. Your analysis should reveal answers to each of the following questions. Clearly label all cells. Highlight the cells that answer the following questions:

1. What is the initial investment amount (Year 0)?
2. What are the total cash flows each year (Years 1-5)
3. What is the NPV?
4. What is the IRR?

An accountant for a large department store has the business objective of developing a model to predict the amount of time it takes to process invoices. Data are collected from the past 32 working days, and the number of invoices processed and completion time (in hours) are shown below. (Hint: First determine which are the independent and dependent variables.) Use Excel:

• Assuming a linear relationship, create a regression model to compute the regression coefficients b₀ and b₁.
• Interpret the meaning of the Y intercept, b₀, and the slope, b₁. What does the slope tell you about the relationship between number of invoices processed and completion time?
• Use the prediction line developed to predict the mean amount of time it would take to process 150 invoices. Would you use the regression model to predict the amount of time it would take to process 350 invoices? Why?
• What is the coefficient of determination, r² ? Interpret its meaning.
• Plot the residuals against the number of invoices processed and also against time. Based on these plots, does the model seem appropriate?

A sample of 31 people took a written driver’s license exam. Two variables were measured on them: The result of the exam (0 = fail, 1 = pass), and how much time (in hours) the person studied for the exam. Using the data, fit an appropriate regression model to determine whether time spent studying is a useful predictor of the chance of passing the exam. Formally assess the overall fit of the model. Formally assess whether time spent studying is a useful predictor (as always, providing numerical justification (test statistic and P-value) for your conclusion). Carefully interpret what the estimated model tells you about how the chance of passing the exam changes as the time spent studying changes. A prospective examinee named Matthew spent 3.0 hours studying for his written exam. Estimate (with a point estimate and with a 90% interval) his probability of passing the exam. Based on this estimate, predict whether he will pass or fail.

SAS code:

DATA three;
INPUT result hours;
/* result=0 is fail; result=1 is pass */
cards;
0 0.8
0 1.6
0 1.4
1 2.3
1 1.4
1 3.2
0 0.3
1 1.7
0 1.8
1 2.7
0 0.6
0 1.1
1 2.1
1 2.8
1 3.4
1 3.6
0 1.7
1 0.9
1 2.2
1 3.1
0 1.4
1 1.9
0 0.4
0 1.6
1 2.5
1 3.2
1 1.7
1 1.9
0 2.2
0 1.3
1 1.5
;
run;

Question 3

Again ignore any trend or seasonality in the data.  Suppose the company uses exponential smoothing to make forecasts.  

1. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.3. Assume that the forecast for Q1 Year 1 was 25 units.
2. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.8.  Assume that the forecast for Q2 Year 1 was 25 units.
3. Compare the accuracies of the forecasts in (a) and (b) using Mean Absolute Percent Error.  Which value of alpha gives us the better forecasts?

Question 4

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. Use MAPE to calculate the accuracy of your forecasts.

Question 5

1. Compare the preferred method in Question 2, the preferred method in Question 3, and the method in Question 4.  Which one would you choose on the basis of MAPE?
2. Using the method in Question 4 make forecasts for the 4 quarters of Year 6.

1. The greenwood hotel invested a single amount of 100,000 at 8% for 10 years. Assume annual compounding. A. How much interest will be earned over the first five years? B. How much interest will be earned over the last five years? C. What will be the total amount of the investment after 10 years?

Needs to document an amusement park ride. For example: Round rotating cylinder where the floor drops out. The information can come from any online website. Needs to know the radius and speed at which the ride needs to rotate so that you don't fall?

Calculate the coefficient of static friction for this ride also

Based on recent data, there are on average 1.3 days per winter where snowfall reaches more than 6 inches in Central Park, New York City. We’ll call these “snow days”.

Assume that there were more than 2 “snow days” this winter. What is the chance that exactly 4 such days occur?

A tourist from the US spends three nights in a hotel in Amsterdam Which statement(s) is (are) true?

a. This increases GDP of the Netherlands and decreases net exports of the US.

b. This increases GDP of the Netherlands and increase consumption of the US.

c. This increases consumption of the US, but decreases net exports of the US.

d. All of the above are correct

The food and beverage department and its housekeeping counterpart employ the vast majority of the employees in a full-service hotel. These departments are very labor-intensive because it is not practical to use technology to replace human workers in these areas. To what extent do you think technology will replace employees in the food and beverage department in the future? Why?

In the list below tell me whether it’s a private good, public good, common resources or club resources. Explain.

Fish in Beaverkill stream in Roscoe NY

NYC Central Park
NYC water from the Catskill Mountains

Basic research on cancer drug

Radio broadcasting system

Cable television signals