JD Sdn Bhd

JD Sdn Bhd has developed a new industrial detergent that can be used in motor vehicle garages. It would cost RM1 million to buy the equipment necessary to manufacture the blenders, and initially it would require net operating working capital equal to 15% of the 1st year sales amount. Net operating working capital will remain at the same rate. The project would have a life of 5 years. If the project is undertaken, it must be continued for the entire 5 years.

The firm believes it could sell 100,000 units per year. The detergents would sell for RM12 perunit. After the first year, JD intends to increase the sales price by 3% annually.

The variable cost is RM5 per unit and will increase at the inflation rate of 3%. The companys fixed costs would be RM420,000 at Year 1 and would also increase at a rate of 3% annually.

The equipment would be depreciated over a 5-year period, using the straight-line method. The annual depreciation will be calculated based on a salvage value of the equipment at the end of the projects 5-year life of RM200,000. The company however estimated the machine can be sold as scrap for RM250,000. The corporate tax rate is 25%.

The cost of capital is 12%.

a. Develop a spreadsheet model and use it to find the projects NPV, IRR, and payback.

b. Conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, number of units sold, the variable costs per unit, fixed costs and the cost of capital. Set these variables values at 10% above and 10% below their base-case values. Include a graph in your analysis.

c. Conduct a scenario analysis. Assume that the best-case condition is with no increase in the sales price, a 5% increase in the number of units sold, and a 3% decrease in the variable cost per unit. All other variables remain the same. For the worst-case condition, there will be a 5% decrease in units sold, a 2% decrease in unit price and a 3% increase in the variable cost per unit. The best-case condition, worst-case condition, and the base case are assumed to have an equal probability. Determine the expected NPV, the standard deviation of the NPV and the projects coefficient of variation NPV.

d. On the basis of your analysis, would you recommend that the project be accepted? What added advise and special attention would you give to the company with regard to the project?

Please provide the spreadsheet and input the data as well. All the calculations must be included with steps and complete input data in the spreadsheet.

Step 1: Set Up the Spreadsheet

Create a spreadsheet with columns for each year of the project's life (Years 1 to 5) and rows for the following variables: Sales, Sales Price, Variable Cost, Fixed Cost, Depreciation, tax, Operating Working Capital, and Free Cash Flow.

Step 2: Fill in the Initial Data

Equipment Cost: RM1,000,000

Net Operating Working Capital (Year 1): 15% of Year 1 Sales

Project Life: 5 years

Units Sold per Year: 100,000

Initial Sales Price: RM12 per unit

Sales Price Increase: 3% annually

Initial Variable Cost: RM5 per unit

Variable Cost Increase: 3% annually

Initial Fixed Costs (Year 1): RM420,000

Fixed Cost Increase: 3% annually

Salvage Value of Equipment: RM200,000

Equipment Scrap Value: RM250,000

Corporate Tax Rate: 25%

Cost of Capital: 12%

Step 3: Calculate Yearly Data For each year (1 to 5), calculate the following: Sales (Units Sold * Sales Price) Variable Cost (Units Sold * Variable Cost) Fixed Cost Depreciation (Equipment Cost / Project Life) Tax (Tax Rate * (Sales - Variable Cost - Fixed Cost + Depreciation)) Operating Working Capital (15% of the previous year's Sales) Free Cash Flow (Sales - Variable Cost - Fixed Cost - Tax + Depreciation)

Step 4: Calculate Cumulative Data Calculate Cumulative Free Cash Flow for each year by summing up the Free Cash Flows from Year 1 to the current year.

Step 5: Calculate NPV, IRR, and Payback Use the NPV function to calculate the Net Present Value. The formula is NPV(Cost of Capital, Year 1 to 5 Cash Flows) - Initial Equipment Cost. Use the IRR function to calculate the Internal Rate of Return for the project's cash flows. Calculate the Payback Period, which is the year in which Cumulative Free Cash Flow becomes positive.

Step 6: Sensitivity Analysis a. Create scenarios by changing the variables (Sales Price, Units Sold, Variable Cost, Fixed Cost, and Cost of Capital) by 10% above and below their base-case values. b. Calculate the NPV for each scenario and create a table or chart to show the sensitivity of NPV to these changes. Step 7: Scenario Analysis a. For the best-case scenario: No increase in Sales Price, 5% increase in Units Sold, and 3% decrease in Variable Cost. b. For the worst-case scenario: 5% decrease in Units Sold, 2% decrease in Sales Price, and 3% increase in Variable Cost. c. Calculate the expected NPV, standard deviation of NPV, and the coefficient of variation of NPV. Step 8: Recommendation Based on the analysis, make a recommendation whether to accept the project. Consider the NPV, IRR, payback period, sensitivity analysis, and scenario analysis results. Additional Advice Provide advice and special attention to the company regarding the project: Consider the project's sensitivity to changes in key variables. Ensure that the project aligns with the company's long-term strategy and risk tolerance. Assess the impact of changes in inflation rates, market conditions, and competition. Consider potential exit strategies for the equipment, including selling it as scrap or repurposing it after the project ends. Evaluate whether the project contributes to the company's overall objectives and profitability. Make sure to set up the spreadsheet and perform the calculations accurately, as this will form the basis for your analysis and recommendation.