Hello, I'm working on this problem and I'm having troubles with the formulas. Could you help me? Thank you!!

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 rms for negotiated fees based on actual tons shipped and miles. The operations management group has been dissatised 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 eet 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 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. (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, 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 years income the fol-lowing: operating costs, the interest portion of the 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 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 aver-age 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:

For year 0, list any capital expenditures (purchase option) and/or upfront fees (lease option).

For years 17, 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 auto-matically 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 6.6.

Table 6.6: Comparison worksheetNet Costs after Taxes: Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Trucking by others Purchasing Leasing Cost Savings Comparisons Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Trucking by others vs. Purchasing Trucking by others vs. Leasing

Regardless of which signs youve 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.)

.