EXCEL

Case Problem 3 – Data files needed Global.xlsx

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.