KG Auto compact car manufacturing assembly plants rely on parts from multiple outside vendors and internal subassembly plants. Currently, these parts are all transported via independent trucking firms for negotiated fees based on actual tons shipped and miles. The operations management group has been dissatisfied lately with the service levels provided by these outside trucking companies, as well as with the rising costs of roughly 6.0% per year for the last two years. These costs are expected to rise in the foreseeable future at similar rates, according to industry analysts. The operations management group is beginning a study to determine if purchasing or leasing a fleet of trucks would be a more cost-effective solution over the next seven years. To do so, the group has compiled some of the costs for each transport option, as follows: (1) Trucking by others-Using several different trucking carriers, the CKG Auto compact car manufacturing group currently pays $12,000,000 annually in trucking fees. Again, these costs are expected to rise at an annual rate of 6%. So in year 1, the cost is expected to be $12 million plus an additional 6%. All costs are considered expenses, which can be used to reduce income for purposes of calculating taxes. (2) Buying trucks- If CKG Auto purchased a fleet of 20 trucks, the cost of such a purchase would be based on the following: The model of truck being considered with trailers is estimated to cost $125,000 per truck. This amount will be spent in year 0 (now). This purchase would be funded using a bank loan. The bank is willing to lend the money at a 4.75% annual interest rate compounded quarterly over the next four years. A 10% down payment will be required, which can be funded from current assets. The operations management group has been directed to assume that if CKG Auto purchases this fleet, it would be depreciated using straight line depreciation over the full seven-year period, (2) Buying trucks- If CKG Auto purchased a fleet of 20 trucks, the cost of such a purchase would be based on the following: The model of truck being considered with trailers is estimated to cost $125,000 per truck. This amount will be spent in year o (now). This purchase would be funded using a bank loan. The bank is willing to lend the money at a 4.75% annual interest rate compounded quarterly over the next four years. A 10% down payment will be required, which can be funded from current assets. The operations management group has been directed to assume that if CKG Auto purchases this fleet, it would be depreciated using straight line depreciation over the full seven-year period, assuming a salvage value of 8% of the original purchase price. Operating costs for year 1 are estimated at $3.80 per mile; this includes driver wages, gas, insurance, maintenance, fees, and licenses. It is also assumed that each truck will average 150,000 miles per year. For year 2 and all subsequent years, assume a cost increase of 3.5% per year above the previous year. . For the calculation of taxes, CKG Auto can deduct from each year's income the following: operating costs, the interest portion of the loan payments, and depreciation. (3) Leasing trucks - If CKG Auto leases a fleet of 20 trucks, the cost of such a lease would be based on the following: There will be an upfront signing fee of $10,000 per truck due at signing (year O). These fees will be paid directly out of cash assets, and no additional financing will be required. These fees can be used to reduce income in year o for tax purposes. Each year, the lease cost will be a flat fee of $35,000 per truck for each of the next seven years. This fee is fixed for the duration of the lease based on a 150,000-per-mile limit per year per truck. Operating costs for year 1 are estimated at $3.70 per mile; this includes driver wages, gas, insurance, fees, and licenses. Regular maintenance and repairs are the responsibility of the truck leasing company. It is assumed that each truck will average 150,000 miles per year. For year 2 and all subsequent years, assume a cost increase of 3.5% per year above the previous year. (3) Leasing trucks- If CKG Auto leases a fleet of 20 trucks, the cost of such a lease would be based on the following: There will be an upfront signing fee of $10,000 per truck due at signing (year 0). These fees will be paid directly out of cash assets, and no additional financing will be required. These fees can be used to reduce income in year 0 for tax purposes. Each year, the lease cost will be a flat fee of $35,000 per truck for each of the next seven years. This fee is fixed for the duration of the lease based on a 150,000-per-mile limit per year per truck. Operating costs for year 1 are estimated at $3.70 per mile; this includes driver wages, gas, insurance, fees, and licenses. Regular maintenance and repairs are the responsibility of the truck leasing company. It is assumed that each truck will average 150,000 miles per year. For year 2 and all subsequent years, assume a cost increase of 3.5% per year above the previous year. Because this is an operating lease, there is no depreciation. The entire cost of the lease is considered an expense and can be used to reduce income for purposes of calculating taxes. Your task is to analyze the various options for CKG Auto to determine which is the most viable. Complete the following: 1. Create a new workbook and save it as Buy OrLease.xlsx in the Chapter 6 folder. Begin by setting up three separate worksheets, one for each option, with appropriate sheet names and titles. 2. For each option, calculate the net costs after taxes for each year, starting with year o through year 7, as follows: . For year o, list any capital expenditures (purchase option) and/or upfront fees (lease option). For years 1-7, itemize the relevant costs for calculating taxable deductible expenses, including any operating expenses, leasing or trucking fees paid in that year, any associated depreciation, and interest (purchase option). Remember that the $12 million for trucking costs will go up by 6% in year 1 for the first option, and operating expenses for the purchase and lease options will increase by 3.5% each year, with year 1 operating expenses for purchase option at $3.80/mile and for lease option at $3.70 per mile assuming 150,000 miles per year per vehicle. Calculate the total tax deductible costs for each year and each option. . Based on the taxable costs, calculate the resulting tax savings for each year and each option. Tax savings can be calculated by multiplying all tax deductible costs by the marginal tax rate of 15% Subtract this tax savings from the tax deductible costs to arrive at the net costs after taxes. For year 0, only consider monies paid toward leases and/or purchases, and keep in mind that these purchases are not tax deductible. 3. Insert a fourth worksheet named Comparison, and include the following on this worksheet: List the net cost after taxes for each year for each option in three sequential rows, referencing the original worksheets so that any subsequent changes will be automatically reflected on this sheet. . Using the net costs after taxes, calculate the cost savings between using the current trucking method (by others) and purchasing a fleet; and then in the next row, the cost savings between the current trucking method and leasing. Your Comparison worksheet should have a format similar to the one in Table Table Comparison worksheet Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Net Costs after Taxes: Trucking by others Purchasing Leasing Cost Savings Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Comparisons Trucking by others vs. Purchasing Trucking by others vs. Leasing . Regardless of which signs you've used in your analysis so far, express cost savings as a positive number. (For example, if the costs for shipping by others for year 1 are $10,000 after Kegardless of which signs you've used in your analysis so far, express cost savings as a positive number. (For example, if the costs for shipping by others for year 1 are $10,000 after taxes, and the costs for shipping with purchased trucks in year 1 are $6,000 after taxes, express the cost savings as a positive $4,000.) 4. Using NPV, determine the discounted value of the cost savings (if any) between trucking by others versus purchasing trucks for years 0 through 7, for hurdle rates between 8% and 18% (at 1% intervals). In a similar way, determine the net present value of the cost savings between trucking by others versus leasing trucks. 5. Calculate the internal rate of return for the cost savings amounts (trucking by others versus purchasing, and trucking by others versus leasing). 6. Calculate the return on investment and payback period on the investment versus the cost savings of the purchasing option. 7. Make a recommendation of which method to use for trucking (by others, purchasing, or leasing). Highlight in yellow the row containing the net cost savings of the option you recommend. In a separate area on the worksheet, highlighted in yellow, explain the reason for your choice. 8. Save and close the BuyOrLease.xlsx workbook