please answer as soon as possible. use excell. thank you very much
system that will fully automate the order picking process in its new start-of-the-art warehouse facility. Since order picking is currently a manual process they anticipate significant labor savings and improvements in order accuracy but have asked for your help in evaluating the investment worthiness of this system. You've identified a suitable system on the second-hand equipment market and have collected the following information regarding this investment. Evaluate the investment worthiness of this project by building the income and cash flow statements and calculating the net present worth of this investment opportunity. A total of 125 orders are currently manually picked and shipped from this warehouse each day Each warehouse employee, regardless of their assigned duties, earns an annual salary of $39,250. The new automatic retrieval system will cost $535,000 and will be depreciated according to a 7-year MACRS class schedule. Freight costs for shipping the new system to the warehouse total $2,660 while installation will cost $8,134 The system will require some programming by in house personnel during the first year of use. Integrating the software into the existing warehouse management system will cost $27,250 for programming and testing services. The available service contract, necessary to keep the automated system in good working condition and prevent break-downs, will cost an additional $45,500 per year. A full system overhaul will be required in year 3 and will cost $28,950 As volumes continue to increase you anticipate needing to move into an even larger facility in a few years. Therefore, you anticipate needing this system only for 5 years. At the end of the system's useful life, you've estimated the system's salvage value to be 14% of the cost basis. The system will reduce the company's labor needs by 7 individuals (the original system required 8 workers while only 1 worker is required once the new automated system is installed). Savings, in terms of reduced labor costs, will be realized every year. With the automated order picking system, your company will now be able to pick a total of 150 orders per day (e. 25 more orders than the old system). Revenues from the additional orders that the automated system will pick each day are estimated to be $43,750 annually. In order to manage daily financial obligations, your team has determined that an additional $24,000 investment in working capital is needed. This sum will be recovered in full at the end of each year. . Most (75%) of the system's cost basis will be financed through a bank loan which has an interest rate of 10%. Payments will be made at the end of each year for the next 5 years. The federal and state marginal tax rates for your company are 21% and 8.84% respectively . Your company's interest rate (MARR) is 15%. Use the attached Excel template to build the financial statements and submit as an attachment to your answer. Remember to indicate your final decision, as well as your calculation of NPW, in your spreadsheet, and HIGHLGHT these answers (NPW and your final decision) within the spreadsheet. Financial statement template xlsx D F H 2. 0 1 2 3 4 5 3 4 6 Income Statement Revenues Expenses COGS Depreciation Debt Interest Operating Expenses Taxable income Taxes Net Income 8 9 10 11 2 3 0 1 2 3 4 5 4 5 6 7 8 9 Cash Flow Statement Operating Activiies Net Income Depreciation Investment Activities Captial Investment Salvage value Gains Tax Working Capital Financing Activities Borrowed Funds Repayment of Principal Net Cash Flow 0 1 2 3 4 5 6 system that will fully automate the order picking process in its new start-of-the-art warehouse facility. Since order picking is currently a manual process they anticipate significant labor savings and improvements in order accuracy but have asked for your help in evaluating the investment worthiness of this system. You've identified a suitable system on the second-hand equipment market and have collected the following information regarding this investment. Evaluate the investment worthiness of this project by building the income and cash flow statements and calculating the net present worth of this investment opportunity. A total of 125 orders are currently manually picked and shipped from this warehouse each day Each warehouse employee, regardless of their assigned duties, earns an annual salary of $39,250. The new automatic retrieval system will cost $535,000 and will be depreciated according to a 7-year MACRS class schedule. Freight costs for shipping the new system to the warehouse total $2,660 while installation will cost $8,134 The system will require some programming by in house personnel during the first year of use. Integrating the software into the existing warehouse management system will cost $27,250 for programming and testing services. The available service contract, necessary to keep the automated system in good working condition and prevent break-downs, will cost an additional $45,500 per year. A full system overhaul will be required in year 3 and will cost $28,950 As volumes continue to increase you anticipate needing to move into an even larger facility in a few years. Therefore, you anticipate needing this system only for 5 years. At the end of the system's useful life, you've estimated the system's salvage value to be 14% of the cost basis. The system will reduce the company's labor needs by 7 individuals (the original system required 8 workers while only 1 worker is required once the new automated system is installed). Savings, in terms of reduced labor costs, will be realized every year. With the automated order picking system, your company will now be able to pick a total of 150 orders per day (e. 25 more orders than the old system). Revenues from the additional orders that the automated system will pick each day are estimated to be $43,750 annually. In order to manage daily financial obligations, your team has determined that an additional $24,000 investment in working capital is needed. This sum will be recovered in full at the end of each year. . Most (75%) of the system's cost basis will be financed through a bank loan which has an interest rate of 10%. Payments will be made at the end of each year for the next 5 years. The federal and state marginal tax rates for your company are 21% and 8.84% respectively . Your company's interest rate (MARR) is 15%. Use the attached Excel template to build the financial statements and submit as an attachment to your answer. Remember to indicate your final decision, as well as your calculation of NPW, in your spreadsheet, and HIGHLGHT these answers (NPW and your final decision) within the spreadsheet. Financial statement template xlsx D F H 2. 0 1 2 3 4 5 3 4 6 Income Statement Revenues Expenses COGS Depreciation Debt Interest Operating Expenses Taxable income Taxes Net Income 8 9 10 11 2 3 0 1 2 3 4 5 4 5 6 7 8 9 Cash Flow Statement Operating Activiies Net Income Depreciation Investment Activities Captial Investment Salvage value Gains Tax Working Capital Financing Activities Borrowed Funds Repayment of Principal Net Cash Flow 0 1 2 3 4 5 6
