Build an EXCEL model for the Earthlizer problem above such that the model can automatically calculate the project’s NPV for any value of the input parameters below (within the range specified below). All other relevant information remains unchanged.

CSM, Inc., is considering a 5-year investment in Earthilizer, a new wholly organic fertilizer made from dairy farm waste. This fertilizer was developed by CSM’s ag-division over the past 3 years in response to growing demand from organic farmers for a cheaper alternative and due to increased restrictions on the disposal of cattle manure. The product has undergone extensive testing and satisfies all the regulatory parameters.

If the top management team at the corporate office gives the final go ahead, the project will be “up and running” by the end of 2019 (end of this year), with first year of sales in 2020. It will require an initial investment in property, plant and equipment (PPE) of $330K (in 2019). The assets have a 10-year life and will be depreciated to zero salvage value over that time on a straight-line basis. The company expects that it can sell the assets at its book value when the project ends at year-end of 2024.

In the first year (2020), sales are expected to be $1 million. Further, they initially assume a conservative project sales growth rate of 0% throughout the 5-year period (till 2024). The COGS is expected to 67.40% of sales and Operating expenses except for depreciation are expected to be 10% of sales (variable expenses) plus an annual fixed expense of $115K.

The project’s operating working capital requirement at the end of each year will depend on the project sales of next year. Inventory and Cash requirements will be 20% & 5% of project sales respectively while Accounts Receivables and Account payable will be each be 10% of project sales. Finally, the company estimates that the project will have tax rate of 30%, will be funded entirely with equity capital, and have a cost of capital of 13.25%.

 

Transcribed Image Text:

Table 1: Range of Values each input parameter can take (all numbers in thousands) Input Parameter Range Yearly Sales Growth Initial Sales (i.e., Year 2020 Sales) COGS (% Sales) Operating Expenses (% Sales) Fixed Operating Expenses Tax Rate NOWC required (% Sales) Initial new PPE (CAPEX) required Discount Rate Min 0% $ 500 72.0% 5.0% $58 21% 12.50% $ 281 9.94% Max 30% $ 1,500 60.0% 15.0% $ 173 39% 37.50% $380 16.56% Once the model is built, your task is to complete a type of sensitivity analysis (Tornado Analysis). To do so, you should first compute the projects' NPV, with each variable's value = its base value specified in Table 2 below. Table 1: Range of Values each input parameter can take (all numbers in thousands) Input Parameter Range Yearly Sales Growth Initial Sales (i.e., Year 2020 Sales) COGS (% Sales) Operating Expenses (% Sales) Fixed Operating Expenses Tax Rate NOWC required (% Sales) Initial new PPE (CAPEX) required Discount Rate Min 0% $ 500 72.0% 5.0% $58 21% 12.50% $ 281 9.94% Max 30% $ 1,500 60.0% 15.0% $ 173 39% 37.50% $380 16.56% Once the model is built, your task is to complete a type of sensitivity analysis (Tornado Analysis). To do so, you should first compute the projects' NPV, with each variable's value = its base value specified in Table 2 below.

Answer rating: 100% (QA)

The Excel model should be able to calculate the projects NPV for any value of t... View the full answer