**Put into excel format**

Milani Electronics sells only one product, it's ABC123 gadget. The company put together a strategic plan that included the following sales estimates: Currently, the product sells for $33.00. Cost data associated with the product is as follows: - Materials to create the product: $5 - Each product requires 42 minutes of labor and the average hourly rate is $10 per hour - Variable MOH costs are $2 per unit - Selling Costs are $1 per unit - Factory Supervisor Salaries are $55,000 per year - Factory Property Tax and Insurance are $25,000 per year - Other Administrative Costs are $10,000 per year - Production levels are expected to be 9,000,17,000, and 16,000 for the next three years. Milani's is preparing to ask Hesburgh Bank for a loan for some new equipment. The loan is expected to be for $315,000 and it carries an 8% interest rate. As part of its due diligence, the bank would like to see three years of projected income statements following GAAP (you can ignore any potential interest expense from the loan). In order to better understand the implications on profits, Milani's executive management team prefers to use a contribution formatted income statement for the same three-year period. Requirement 1: Please provide three years of projected financial statements. The bank and management team are also interested in analyzing the company's profitability so please use all appropriate ratios or calculations to help each group understand the business. The proceeds from the loan will be used to purchase some new factory equipment that will enhance the product and give it broad market appeal. The company has performed marketing and cost studies that revealed the following information: a. New equipment would have to be acquired to produce the device. The equipment would cost $315,000 and have a six-year useful life. After six years, it would have a salvage value of about $15,000. b. Sales unit projections are at 5% above the previously budgeted levels and the quality enhancement would allow Milani's to sell the product for $45. c. Updated cost information includes the following: - Materials to create the product: $5 - Each device requires 48 minutes of labor and the average hourly rate is $12 per hour - Variable MOH costs are $2.50 per unit - Selling Costs are $1 per unit - Factory Supervisor Salaries are $55,000 per year - Factory Property Tax and Insurance are $25,000 per year - Other Administrative Costs are $10,000 per year - Production levels would also increase by 5% per year over previously budgeted amounts c. Production and sales of the device would require working capital of $60,000 to finance accounts receivable, inventories, and day-to-day cash needs. This working capital would be released at the end of the project's life. e. The company uses straight line depreciation by taking the cost of an asset less the salvage value. f. In addition to the costs above and to gain rapid entry into the market, the company would have to advertise heavily. The advertising costs would be: Milani's finance team has warned the executive management that due to the current rise in interest rates, the rate offered on the loan is at risk and could change. As a result, any capital budgeting analysis should include interest rate swings of +/1%. Requirement 2: Please provide the necessary capital budgeting analysis for this project (assume cash flows in years 4-6 are the same as year 3) as well as updated projected income statements, ratios, and calculations from requirement 1 for the next three years for Milani's. Unlike before, you should include interest expense in your projections (assume 8% is the correct interest rate) and you can assume the entire loan won't be paid off until the end of the third year. Requirement 3: Prepare a written analysis that provides a recommendation for or against purchasing the new equipment. The quality of your work should be that of a report ready to be presented to the executive management team at Milani Inc. Milani Electronics sells only one product, it's ABC123 gadget. The company put together a strategic plan that included the following sales estimates: Currently, the product sells for $33.00. Cost data associated with the product is as follows: - Materials to create the product: $5 - Each product requires 42 minutes of labor and the average hourly rate is $10 per hour - Variable MOH costs are $2 per unit - Selling Costs are $1 per unit - Factory Supervisor Salaries are $55,000 per year - Factory Property Tax and Insurance are $25,000 per year - Other Administrative Costs are $10,000 per year - Production levels are expected to be 9,000,17,000, and 16,000 for the next three years. Milani's is preparing to ask Hesburgh Bank for a loan for some new equipment. The loan is expected to be for $315,000 and it carries an 8% interest rate. As part of its due diligence, the bank would like to see three years of projected income statements following GAAP (you can ignore any potential interest expense from the loan). In order to better understand the implications on profits, Milani's executive management team prefers to use a contribution formatted income statement for the same three-year period. Requirement 1: Please provide three years of projected financial statements. The bank and management team are also interested in analyzing the company's profitability so please use all appropriate ratios or calculations to help each group understand the business. The proceeds from the loan will be used to purchase some new factory equipment that will enhance the product and give it broad market appeal. The company has performed marketing and cost studies that revealed the following information: a. New equipment would have to be acquired to produce the device. The equipment would cost $315,000 and have a six-year useful life. After six years, it would have a salvage value of about $15,000. b. Sales unit projections are at 5% above the previously budgeted levels and the quality enhancement would allow Milani's to sell the product for $45. c. Updated cost information includes the following: - Materials to create the product: $5 - Each device requires 48 minutes of labor and the average hourly rate is $12 per hour - Variable MOH costs are $2.50 per unit - Selling Costs are $1 per unit - Factory Supervisor Salaries are $55,000 per year - Factory Property Tax and Insurance are $25,000 per year - Other Administrative Costs are $10,000 per year - Production levels would also increase by 5% per year over previously budgeted amounts c. Production and sales of the device would require working capital of $60,000 to finance accounts receivable, inventories, and day-to-day cash needs. This working capital would be released at the end of the project's life. e. The company uses straight line depreciation by taking the cost of an asset less the salvage value. f. In addition to the costs above and to gain rapid entry into the market, the company would have to advertise heavily. The advertising costs would be: Milani's finance team has warned the executive management that due to the current rise in interest rates, the rate offered on the loan is at risk and could change. As a result, any capital budgeting analysis should include interest rate swings of +/1%. Requirement 2: Please provide the necessary capital budgeting analysis for this project (assume cash flows in years 4-6 are the same as year 3) as well as updated projected income statements, ratios, and calculations from requirement 1 for the next three years for Milani's. Unlike before, you should include interest expense in your projections (assume 8% is the correct interest rate) and you can assume the entire loan won't be paid off until the end of the third year. Requirement 3: Prepare a written analysis that provides a recommendation for or against purchasing the new equipment. The quality of your work should be that of a report ready to be presented to the executive management team at Milani Inc