Please cancel this question.

ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT

In this assignment, you will produce a spreadsheet that models Electric Car’s financial situation. Then, in Assignment 2, you will use the spreadsheet for decision support and write a memorandum that documents your analysis and findings. In Assignment 3, you will prepare and give an oral presentation of your analysis and conclusions to company management. First, you will create the spreadsheet model of the decision. The model covers the three years from 2018 to 2020. This section helps you set up each of the following spreadsheet components before entering cell formulas: Constants Inputs Summary of Key Results Calculations Income and Cash Flow statements Debt Owed A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use; it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 7, and then select ElectricCar.xlsx.

Calculations section Charger Locations—There were 600 locations at the end of 2017. The number of locations will increase each year by the related value in the Inputs section. Change in Units Sold (% Points)—Three factors will affect the number of sports cars and family sedans sold in a year: the market momentum effect, the cost of gasoline, and the number of new charger locations. Market Momentum Effect—This percentage increase is a value from the Inputs section, which can be echoed here. Gas Price Effect—The direction of the price of gas is a value from the Inputs section. If the price is expected to decrease, the effect is minus 5 percent (in other words, unit sales will decline by 5 percent in the year). If the price is expected to increase, the effect is plus 5 percent (unit sales will increase by 5 percent in the year). If prices are expected to be stable, there is no effect.

New Charger Location Effect—If there are more than 100 new charger locations, the expected effect on units sold is plus 5 percent. If there are between 76 and 100 new charger locations, the effect is plus 3 percent. If there are between 26 and 75 new charger locations, the effect is plus 2 percent. Otherwise, there will be no effect. Total Change—The total expected percentage change in units sold is the sum of the market, gas price, and charger location effects. Units Sold–Sport—The number of units sold in a year is a function of the number sold in the prior year and the total percentage change in units expected. For example, if 53,000 sports cars were sold in 2017 and a 10 percent increase was expected in 2018, the number of units sold would be 1.10 × 53,000, or 58,300. Units Sold–Family—No family sedans were sold in 2017. Management thinks 20,000 will be sold in 2018, plus or minus the expected change in units sold. Thus, if a 10 percent increase was expected in 2018, the number of units sold would be 1.10 × 20,000, or 22,000. In succeeding years, the number of units sold will be a function of the number sold in the prior year and the total percentage change in units expected. Change in Selling Price (% Points)—Three factors will affect the selling price of sports cars and family sedans sold in a year: the market momentum effect, the cost of gasoline, and the number of new charger locations. Market Momentum Effect—A 2 percent increase is expected each year. You can hard-code a “2” in each year. Gas Price Effect—If gas prices are expected to rise, sports car and sedan selling prices will increase by 3 percent in the year. Otherwise, there will be no effect on selling prices. New Charger Location Effect—If the number of new charger locations exceeds 25, sports car and sedan selling prices will increase by 1 percent in the year. If there are 25 or fewer new charger locations, there will be no effect on selling prices. Total Change—The total expected percentage change in sports car and family sedan selling prices is the sum of the market, gas price, and charger location effects. Selling Price–Sport—The selling price in a year is a function of the prior year’s price and the total percentage change expected. For example, in 2017 the selling price was $75,000. If a 5 percent total change was expected, the 2018 selling price would be 1.05 × $75,000, or $78,750. Selling Price–Family—No family sedans were sold in 2017, so there was no 2017 selling price. Management thinks $35,000 should be the selling price in 2018, plus or minus the expected change. Thus, if a 5 percent increase was expected in 2018, the selling price would be 1.05 × $35,000, or $36,750. In succeeding years, the price will be a function of the price in the prior year and the total percentage change expected. Unit Cost–Sport—The unit cost of production in a year is a function of the prior year’s cost and the cost reduction factor, which is a value from the Inputs section. For example, the unit cost in 2017 was $78,000. If a 2 percent decrease was expected in 2018, the unit cost would be .98 × $78,000, or $76,440. Unit Cost–Family—No family sedans were made for sale in 2017. Management estimates a $30,000 unit cost in 2018, minus the effect of any cost reduction factor. Thus, if a 2 percent decrease was expected in 2018, the unit cost would be .98 × $30,000, or $29,400. In succeeding years, the unit cost will be a function of the cost in the prior year and the cost reduction factor expected in the year. Income and Cash Flow Statements The forecast for net income and cash flow starts with the cash on hand at the beginning of the year. This value is followed by the income statement and the calculation of cash on hand at year’s end. For readability, format cells in this section as currency with zero decimals. Values must be computed by cell formula; hard- code numbers in formulas only if you are told to do so. Cell formulas should not reference a cell with a value of “NA.” Your spreadsheets should look like those shown in Figures 7-5 and 7-6. A discussion of each item in the section follows each figure.