The company you currently work for is looking for guidance on several projects they are considering. You have been asked to review the following scenarios and offer your recommendations regarding how to proceed:

The following opportunities are being considered as part of their capital budget process (NOTE: they can only choose one):

• A new piece of equipment could increase output of their widgets by 25%. Currently they can create 10000 widgets per day, but forecasts show the possibility to sell more if they can increase their output. The cost of this piece of equipment is $500,000 and it would allow for an annual increase in sales of $150,000 with a gross profit margin of 40%. They anticipate the equipment could be used for 10 years, and sold for $100,000 at the end of its useful life. The equipment would be financed at 7% interest.
• A competing business is available for sale and can be acquired for $1,000,000. This investment will add $75,000 to the EBIT, growing by 4% annually for 10 years. After 10 years, this business is anticipated to be worth $1,500,000 and would be sold as part of a strategic business restructuring plan. The purchase price would be financed at 5.25% interest.

The business currently has the following financial details available:

• Gross sales of $3,650,000
• Gross profit margin 40%
• Fixed costs of $1,110,000
• Tax rate 25%
• Interest Expense (included in fixed costs) of $150,000 without the above investments.
• Annual sales is expected to increase by 5% regardless of which capital project is chosen.

Submit an Excel workbook with the following worksheets:

1. Capital Budget calculations for both options.
2. 10 year forecast based on the above information (NOTE: do a separate forecast for each of the capital expenditure options).
3. Summary worksheet

Particular Current Additional Equipment Competing Business Output 10,000 Cost 12,500.00 1,000,000.00 Increase in profit per y 60,000.00 75,000.00 Scarp vaule end of 10y 100,000.00 15,000.00 Finance 7% 5.25% Growth 5% 4% Sales ,500,000.00 GP 14,600,000.00 Fixed cost 1,110,000.00 L1 Additional Equipment 13 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 14 Initial Outlay 500,000.00 500,000.00 15 Increase in growth profit 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 16 Depreciation 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 Net increase 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 Tax 25% 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 19 Profit After Tax 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 20 Scrap vaule end of 10 year 100,000 Cashflow 55,000 55,000 55,000 55,000 55,000 55.000 55,000 55,000 55,000 550,000 2 Discounting 7% 1.00 0.93 0.86 0.79 0.72 0.65 0.58 0.51 0.44 0.37 23 Present vaule of Cash Flow 55,000 51,150 43,989 34,751 25,021 16,264 9,433 4,811 2,117 783 243,318 Net Present Value 256,682 15 26 Competing Business 27 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 28 Initial Outlay 1,000,000.00 1,000,000.00 Increase in growth profit 75,000 78,000 81,120 84,365 87,739 91,249 94,899 98,695 102,643 106,748 0 Tax 25% 18,750 19,500 20,280 21,091 21,935 22,812 23,725 24,674 25,661 26,687 31 Profit After Tax 56,250 8,500 60,840 63,274 65,804 68,437 71,174 74,021 76,982 80,061 32 Vaule end of 10 year 1,500,000 Cashflow 56,250 58,500 60,840 63,274 65,804 68,437 71,174 74,021 76,982 1,580,061 $4 Discounting 5.25% 1.00 0.95 0.90 0.84 0.79 0.74 0.69 0.63 0.58 0.53 35 Present vaule of Cash Flow 56,250 53,297 109,547 36 Net Present Value