Tesla has recently set up a Mega factory in China. The factory can produce three models for the Chinese customers: Model X, Model S, and Model C. All three models go through three main steps during the manufacturing process: Battery Manufacturing, Body Stamping, and Final Assembly. All cars share the same battery manufacturing, body stamping, and assembly line capacity. Table 3 summarizes cost and sales data for all three models, and Table 4 shows the available capacity in hours and time required per car for each process.

a. Develop a spreadsheet model and use Solver to find the best product mix ( number of cars to make) for Tesla. (25)

b. The marketing manager of model C insists that they should make at least 5000 cars of model C. How will you respond to this request? (3)

c. What is the maximum number of model C cars that Tesla can make? (2)

Table 3: Cost and Market Data Model X Model S Model C Selling price ($/car) 35,000 38,000, 30,000 Direct Material Cost ($/car) 20,000 22,000 18,000 Labor Csost: Battery manufacturing ($/car) 1,200 1,200 1,100 Stamping ($/car) 1,500 1,700 1,200 Assembly Line ($/car) 1,700 1,900 1,500Table 4: Labor and Capacity Model X Model S Model C Total Capacity available Hr/ car Hr/car Hr/car Labor Hrs Battery Manufacturing 20 2.0 2-5 18,000 Stamping 1.5 1.7 1-2 15,000 Assembly Line 1.7 1.9 1-5 16,000