Case Problem-
Global Site GPS - Kevin Hodge is a production assistant at Global Site GPS, a leading manufacturer of GPS devices located in Crestwood, Missouri. One of Kevin's jobs is to monitor output at the company's five regional plants. He wants to create an Excel workbook that reports the monthly production at the five sites, including the monthly average, minimum, and maximum production and total production for the previous year. He asks you to crate the workbook that reports these stats.
Complete the following steps:
1. Open the Global workbook located on your flash drive, and then save it as Global Site.
2. Rename the sheet1 worksheet as Production History, and then inset 12 new rows at the top of the worksheet.
3. Increase the width of column to 23 characters and the width of columns B through F top 14 characters.
4. In the range B7:F7, enter the titles Plant1, Plant2, Plant3, Plant4 and Plant5, respectively.
5. In the range A8:A11, enter Total Units Produced, Average per Month, Maximum, and Minimum, respectively.
6. Select the range B26:F26, use AutoSum to calculate the sum of the production values for each of the five plants, and then drag and drop the selected cells to the range B8:F8.
7. Select the range B26:F26, use AutoSum to calculate the average of the production values for each of the five plants, and then drag and drop the selected cells to the range B9:F9.
8. Repeat Step 7 to calculate the maximum values for each of the five plants and then move those calculated values to the range B10:F10, and then repeat to calculate the minimum production values and drag and drop those calculated values to the range B11:F11.
9. In the Production History worksheet, enter the following data:
Cell Data Cell Data
A1 Global Site Gps
A2 Production Report
A3 Model B3 MapTracker 201
A4 Year B4 2010
A5 Total Units Produced
10. In cell B5, use the SUM function to add the values in the range B8:F8.
11. Insert a new worksheet named Plant Directory as the first worksheet in the workbook.
12. In cells A1 and A2, enter Global Site GPS and Plant Directory, respectively, and then enter the text given to you by your instructor
|
Plant
|
Plant Manager
|
Address
|
Phone
|
|
1
|
Karen Brookers
|
300 Commerce Ave
Crestwood, MO 63216
|
(314)555-3881
|
|
2
|
Daniel Gomez
|
15 North Main Street
Edison, NJ 08837
|
(732)555-0012
|
|
3
|
Jody Hetrick
|
3572 Howard Lane
Weston, FL 33326
|
(954)555-4817
|
|
4
|
Yong Jo
|
900 South Street
Kirkland, WA 98033
|
(425)555-8775
|
|
5
|
Sandy Nisbett
|
3771 Water Street
Helena, MT 59623
|
(406)555-4114
|
13. Set the width of Column B to 15 characters, the width of column C to 30 characters, and the width of column D to 16 characters. Autofit the height of each rows to its content.
14. Insert a new worksheet named Documentation as the first worksheet in the workbook, and then enter the following data
Cell Data Cell Data
A1 Global Site Gps
A3 Author B3 Your Name
A4 Date B4 the current date
A5 Purpose B5 Production report for Global Site GPS
15. Switch the Production History worksheet to Page Layout view, change the orientation to landscape, and then verify that the worksheet fits on a single page.
16. Save the workbook.