Can you please explain in DETAIL how to CALCULATE WARRANTY COSTS IN EXCEL.

I am doing a capital budgeting analysis for a trasit bus. I have two engine options: Detroit and Marcus.

All the numbers are expressed in todays dollars. The forecasted average inflation per year is 3.5%

Price per bus $220,000 Units sold per year 11,000

Labor cost per bus $50,000

Components & Parts per bus $95,000

Selling General & Administrative (fixed) $250,000,000

NOTE: Average warranty cost per year per bus for the first five years is $1,000. THE PRESENT VALUE OF THIS COST will be used as a cost figure for each bus. Afterwards, the bus operator will become responsible the repairs on the buses. The buses can be produced for twenty years. Afterwards, the designs become obsolete.

DETROIT ENGINE Price per engine: 20, 000 Average annual warranty cost per year for five years: 1,000 Afterwards, the bus operator will become responsible for the repairs on the buses.*

MARCUS ENGINE Price per engine: 18, 000 Average annual warranty cost per year for five years: 1,500 Afterwards, the bus operator will become responsible for the repairs on the buses.* The chosen engine will be installed in every bus and will become a cost figure for each bus. NOTE: The engine manufacturers are not providing Road King Trucks with any warranty. However, Road King Trucks will provide a warranty to its customers. After the initial five years, the bus operators may purchase an extended warranty from any insurance company that offers such packages.

WACC = 9.91%

The big hassle in this cash flow analysis for Road King Trucks is determining the warranty cost for the bus and the engines. Since the warranty is only for five years, the analysis calls for a present value of these five years of costs to be included in each bus produced. How you determine this is one of the key segments of this analysis.