Data Management

Open e06c1AutoSales and save it as e06c1AutoSales_LastFirst.

Create appropriate range names for Purchase Price (cell C5), Sales Tax (cell C6), Down Payment (cell C7), Months Financed (C8), APR (cell C9), Down Payment Amount (cell C12), Amount Financed (cell C13), and Sales Tax (cell C14).

Edit the existing name range Tax_Owed to display as Tax.

Apply the range names to the existing formulas and functions in the worksheet.

Create a new worksheet labeled Range Names, paste the newly created range name information in cell A1, and then resize the columns as needed for proper display.

Goal Seek

The customer has budgeted a maximum of $500 per month for the car payment. Currently based on a purchase price of $50,000, a 10% down payment, and 7% sales tax you estimate monthly payments as $688.76. You will use Goal Seek to calculate the optimal purchase price to meet the customer’s budget.

Use Goal Seek to determine the optimal purchase price to reach a $500 monthly payment with all other variables remaining unchanged.

One-Variable Data Table

Your initial calculations were based on a $50,000 purchase price. You want to evaluate the change in down payment, taxes, and monthly payment at various purchase prices. You will create a one-variable data table using substitution values from $20,000 to $75,000 to complete the task.

Start in cell E5. Complete the series of substitution values ranging from $20,000 to $75,000% at increments of $5,000 vertically down column E. Apply Comma Style format to the range E5:E16 with no decimal points and preserve the thick bottom border on row 16.

Enter references to the Down Payment Amount, Tax Owed, and Monthly Payment in the correct location for a one-variable data table.

Complete the one-variable data table and format the results with Comma Style with no decimal places.

Apply Custom number formats to make the formula references display as descriptive column headings. Bold and center the headings and substitution values.

Two-Variable Data Table

Your customer wants to consider the impact of various down payments and purchase prices on the monthly payment. You will create a two-variable data table to complete the task.

Copy the purchase price substitution values from the one-variable data table and paste the values starting in cell J5. Adjust the width of column J as needed.

Type 10000 in cell K4. Complete the series of substitution values from $5,000 to $15,000 at $5,000 increments.

Enter the reference to the Monthly Payment function in the correct location for a two-variable data table.

Complete the two-variable data table and apply Comma Style to the range K4:M15 with no decimal places.

Apply a custom number format to make the formula reference display as a descriptive column heading. Bold and center the heading and substitution values.

Scenario Manager

Up to this point, you have created forecasts based on static amounts; however, it is important to plan for several possible finance options. To help you analyze best, worst, and most likely outcomes, you will use Scenario Manager.

Create a scenario named Best Case, using Purchase Price and Months Financed. Enter these values for the scenario: 40000, and 36.

Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 50000, and 72.

Create a third scenario named Most Likely, using the same changing cells. Enter these values for the scenario: 45000, and 60.

Generate a Scenario Summary report based on Monthly Payment.

Format the summary as discussed in the chapter.

Use Solver

You realize that while the best-case scenario provides the best price, it may not be economically feasible for the customer. You have decided to continue your analysis by using Solver to determine the perfect combination of Purchase Price, Down Payment, and Months Financed to reach an agreement with the customer.

Load the Solver add-in if it is not already loaded.

Set the objective to calculate a Monthly Payment of $500.

Use Purchase Price and Months Financed as changing variable cells.

Set constraints to ensure the Purchase Price is less than or equal to $50,000, greater than or equal to $30,000, and a whole number.

Set constraints to ensure months financed are less than or equal to 72, greater than or equal to 24, and a whole number.

Solve the problem. Generate the Answer Report. If you get an internal memory error message, remove Solver as an add-in, close the workbook, open the workbook, add Solver in again, and finish using Solver.

Save and close the file. Exit Excel. Based on your instructor’s directions, submit: e06c1AutoSales_LastFirst