Create a cash flow for the 2 options given. Please use excel if possible

Houston Auto Parts, HAP, is a company owned and operated for 25 years on the outskirts of Houston, Alaska. The business has excellent contracts for parts supply with several national retailers operating in the areaNAPA, AutoZone, OReilly, and Advance. Additionally, HAP operates a rebuild shop serving these same retailers for major automobile components, such as carburetors, transmissions, and air conditioning compressors.

An important and difficult decision is facing the management of HAP: What to do with the business? The answer to this question management has turned to a new employee, Dee. Dee graduated last year with an engineering degree where he completed a course in engineering economics. Part of his job at HAP is to perform basic rate of return and an equivalent worth analyses on managements proposals. After acquiring information over the next few weeks, Dee outlined five options, including managements favorite of selling in 5 years. Dee summarized all the estimates over a 10-year horizon. These options and cash flow estimates were then delivered to upper management.

Option 2: Contract out rebuild operations. The cost to get the rebuild shop ready for an interested contractor to take over will cost $1M immediately. Annual revenues and expenses for HAP will be the same and should increase at the same rate as they would if option 1 was adopted. (HAP will no longer have control of the rebuild operation.) The financial advantage to HAP for the contract would be the annual fee paid to HAP from the contractor. This fee (good will) of $80,000 payable in year 1 would increase at a rate of 0.25% each year thereafter for the duration of the ten-year contract. The fee is consideration for the use of the HAP name and facility rental.

Option 3: Stop the Rebuild Enterprise, maintain status quo and sell out after 5 years (HAPs management favorite). The cash flow for this option will remain the same as option 1 until EOY 5. At EOY 5 and until EOY 9 HAP expects to receive the proceeds from a buyout offer from the contractor named in option 2. A $4M buyout will be spread uniformly over the years 5 to 9. At EOY 10 the contractor will assume full ownership of the HAP brand. The cash flow for the previous HAP owners (sellers) in EOY 10 will be 0.