Answered step by step
Verified Expert Solution
Question
1 Approved Answer
please help me to solve this question using Excel software. with formulas 1. Chicago Turkey is considering building a new turkey farm to service their
please help me to solve this question using Excel software. with formulas
1. Chicago Turkey is considering building a new turkey farm to service their western region stores. The stores currently require 500,000 turkeys per year, and they are purchased from various local turkey farms for an average price of $7 per bird. The managers of Chicago Turkey believe that their new farm would lower the cost per bird to $6, while maintaining their average selling price of $10 per bird. However, due to the centralized structure of this operation, shipping expenses will increase to $1.50 per bird from the current average of $1.00. In addition, the firm will need to increase its inventory of live turkeys by $12,000. It is estimated that it will cost $100,000 to purchase the land, and $300,000 to construct the buildings and purchase equipment. In addition, labor expenses will rise by $120,000 per year. The buildings and equipment will be depreciated using the straight line method over five years to a salvage value of $100,000. At the end of five years the company will sell the farm for $300,000 ($100,000 for the buildings and equipment and $200,000 for the land). Assume that the firm's marginal tax rate is 40%, and note that land is not depreciable. a. Calculate the initial outlay, annual after-tax cash flows, and terminal cash flow for this project. b. If the WACC is 12%, calculate the payback period, discounted payback period, NPV, PI, IRR, and MIRR. c. The managers of Chicago Turkey are uncertain about several of the variables in your analysis and have asked you to provide three different scenarios. Create a scenario analysis showing the profitability measures for this investment using the information in the table below. (Note: The salvage value of the buildings is the actual forecasted salvage value, not the salvage value used for depreciation.) Scenario Best Case Expected Case Worst Case Labor Expense $100,000 120,000 140,000 Salvage Value of Buildings $150,000 100,000 50,000 Salvage Value of Land $300,000 200,000 100,000 1. Chicago Turkey is considering building a new turkey farm to service their western region stores. The stores currently require 500,000 turkeys per year, and they are purchased from various local turkey farms for an average price of $7 per bird. The managers of Chicago Turkey believe that their new farm would lower the cost per bird to $6, while maintaining their average selling price of $10 per bird. However, due to the centralized structure of this operation, shipping expenses will increase to $1.50 per bird from the current average of $1.00. In addition, the firm will need to increase its inventory of live turkeys by $12,000. It is estimated that it will cost $100,000 to purchase the land, and $300,000 to construct the buildings and purchase equipment. In addition, labor expenses will rise by $120,000 per year. The buildings and equipment will be depreciated using the straight line method over five years to a salvage value of $100,000. At the end of five years the company will sell the farm for $300,000 ($100,000 for the buildings and equipment and $200,000 for the land). Assume that the firm's marginal tax rate is 40%, and note that land is not depreciable. a. Calculate the initial outlay, annual after-tax cash flows, and terminal cash flow for this project. b. If the WACC is 12%, calculate the payback period, discounted payback period, NPV, PI, IRR, and MIRR. c. The managers of Chicago Turkey are uncertain about several of the variables in your analysis and have asked you to provide three different scenarios. Create a scenario analysis showing the profitability measures for this investment using the information in the table below. (Note: The salvage value of the buildings is the actual forecasted salvage value, not the salvage value used for depreciation.) Scenario Best Case Expected Case Worst Case Labor Expense $100,000 120,000 140,000 Salvage Value of Buildings $150,000 100,000 50,000 Salvage Value of Land $300,000 200,000 100,000Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started