specific help needed within worksheets including buying trucks and comparison worksheet. Formulas are needed.

Initial Cost Yearly Increase Marginal Tax Rate Year Trucking Expense Tax Savings Net Cost After Taxes ($12,000,000) 6% 15% 0 1 2 3 4 ($12,720,000) ($13,483,200) ($14,292,192) ($15,149,724) ($1,908,000) ($2,022,480) ($2,143,829) ($2,272,459) ($10,812,000) ($11,460,720) ($12,148,363) ($12,877,265) 5 6 7 ($16,058,707) ($17,022,229) ($18,043,563) ($2,408,806) ($2,553,334) ($2,706,534) ($13,649,901) ($14,468,895) ($15,337,029) Trucks Purchased Cost Per Truck Marginal Tax Rate Year Purchase Trucks Operation Costs Interest on Loan SLN Depreciation Trucking Costs Tax Deuduction Net Cost after Taxes Annual Loan Rate Down Payment Periods per Year Length (years) ($125,000) 15% $ $ $ 0 (2,500,000) $ $ $ (2,500,000) $ $ (2,500,000) 1 (11,400,000) $ (97,645) $ (328,571) $ (11,826,217) 1,773,933 4.75% 10% 4 4 2 (11,799,000) (72,342) (328,571) 3 Operating Expense Increase Milage Rate Miles per Year Salvage Value Depreciation Life (years) 4 5 3.50% ($3.80) 150,000 8% 7 6 7 Trucks Leased Fee Per Truck Marginal Tax Rate Lease Cost per Truck 35,000 ($10,000) 15% ($35,000) Year Operating Cost Annual Lease Fee Lease Initiation Fee Subtotal Trucking Tax Deduction Net Cost after Taxes 0 $ $ $ $ $ 1 (11,100,000) $ (700,000) $ 2 (11,488,500) $ (700,000) $ 3 (11,890,598) (700,000) (200,000) $ 30,000 $ (170,000) $ (11,800,000) $ (1,770,000) $ (10,030,000) $ (12,188,500) $ (1,828,275) $ (10,360,225) $ (12,590,598) (1,888,589.63) (10,702,008) Operating Expense Increase Milage Rate Miles per Year 3.50% ($3.70) 150,000 $ $ 4 (12,306,768) $ (700,000) $ 5 (12,737,505) $ (700,000) 6 (13,183,318) $ ### $ 7 (13,644,734) (700,000) $ $ $ (13,006,768) $ (1,951,015.26) $ (11,055,753) $ (13,437,505) $ (2,015,625.80) $ (11,421,880) $ (13,883,318) $ (2,082,497.70) $ (11,800,820) $ (14,344,734) (2,151,710.12) (12,193,024) Year Trucking by Others Buying Trucks Leasing Trucks 0 1 2 3 Cost Savings Comparisons 0 1 2 3 Trucking by Others vs. Purchasing Trucking by Others vs. Leasing $ $ (2,500,000) $ (170,000) $ 759,716 782,000 Net Present Values Savings: Purchase Savings: Leasing vs. Trucking by vs. Trucking by Others Others Savings from Purchase IRR ROI PAYBACK Cumulative Cash Flow > 0 Cumulative Cash Flow > 0 4 5 6 7 4 5 6 7 Succeeding in Business with Microsoft Excel 2013 Chapter 6, Level 3 - Analyzing Purchasing vs. Leasing Options for CKG Auto CKG 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 othersUsing several different trucking carriers, the CKG Auto com- pact 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. The marginal tax rate is 15%. 2. Buying trucksIf CKG Auto purchased a fleet of 20 trucks, the cost of such a purchase would be based on the following: a. 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). b. The marginal tax rate is 15%. c. 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. d. 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. e. 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. f. 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 trucksIf CKG Auto leases a fleet of 20 trucks, the cost of such a lease would be based on the following: a. 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. b. The marginal tax rate is 15%. c. 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. d. 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 Page 1 of 3 Succeeding in Business with Microsoft Excel 2013 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. e. 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:3.5 1. Download the template named BuyOrLease Template that is provided in the drop box for this case problem in Blackboard. Save it as BuyOrLease in the Chapter 6 folder. a. Note: Some numbers appear so you can check your progress. In these cells, you must change the numbers to the correct formulas. 2. Enter the inputs at the top of the TruckingByOthers, BuyingTrucks, and LeasingTrucks sheets. The inputs are provided in the preliminary description to this case problem. 3. For each option, calculate the net costs after taxes for each year, starting with year 0 through year 7, as follows: a. Note: The formulas for Year 0 may be different than the formulas for Year 1. Note: The formulas for Year 1 may be different than Year 2. Note: All formulas for Year 2 can be copied to years 3:7. There is one exception on the BuyingTrucks sheet. b. For year 0, list any capital expenditures (purchase option) and/or upfront fees (lease option). c. 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). i. Remember that the $12 million for trucking costs will go up by 6% in year 1 for the first option. ii. Remember that 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. d. Calculate the total tax deductible costs for each year and each option. e. 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%. f. 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. 4. On the Comparison sheet: a. List the net cost after taxes for each year for each option in rows 2:4, referencing the original worksheets so that any subsequent changes will be automatically reflected on this sheet. Page 2 of 3 Succeeding in Business with Microsoft Excel 2013 5. 6. 7. 8. 9. b. 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. c. 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 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.) 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. Calculate the internal rate of return for the cost savings amounts (trucking by others versus purchasing, and trucking by others versus leasing). Calculate the return on investment and payback period for both options. Make a recommendation of which method to use for trucking (by others, purchasing, or leasing). a. Highlight in yellow the row containing the net cost savings of the option you recommend. b. In a separate area on the worksheet, highlighted in yellow, explain the reason for your choice. Save and close the BuyOrLease.xlsx workbook. Page 3 of 3