need help with completing this spreadsheet -- info you may need is below starting with exhibit 1

Road King Trucks Excel Spreadsheet Below is a list of items you need to identify and quantify

A B C D E F-P

Unit Year Year Year Years

Data 0 1 2 3-13

Price [sales begin]

Revenue

Labor cost per bus

Parts cost per bus

Engine costs per bus

Bus Warranty

Engine Warranty

SG&A costs

Depreciation

EBIT

Taxes

NOPAT

Depreciation Add Back

Operating Cash flow

Investment costs:

Land

Plant and Equipment

Working Capital [year 1 subtract, year 13 add back]

Salvage Summary: year 13

Land

Building clean-up costs

Equipment salvage costs

Total Salvage Value

Taxes

Net Salvage Value

TOTAL CASH FLOWS

Net Present Value using companys weighted average cost of capital (WACC)

Internal Rate of Return

NPV(rate,D34:P34)+C34

IRR(C34:P34)

Exhibit 1: Sales and Cost Forecast The sales forecast is based on projected levels of demand. All the numbers are expressed in todays dollars. The forecasted average inflation per year is 2.5% .

Price per bus $225,500

Units sold per year 7,750

Labor cost per bus $52,000

Components & Parts $94,000

Selling General & Administrative $250,000,000

NOTE: Average warranty cost per year per bus for the first five years is $1,100. 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 fourteen years. Afterwards, the designs become obsolete.

Engine choices

Engine Detroit engines Marcus engines

Price per engine, including installation $20,000 $18,000

Avg annual warranty cost per yr for 5yrs $1000 $1500

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.

Exhibit 2: Investment Needs To implement the project, the firm has to invest funds as shown in the following table:

Year 0 Year 1 Year 2

$400 million*+land co bought for $5 million. $284 million* Production and selling of busses starts *

Road King Trucks estimated that it would cost a total of $684 million to build the factory and purchase the necessary equipment to produce the buses. Use straight line depreciation of $684 million over 12 years for the sake of simplicity: $57 million

The company will have to allocate funds to net working capital (NWC) for parts inventories equivalent to 8% of annual sales, spent in year 1 and sold off in year 13 at the end of the project.

Assume that the land, factory, and equipment will be sold at the end of the project. The company expects to spend about $300,000 demolishing the factory and cleaning the land. The company expects to sell the land for its current value. The equipment will be sold for salvage at about $15,000,000.