Set up the spreadsheet with the SCIM equations. To get started, setup a spreadsheet with the data in Exhibit 1.6. In your spreadsheet, make sure to calculate the totals that are given in the exhibit. For example, Total automotive sales is the sum of Automotive sales and Automotive leasing. Further, Total sales is the sum of Total automotive sales, and Services and other. The idea is to make the spreadsheet capture potential changes in the detail accounts. So, if Automotive sales changes, the Total automotive sales and Total sales number automatically change to reflect the update. a.Using the data in Exhibit 1.6 and your spreadsheet, calculate the profit margin, asset turnover, page 22and return on assets of the company as presented in Exhibit 1.6. (Hint: Profit margin and return on assets are negative.) b.To better understand how the SCIM works, complete the following table. Assume that the change suggested is only to the single account, and the other accounts are the same as presented in Exhibit 1.6. The change can be described in simple up, down, or does not change terms. 2.Analyze the impact of the increase in sales over the next year. eCar expects that automobile sales will increase significantly over the next year. During the year covered by the data in Exhibit 1.6, eCar produced and delivered about 370,000 cars. eCar expects that sales will increase to about 450,000 cars next year. Consider the following questions related to the impact of this increase in sales. a.Using your spreadsheet, calculate the average revenue (sales price) and cost of each car produced last year. Base the cost estimate on the automotive sales COGS (this reflects the variable cost for each car). b.Assume that the relationships between average revenue and cost stay the same next year (revenue/car and variable cost/car stay the same) and that nothing else changes in the financials. If sales do improve to 450,000 cars, what would be the expected profit margin, asset turnover, and return on assets if it were possible for the company to do this? c.What questions might you explore with management to evaluate the assumptions that were made in your calculations? 3.Analyze the impact of building a more efficient manufacturing plant. eCar recognizes that their current manufacturing plant is not very efficient. Investing in their plant would allow greater use of robots and other automation, thus reducing the cost to produce cars. This would be a major change for the company and would have broad implications for their financials. They anticipate that they could use their current cash to pay for the investment. Management also expects to raise the average revenue on each car to $65,000 by offering more options. They expect the average cost to build the cars to go down to $40,000 per car. This reduction is due to the reduction of some labor and better integration of the electronics in the car. They expect to be able to sell 450,000 cars next year. a.Return to the original data given in Exhibit 1.6. Assume that they use $2 billion of their cash and invest it in new equipment. Also, assume that they can raise the average revenue and lower the average cost as stated previously. What would be the expected profit margin, asset turnover, and return on assets for the company after the change? b.Looking at different worst- and best-case scenarios is often good. What if eCar can only increase the revenue per car to $58,000 and only reduce the cost to $42,000? What would be the expected profit margin, asset turnover, and return on assets if this happened (assume they make the investment in the equipment)? c.What questions might you explore with management to evaluate the assumptions that were made in your calculations