Build a spreadsheet in excel to analyze the cash flows

Assignment Help Finance Basics

Reference no: EM131549608

The Pear Case - Burton Flynn

In the write-up state carefully any assumptions that you make in order to be able to solve the problem.

The Pear Company is thinking of building a new plant to put the pears it grows into cans. The plant is expected to last for 20 years. Its initial cost is $60 million. This cost can be depreciated over the full 20- year of the plant using straight line depreciation. It will require a major renovation which will cost $8 million in real terms after 12 years. This cost of renovation can be depreciated also using straight line depreciation over the remaining 8 years of the plan's life. The land the plant is built on could be rented out for $600,000 a year in nominal terms for 20 years. The salvage value of the plant at the end of the 20 years is $5 million in nominal terms. There is no salvage value with regard to the renovation.

The plant could be able to produce 60 million cans of pears a year. The price of a can of pears is currently $0.60. It is expected to grow at a rate of 2% per year in real terms for 8 years and then at 0% in real terms for the reminder of the plant's life. The firm expects to be able to sell all the cans of pears it can produce. The pears the firm puts in the cans are grown in the firm's own orchards. If the pears were not canned they could be sold to supermarkets. The current price they could obtain per pear is $0.15. This price is expected to grow at a rate of 3% in real terms for 5 years and then at 2% in real terms for the next five years and finally at 1% in real terms for the reminder of the plant's life. Each can requires pears to fill it. The raw materials for the cans currently cost $0.05 per can. These costs are expected to remain constant in real terms. The labor required to operate the plant costs a total of $7 million a year in real terms. Initially, the plant will require an additional $10 million of inventory, $10 million of accounts receivable, and $5 million of accounts payable. Working capital is expected to remain constant in real terms.

The rate of inflation is expected to be 3% per year for the next six years and 2% per year for the remainder of the plant's life. The firm's total tax rate including local taxes is 40%. The firm expects to make substantial profits on its other operations so that if it can offset any losses on the pear canning plant for tax purposes. Its opportunity cost of capital for projects of this type is 12% in nominal terms.

1. Build a spreadsheet in Excel to analyze the cash flows for this project. Should the firm build the plant? Why?

2. Please explain what was done and state carefully any assumptions that you make in order to be able to solve the problem.

3. After completing your analysis in nominal terms, build a second spreadsheet in real terms (in a another tab in the same file). The value of the real and nominal NPVs should be the same.

4. Which assumptions stated in the case (or your own) is the attractiveness of this project most heavily dependent on? How sensitive are they?

5. How much of the value of the project is due to the depreciation tax shield? If this seems high or low, what factors influence it?

Reference no: EM131549608

Questions Cloud

Determine the patients psychological-existential framework : It revolves being culturally competent and by that understanding religion, mental health, and the combination of both by identifying religious competence.

What is the market price of your preferred stock : The required rate of return on similar issues of preferred stock is 6% What is the market price of your preferred stock

Find the distribution of nx : Let Nt,t=0 be a Poisson process with intensitya. Let X be a random variable, independent of the Poisson process Nt, meaning that X is independent.

Why is the change being made to icd-10 : When was it mandated to begin? Why were there delays? Why is the change being made to ICD-10? What are the advantages of this change?

Build a spreadsheet in excel to analyze the cash flows : FINAN 3040-002 - Summer 2017 - Build a spreadsheet in Excel to analyze the cash flows for this project. Should the firm build the plant? Why?

How do older african americans males with a diagnosis : How do older African Americans males (P) with a diagnosis of (HIV) human immunodeficiency virus (I) in rural southern United States.

Discuss the analytical hierarchy process : This is business optimisation assessment which aims is to assess your methods for identifying and solving a Multi-Criteria Decision Making problem.

What is the effectiveness of using alcohol-based solution : What is the effectiveness of using alcohol-based solution in hand hygiene based on CDC guidelines in reducing nosocomial infection.

Traditional systems development life cycle : Describe the self sourcing process as an alternative to the traditional systems development life cycle.

Reviews

len1549608

6/30/2017 5:45:55 AM

For extra credit, please develop slides that you would be willing to present to the class if you’re chosen. The slides should focus on what you did and what you learned and should take no more than 10 minutes to present. For preparing the slides and being willing to present, I will award 5 pts extra. If you end up presenting I will award an additional 10 pts.

6/30/2017 5:45:45 AM

need total 2 excel file of spreadsheet, also, need explanations about question number 2, 4, and 5 as well. There are two cases to choose from. This case is Option A. It is worth 100 pts and will count for 5% of the course grade. This case has an additional 21 pts extra credit potential. It is due on 2 at 11:59pm. It should be uploaded to the Canvas site. The case should be done in groups of 2-4 people with groups of 2 or 3 being strongly preferred. Each group should include one Excel file containing the spreadsheets developed to solve the case and a brief 1-2 page write-up explaining what was done and answering the questions at the end of the case. In the write-up state carefully any assumptions that you make in order to be able to solve the problem. I strongly prefer short answers to lengthy ones (more words will not equate to more points; bullet points are fine).

Write a Review

Required(*) Message

User Account

All Pages