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

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

All the numbers are expressed in today’s dollars. Theforecasted 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 thefirst five years is $1,000. THE PRESENT VALUE OFTHIS COST will be used as a cost figure for each bus.Afterwards, the bus operator will become responsible the repairs onthe 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 fiveyears: 1,000

Afterwards, the bus operator will become responsible for therepairs on the buses.*

MARCUS ENGINE

Price per engine: 18, 000

Average annual warranty cost per year for fiveyears: 1,500

Afterwards, the bus operator will become responsible for therepairs on the buses.*

The chosen engine will be installed in every bus and will becomea cost figure for each bus. NOTE: The engine manufacturers are notproviding Road King Trucks with any warranty. However, Road KingTrucks will provide a warranty to its customers. After the initialfive years, the bus operators may purchase an extended warrantyfrom any insurance company that offers such packages.

WACC = 9.91%