Could you please review my work below which is the NPV and sensitivity analysis sheets provided in images and provide your feedback? I'd greatly appreciate your advice on what changes, fixes, or additions are necessary. Your help would mean a lot?thank you so much!!!

FIN3125 Coursework:

Sherwood plc is a leading multi-divisional organisation based in the UK and specialising in manufacturing household appliances. To address the major advances in technology over recent years and a growing demand for energy efficient products, Sherwood plc is entering a period of significant capital investment to secure future growth and enhance shareholder value.

Several capital budgeting projects due to be implemented next year including new product lines and modifications to existing products, are being considered and require immediate evaluation. The Financial Director of Sherwood plc has allocated one of these investment proposals for your evaluation - The Ergogrip.

Company General Information

1. It is nearing the end of the last quarter of 2024 and the company prepares accounts with a financial year ending 31^st December.

1. Company earnings are subject to 20% corporation tax, payable on a current year basis.

1. Land and buildings qualify for 4% reducing balance industrial buildings allowances whilst plant, equipment and machinery are subject to 'short-life' asset election and attract 22% reducing balance tax allowances. Capital allowances can be claimed in the year of acquisition and every subsequent year of ownership except for the last year where a balancing allowance can be claimed or there will be a balancing charge to the company, unless otherwise specified in the particulars of each case.
2. All operating cash flows (revenues and operating costs) are quoted at current 2024 prices and subject to annual inflation, unless otherwise specified in the particulars of each case.

1. General rate of inflation is expected to be 3.5% p.a. for each of the next three years and 2.5% p.a. in each subsequent year.
2. Management consider the company's existing after-tax cost of capital of 16% is an appropriate rate to appraise all capital investments (unless otherwise specified).

1. Time Zones for Appraisal -We are in the last quarter of 2024:

2024 is Year0 i.e. cash flows occurring at the end of 2024/beginning of 2025

2025 is Year 1i.e. cash flows occurring at the end of 2025/beginning of 2026

2026 is Year 2i.e. cash flows occurring at the end of 2026/beginning of 2027 and so on.

Investment Particulars - The Ergogrip

Sherwood plc is considering moving one of its factories to a new site. The new site would cause a significant disruption to existing production and sales of theErgogrip, however the current production capacity of 9 million units would no longer be constrained by the size of the existing factory and hence, a higher output can be achieved to match a growing demand for the Ergogrip.

Moving to the new premises would take place at the beginning of 2025. The existing premises would be leased for indefinite period, commencing on 1^st January 2025 for an annual rent of 500,000 payable in advance. Lease rentals would be subject to 1.5% p.a. increase in nominal terms and deemed as taxable income.

The cost of the new premises would be 6 million and existing plant & equipment will be acquired for 3 million, both amounts payable on 1^st January 2025. Modifications to the equipment in the new factory would be required at a cost of 900,000, also payable on 1^st January 2025. The new premises, plant & machinery and modification expenditure would qualify for the company's capital allowance rates.

The maximum production capacity of the new factory is 15 million units per year but due to initial setting-up time and disruption from moving, the capacity of the new factory for 2025 would only be 9 million units. Potential sales demand for the company's output is estimated to be 15 million units p.a. for 2025, reducing by 1 million units each year until 2030 and remain at 10 million units from 2030 onwards. Given that projected sales and output can be accommodated by both factories from 2030 onwards, no incremental manufacturing costs or revenues will arise from the move after 2029.

Labour is employed under flexible contracts and therefore the labour costs will vary directly with output. Each unit of the Ergogrip requires 8 hours of unskilled labour at 5/hr and 2 hours of semi-skilled labour at 12/hr. If the company decides to move site, it is anticipated that some employees will refuse to move and hence it is expected that the company will have to make redundancy payments of 875,000 on 1^st January 2025. The company will have to replace these employees and incur retraining costs of 500,000 payable on 31^st December 2025.

Material quantities and costs per kg per unit of the Ergogrip are 4kgs of Gamma99 at 3.80/kg and 2kgs of Alpha2 at 2.50/kg.Gamma99 is only available from an overseas supplier leading to additional transport costs of 20p per kg of Gamma99 purchases.

Fixed costs of production are currently 100 million p.a. however this will increase by 20% with the move to the new factory. The working capital required is expected to be 4,000,000, 5,000,000, 6,000,000, 6,000,000 and 4,000,000 over the next 5 years respectively. Working capital needs to be in place at the start of each year (for the purposes of the appraisal all working capital will be liquidated at the end of 5 years).

The selling price for the Ergogrip is set in order to achieve a contribution of 15%. Contribution is defined as selling price less variable costs (material, labour and transport costs). It excludes training and redundancy costs. All the above costs and revenues are quoted in current prices and subject to general inflation per annum, with the exception of working capital and capital allowances.

Assignment Requirements Total 40 marks

Requirement 1 - Investment Appraisal(35 marks)

Using Excel, identify the annual net cash flows (based on the incremental output for the relevant yearsas a result of relocating to the new factory) that would arise from the company's decision to relocate, and calculate the NPV of the investment.

Requirement 2 - Sensitivity Analysis(5 marks)

What would be the minimum selling price that must be achieved for theErgogrip to ensure the relocation is economically desirable?

A8 viX fx |8 hours WORKINGS NET PRESENT VALUE Years 2024 2025 2026 2027 2028 2029 2030 Variable Cost Initial Investment - 10,775,000.00 Unskilled per hour rate $ 5 Revenue (units) 9,000,000.00 14,000,000.00 13,000,000.00 12,000,000.00 1 1,000,000.00 Semi-skilled per hour rate f 12 10,000,000.00 Revenue (sales) E 900,000,000.00 & 1,449,000,000.00 f 1,392,592,500.00 f 1,330,461,450.00 f 1,250,079,404.06 { 1,164,846,717.42 Unskilled hours Semi-skilled hours Variable Cost & 765,000,000.00 E 1,231,650,000.00 f 1,183,703,625.00 & 1,130,892,232.50 f 1,062,567,493.45 990,119,709.81 3 hours 2 hours Labour cost per unit Contribution Margin & 135,000,000.00 217,350,000.00 208,888,875.00 199,569,217.50 187,511,910.61 174,727,007.61 40 24 64.00 Fixed Cost f 120,000,000.00 124,200,000.00 128,547,000.00 133,046,145.00 136,372,298.63 139,781,606.09 Working Capital 4,000,000.00 - 5,000,000.00 -f 6,000,000.00 -f 6,000,000.00 -f 4,000,000.00 f 25,000,000.00 Gamma99 cost per kg is $ 4 Retraining Cost 500,000.00 Alpha2 cost per kg is & 2.5 Lease Rent 500,000.00 E 07,500.00 515,112.50 522,839.19 { 530,681.78 38,642.00 Gamma99 Alpha2 Capital Allowance (New Premises) 48,000.00 46,080.00 44,236.80 E 42,467.33 40, 768.63 39,137.89 4 kg 2 kgs Material cost per unit Capital Allowance (Plant & Machinery) 171,600.00 133,848.00 104,401.44 { 81,433.12 f to its it in 49,543.91 16 21.00 Net Earnings f 63,517.84 { 10,780,400.00 88,477,572.00 f 74,708,349.26 f 60,922,011.24 f 47,566,007.29 60,395,361.72 Tax Payable @ 20% 2,156,080.00 17,695,514.40 f 14,941,669.85 f 12,184,402.25 f 9,513,201.46 f 12,079,072.34 Total Variable cost per unit 35.00 Capital Allowance (New Premises) 48,000.00 & 46,080.00 f 44,236.80 E 42,467.33 { 40,768.63 f 39,137.89 Selling Price for achieving 15% contribution 100.00 Capital Allowance (Plant & Machinery) it thi 171,600.00 { 133,848.00 104,401.44 { 81,433.12 f 63,517.84 49,543.91 margin per unit Net Cash Flows 3,843,920.00 f 70,961,985.60 59,915,317.65 f 48,861,509.44 38,157,092.30 48,404,971.18 DCF @ 16% 0.862 0.743 0.641 0.552 0.476 0.410 Years Inflated Sale Price Discounted Cash Flows f 10,775,000.00 7,624,068.97 f $2,736,315.10 f 38,385,208.01 { 26,985,776.69 f 18,167,088.28 f 19,867,445.51 2025 100.00 NPV 2026 f 152,990,902.56 103.50 2027 107.12 As the NPV of the investment is positive which is required to relocate the factory, so this project seems to be feasible and profitable for the Sherwood PLC and it must be accepted. 2028 1 10.87 2029 113.64 2030 116.48 Years Inflated Variable Cost 2025 85.00 2026 87.98 2027 91.05 2028 94.24 2029 96.60 2030 99.01 Cost of new premises 6,000,000.00 8 Plant & equipment 3,000,000.0 Modifications cost 900,000.00 Redundancy payments 875,000.00 Total Initial Investment f 10,775,000.00 Capital Allowance Tax Savings on Depreciation (New Premises) Years Book Value at start Dep @ 4% Book Value at end Tax Savings @ 20% 2025 6,000,000.00 240,000.00 f 5,760,000.00 48,000.00 2026 5,760,000.00 230,400.00 f 5,529,600.00 46,080.00 2027 5,529,600.00 221,184.00 5,308,416.00 44,236.80 2028 5,308,416.00 212,336.64 E 5,096,079.36 42,467.33 2029 5,096,079.36 f 203,843.17 f 4,892,236.19 f 40,768.63 2030 4,892,236.19 195,689.45 f 4,696,546.74 & 39,137.89 Capital Allowance Tax Savings on Depreciation (Plant & Machinery) Years Book Value at start Dep @ 22% Book Value at end Tax Savings @ 20% 2025 3,900,000.00 858,000.00 f 3,042,000.00 171,600.00 2026 3,042,000.00 669,240.00 f 2,372,760.00 133,848.00 2027 2,372,760.00 $22,007.20 1,850,752.80 104,401.44 2028 1,850,752.80 407,165.62 1,443,587.18 81,433.12 2029 1,443,587.18 317,589.18 1,125,998.00 63,517.84 2030 1,125,998.00 247,719.56 f 878,278.44 f 49,543.91 NPV Sensitivity AnalysisA4 vi X V fx |Variable Cost D M WORKINGS Sensitivity Analysis Years 2024 2025 202 202 2028 2029 2030 Variable Cost Initial Investment -f 10,775,000.00 Unskilled per hour rate f 5 Revenue (units) 9,000,000.00 14,000,000.00 13,000,000.00 12,000,000.00 1 1,000,000.00 10,000,000.00 Semi-skilled per hour rate f 12 Revenue (sales) f 864,000,000.00 f 1,391,040,000.00 f 1,336,888,800.00 f 1,277,242,992.00 f 1,200,076,227.90 f 1,118,252,848.73 Unskilled hours Semi-skilled hours Variable Cost $ 765,000,000.00 f 1,231,650,000.00 f 1,183,703,625.00 f 1,130,892,232.50 f 1,062,567,493.45 { 990,119,709.81 8 hours 2 hours Labour cost per unit Contribution Margin E 99,000,000.00 E 159,390,000.00 153,185,175.00 146,350, 759.50 137,508,734.45 128,133,138.92 40 24 64.00 Fixed Cost 120,000,000.00 124,200,000.00 128,547,000.00 E 133,046,145.00 136,372,298.63 139,781,606.09 Working Capital 4.000.000.00 -E 5,000,000.00 - 6,000,000.00 -E 6,000,000.00 -f 4,000,000.00 25,000,000.00 Gamma99 cost per kg is $ 4 Retraining Cost 500,000.00 Alpha2 cost per kg is f 2.5 Lease Rent 00,000.00 E 507,500.00 515,112.50 & 522,839.19 530,681.78 538,642.00 Gamma99 Alpha2 Capital Allowance (New Premises) 18,000.00 E 46,080.00 44,236.80 & 42,467.33 40,768.63 39,137.89 4 kgs 2 kgs Material cost per unit Capital Allowance (Plant & Machinery) 171,600.00 133,848.00 104,401.44 81,433.12 f 63,517.84 49,543.91 16 21.00 Net Earnings 25,219,600.00 30,517,572.00 19,004,649.26 7,703,553.24 -f 2,437,168.87 13,801,493.03 Tax Payable @ 20% 5,043,920.00 6,103,514.40 3,800,929.85 1,540,710.65 -f 487,433.77 2,760,298.61 Total Variable cost per unit 85.00 Capital Allowance (New Premises) 48,000.00 E 46,080.00 E 44,236.80 42,467.33 40, 768.63 { 39,137.89 Selling Price for achieving 15% contribution 100.00 Capital Allowance (Plant & Machinery) 171,600.00 E 133,848.00 104,401.44 { 81,433.12 f 63,517.84 { 49,543.91 margin per unit Net Cash Flows 19,956,080.00 f 24,593,985.60 15,352,357.65 6,286,743.04 - 1,845,448.63 11,129,876.22 DCF @ 16% .862 0.743 0.641 0.552 0.476 0.410 Years Inflated Sale Price Discounted Cash Flows f 10,775,000.00 -f 17,203,517.24 f 18,277,337.69 { 9,835,605.73 f 3,472,112.22 -f 878,642.11 f 4,568,171.49 2025 96.00 NPV 7,296,067.78 2026 99.36 2027 102-84 So, the last selling price that must be achieved by Ergogrip to ensure relocation is economically feasible is & 96 because at & 95 NPV becomes negative. 2028 106.4 2029 109.10 2030 111.83 Years Inflated Variable Cost 2025 85.00 2026 87.98 2027 91.05 it it it it it it 2028 94.24 2029 96.60 2030 99.01 Cost of new premises 6,000,000.00 38 Plant & equipment 3,000,000.00 39 Modifications cost th th th 900,000.00 Redundancy payments 875,000.00 Total Initial Investment f 10,775,000.00 Capital Allowance Tax Savings on Depreciation (New Premises) Years Book Value at start Dep @ 4% Book Value at end Tax Savings @ 20% 2025 6,000,000.00 240,000.00 5,760,000.00 E 48,000.00 2026 5,760,000.00 f 230,400.00 5,529,600.00 f 46,080.00 2027 5,529,600.00 f 221,184.00 5,308,416.00 & 44,236.80 2028 5,308,416.00 212,336.64 5,096,079.36 E 42,467.33 2029 5,096,079.36 203,843.17 4,892,236.19 40, 768.63 2030 4,892,236.19 f 195,689.45 f 4,696,546.74 f 39,137.89 Capital Allowance Tax Savings on Depreciation (Plant & Machinery) Years Book Value at start Dep @ 22% Book Value at end Tax Savings @ 20% 2025 3,900,000.00 858,000.00 3,042,000.00 E 171,600.00 2026 3,042,000.00 f 569,240.00 2,372,760.00 133,848.00 2027 2,372,760.00 f 522,007.20 1,850,752.80 104,401.44 2028 1,850,752.80 407,165.62 1,443,587.18 f 81,433.12 2029 1,443,587.18 317,589.18 1,125,998.00 f 63,517.84 2030 1,125,998.00 f 247,719.56 878,278.44 & 49,543.91 NPV Sensitivity Analysis