Microsoft Office Tutorials and References
In Depth Information
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 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
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
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.
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.
Use Figure 1-41, which
shows the “end results,”
to create a workbook
budget figures over a
three-month period for
a college student.