excel

Project K

In this project, you are working with inventory information that you receive weekly from Reynolds

Used Cars. You’ll set up scenarios, create a scenario summary, and create a 2-input data table.

I have supplied a file called Project_K_yourname. Review it so that you can identify the functions and

other data that are set up for you to do your data analysis.

 I3:J8: resource information for the payment function

 J4: valid entries: Excellent, Good, Fair, Poor)

 J5: VLOOKUP function to find the valid APR in the Lookup table

Getting Started 1. Download the Project_K_yourname workbook from the assignment in Isidore.

2. When you save the file on your computer, change yourname to your own name. For example, my

workbook is named Project_K_mkreynolds

3. Set up the following, using the Header & Footer tools:

Note: Go to Page Layout View, and click in the left header section. You’ll see the contextual tools

appear in the title bar at the top of the window.

 Header: In the left header section, click the Sheet Name button, so it displays the worksheet

name in the result. You’ll see that Excel enters the code &[Tab].

 Footer: Put your name in the center footer section

4. Click any cell in the worksheet, and then press Ctrl+Home to return to cell A1.

5. Return to Normal view. (If you normally use the View menu to change views, try using the buttons

on the status bar at the lower right side of the windows. Or if you normally use the buttons, try the

View menu. Learn to use different methods to accomplish tasks.)

Project K Case The sales reps at Reynolds Used Cars like to have some information about potential payments, interest,

and credit rating, so that it is easily accessible when they are working with a client. In the file, you’ll set

up 4 scenarios and a data table for the sales reps to use.

Working with Scenario Manager 1. Go to Scenario Manager (as you did in Unit K) and set up the following 4 scenarios. Make sure you

include your own name in the comment box. If you have Excel registered to you, it will appear

automatically. If not, you’ll have to change it.

Caution: Do not type a space after the value. For example, it should be Good not Good[space]

 Scenario 1: Good Rating

Changing cells: J4

Comment: your name

Scenario values: Good

BAI 103L Page 2 of 4

 Scenario 2: Fair Rating

Changing cells: J4

Comment: your name

Scenario values: Fair

 Scenario 3: Excellent Rating

Changing cells: J4

Comment: your name

Scenario values: Excellent

 Scenario 4: Poor Rating

Changing cells: J4

Comment: your name

Scenario values: Poor

2. Show each scenario, and view the results.

3. Show the Good Rating scenario.

4. Select I4:J8, and create names using the Create from Select button on the Formulas tab (Defined

Names group). Use the left column for the names. This step will make your scenario summary

more readable.

5. Generate a scenario summary, using the

default suggestions in the dialog box.

(The result cells are all of the Payment

cells containing the PMT formula in

column G. Excel can only do a

maximum of 32 result cells, so that’s

why I limited the amount of inventory.)

6. On the Scenario Summary worksheet do

the following:

a) Delete the notes in B39:B41.

b) Delete the Current Values column.

c) Change the title in cell B2 to Credit

Rating Scenarios.

d) In cell B8, type = and click the

inventory worksheet tab. Then select

cell C2, and press Enter. This puts

2012 in cell B8 (Formula: ='inventory'!C2). AutoFill the formula down the column.

e) In cell C8, type =CONCATENATE(

and click the Insert Function [fx]

button on the left of the formula bar

to open the function argument dialog

box. Use the following arguments:

 Text1: cell A2 on the inventory

worksheet

 Text2: a space

 Text3: cell B2 on the inventory

worksheet

BAI 103L Page 3 of 4

f) Select cell C8 (where you just put the

Concatenate function), and Autofill through

row 39.

g) Autofit column C so that all vehicle names are

displayed clearly.

h) Change the width of column B to 6 characters.

(You should be in Normal view. In Page

Layout view, the measurement changes to

inches, which we don’t want.)

i) Finally, replace the purple fill in cells B2:G3

with a dark fill color that shows the white text.

(You choose the color.)

Setting up a Data Table The sales representative may want to see results based

on a couple of factors. In this example, you’ll set up a

data table that shows varying payments, based on the

APR and the number of years in the loan. I’ll walk you

through this, since you didn’t create a two-input data table in the book.

1. You want to see various payments for the Ford Focus in row F, so select F1:F6 and make the cells

bold, just as a visual clue to what you are doing.

2. In cell L1 on the inventory worksheet, type 2015 Ford Focus Payment Chart

3. In cell L2 on the inventory worksheet, type =F6, which points to the payment formula for the 2015

Ford Focus in row 6.

4. Copy the APR list from the lookup table: copy cells J12:J15 and paste them starting in cell L3.

Note: You copied the formatting also, but we’ll fix that when we fix the data table formatting.

5. Now we’ll enter the Years in Loan at the top of the chart. Type 4 in M2, 5 in N2, and 6 in O2.

What you’ve done is identified the variables for the PMT formula in cell F6: the APR choices are

set up in a column, so its cell (J6) becomes “column input” and the Years in Loan choices are set

up in a row, so its cell (J5) becomes “row input” when you complete the data table input.

6. Select the table range: L2:O6.

7. Go to Data Table under What-if Analysis, and enter J6 (Years in Loan) for the row input, and J5

(APR) for the column input in the dialog box. Click OK.

BAI 103L Page 4 of 4

8. Excel fills in the Data Table, but we still need to fix formatting:

a) Since the table range L2:O6 is still selected, we’ll fix the borders and fill color. Open the

Format Cells dialog box, by pressing Ctrl+1. Make sure to use the key above the Q on the

QWERTY keyboard. The numeric keypad doesn’t work for this.

b) Go to the Border tab, if necessary. Above the Preview are three buttons. Click the button

labeled None to get rid of the borders that we copied into the data table.

c) Still on the Border tab, select the line style that you want for an outline around the table. Next,

choose the color. Finally, click the Outline button to draw a border in the Preview. What you see

in the Preview is what you will get when you click OK.

d) Select M3:O6, and set the number format to Accounting (2 decimals).

e) Adjust column widths.

f) Select cell L2, and insert the following comment, using the New Comment button in the

Comments group on the Review tab. Note: You can copy the text from these instructions and

paste them into the comment.

 Comment text: To change the table to show payments for a different car, edit the L2 cell so that

says =cellref. The word cellref means the column letter and row number that make up the cell

reference. Ex: =F2 points to the payment for the car in the top row.

 Display the comment (Show all comments).

 Move the comment so we can see the table.

Finishing the Assignment 1. Save your file, and exit Excel.

2. Log into Isidore, and submit your completed file under Assignment 27 – Project K