Can someone please explain how (what formulas/command to use) to get the yellow and green cells? Thank you!
Allied Food Products Fruit Juice Project Capital Budgeting and Cash Flow Estimation Allied Food Products is considering expanding into the fruit juice business with a new fresh lemon juice product. Assume that you were recently hired as a financial analyst to the director of corporate development, and you must evaluate the new project. The lemon juice would be produced in an unused building adjacent to Allied's Fort Myers plant; Allied owns the building, which is fully depreciated. The purchase price of the required equipment is $280,000; shipping and installation costs would cost an additional $20,000. The equipment has a 10 year useful life and will be depreciated on a straight-line basis. They don't foresee buying any equipment other than this. Allied plans to pay for this with cash and retained earnings; they don't expect to take debt out for this project. Historically, working capital has run about 5% of sales. Unit sales are expected to total 100,000 units per year, but grow at 20%,10%,5% for each of the first 3 years, then reach a steady-state growth of 3%. The expected sales price is $2.00 per unit and is expected to keep up with inflation which you project to be 2.5% per year. Cost to produce the fruit juices are expected to total 60% of dollar sales, and sales, general and operating costs, excluding depreciation, typically runs around 15.4% for the firm. Allied's tax rate is 25%, and its WACC is 10%. Tentatively, the lemon juice project is assumed to be of equal risk to Allied's other assets. You have been asked to evaluate the project and to make a recommendation as to whether it should be accepted or rejected. Allied Food Products Fruit Juice Project Capital Budgeting and Cash Flow Estimation Allied Food Products is considering expanding into the fruit juice business with a new fresh lemon juice product. Assume that you were recently hired as a financial analyst to the director of corporate development, and you must evaluate the new project. The lemon juice would be produced in an unused building adjacent to Allied's Fort Myers plant; Allied owns the building, which is fully depreciated. The purchase price of the required equipment is $280,000; shipping and installation costs would cost an additional $20,000. The equipment has a 10 year useful life and will be depreciated on a straight-line basis. They don't foresee buying any equipment other than this. Allied plans to pay for this with cash and retained earnings; they don't expect to take debt out for this project. Historically, working capital has run about 5% of sales. Unit sales are expected to total 100,000 units per year, but grow at 20%,10%,5% for each of the first 3 years, then reach a steady-state growth of 3%. The expected sales price is $2.00 per unit and is expected to keep up with inflation which you project to be 2.5% per year. Cost to produce the fruit juices are expected to total 60% of dollar sales, and sales, general and operating costs, excluding depreciation, typically runs around 15.4% for the firm. Allied's tax rate is 25%, and its WACC is 10%. Tentatively, the lemon juice project is assumed to be of equal risk to Allied's other assets. You have been asked to evaluate the project and to make a recommendation as to whether it should be accepted or rejected
