Microsoft Office Tutorials and References

In Depth Information

**Session 1.2**

Case Problem 3

Data File needed for this Case Problem: Site1.xls

Kips Shoes
Kips Shoes is planning to build a new factory. The company has narrowed the

site down to four possible cities. Each city has been graded on a 1-to-10 scale for four cate-

gories: the size of the local market, the quality of the labor pool, the local tax base, and the

local operating expenses. Each of these four factors is given a weight, with the most impor-

tant factor given the highest weight. After the sites are analyzed, the scores for each factor

will be multiplied by their weights, and then a total weighted score will be calculated.

Challenge

Challenge yourself by

going beyond what

you’ve learned to create

a worksheet that calcu-

lates the weighted

scores of four possible

locations for a new

shoe factory.

Gwen Sanchez, the senior planning manager overseeing this project, has entered the

weights and the scores for each city into an Excel workbook. She needs you to finish the

workbook by inserting the formulas to calculate the weighted scores and the total overall

score for each city. To complete this task:

1. Open the
Site1
workbook located in the Tutorial.01\Cases folder included with your

Data Files, and then save the workbook as
Site2
in the same folder.

2. Switch to the Site Analysis sheet.

3. In cell B14, calculate the weighted Market Size score for Waukegan by inserting a

formula that multiplies the value in cell B7 by the weight value in cell G7.

4. Insert formulas to calculate the weighted scores for the rest of the cells in the range

B14:E17.

5. Select the range B18:E18, and then click the AutoSum button to calculate the sum of the

weighted scores for all four of the cities. Note that you can apply the AutoSum button to

more than one cell at a time. Which city has the highest weighted score?

6. Switch to the Documentation sheet, and enter your name and the date in the appro-

priate locations on the sheet.

7. Spell check the workbook, print the entire workbook in portrait orientation, and then

save your changes to the workbook.

8. Gwen has another set of weighted scores she wants you to try. However, she doesn’t

want you to enter the new values in the Site Analysis worksheet, so you need to make

a copy of the worksheet. To learn how to copy a worksheet, open the Excel Help task

pane, and then enter “copy a worksheet” in the Search for text box. Scroll the list of

topics in the Search Results task pane to locate the topic “Move or copy sheets.”

Open the topic, read the information about copying a sheet, and then close the

Microsoft Excel Help window and the Search Results task pane.

9. Using what you learned in Step 8, create a copy of the Site Analysis worksheet, placing

the new worksheet at the end of the workbook. Rename the new sheet “Site Analysis 2”.

10. In the Site Analysis 2 worksheet, change the weighted scores of Market Size to 0.2

and Labor Pool to 0.4. Which city has the highest weighted score now?

11. Print the contents of the Site Analysis 2 worksheet.

12. Save the workbook as
Site3
in the Tutorial.01\Cases folder, and then close the workbook.

Explore

Explore

Explore

Case Problem 4

There are no Data Files needed for this Case Problem.

Monthly Budget
Alice Drake is a first-year student at MidWest University and has a part-

time job in the admissions department. Her college-related expenses, such as tuition,

books, and fees, are covered through grants and scholarships, so the money Alice makes

goes towards her personal expenses. Being on her own for the first time, Alice is finding it

difficult to keep within a budget. She has asked you to look at her finances and help her

figure out how her money is being spent. Figure 1-41 shows the worksheet that you will

create to help Alice analyze her budget.

Create

Use Figure 1-41, which

shows the “end results,”

to create a workbook

containing monthly

budget figures over a

three-month period for

a college student.