3 part seperate spread sheets each part applied spread sheets…..text book Shelly, G.B., Quasney, J.J., & Jones, B. J. (2012) Microsoft Excel 2010: Comprehensive Concepts & Techniques. Boston: Cengage.

Using excel

SPREAD SHEET 1

1. Apply a master sheet to create a multiple-sheet workbook in this lab work activity.

For this week’s individual work, please complete the following:

• In the Lab:  Lab 1: Using a Master Sheet to Create a Multiple-Sheet Workbook, EX 415 – 417. 
  • For Instructions Part 1: Skip directions 5, 6, & 8
  • For Instructions Part 2: Skip direction 7
  • PART 1

  The master sheet used to create the profi t potential analysis workbook is part of the Data Files for Students. Alice Stewart, the company’s accountant, has asked you to use the master sheet to create the profi t potential analysis workbook. Instructions Part 1: Perform the following tasks.

  1. Open the workbook Lab 6- 1 A tHome Blu- Ray Master from the Data Files for Students. Save the workbook as a workbook using the fi le name, Lab 6- 1 At Home Blu- Ray Profi t Potential Analysis.

  2. Add a worksheet to the workbook between Sheet1 and Sheet2 and then paste the contents of Sheet1 to the three empty sheets.

  3. From left to right, rename the sheet tabs Consolidated, Chicago, New York, and Seattle. Color the tabs as shown in Figure 6 – 65. ( The Consolidated tab uses the Tan, Accent 1 color.) On each of the three store sheets, change the subtitle in cell A2 to match the sheet tab name. Change the title style for each title area in the range A1: F1 to match the sheet tab color. Enter the data in Table 6 – 12 into the three store sheets.

  4. On the Consolidated worksheet, use the SUM and AVERAGE functions, 3- D references, and copy and paste capabilities of Excel to total the corresponding cells on the three store sheets. First, compute the sum in cell B4 and then compute the average in cell C5. Copy the range B4: C5 to the range B5: C11. The Consolidated sheet should resemble Figure 6 – 65.

  PART 2

  Instructions Part 2: Complete the following tasks. 1. Start Excel. Open the workbook Lab 6- 1 At Home Blu- Ray Profi t Potential Analysis. 2. Select the range D6: H11 on the Consolidated worksheet. Select all the worksheets. 3. Use the Format Cells dialog box to apply a custom format of #,## 0.00; [ Green](#,## 0.00). 4. Select the cell B12 on the Consolidated worksheet. Select all the worksheets. 5. Use the Format Cells dialog box to apply a custom format of #,## 0 “ Units”. Widen Column B so that contents of cell B12 are visible. 6. Save the workbook and then close the workbook.

   

  SPREAD SHEET 2

  Now that you have discussed PivotTables and PivotCharts, it is time to create a spreadsheet that includes both.

  For this week’s lab work, please complete the following:

• In the Lab: Lab 1: Creating  a PivotTable Report and PivotChart Report, EX 547-548.
• Lab 1: Creating a PivotTable Report and PivotChart Report Note: To complete this assignment, you will be required to use the Data Files for Students. See the inside back cover of this book for instructions on downloading the Data Files for Students, or contact your instructor for information about accessing the required fi les. Problem: You work for RealQuick Connect and help the fi nancial director prepare and analyze revenue and expenses. He has asked you to create two PivotTables and corresponding PivotCharts based on sales data. One PivotTable and PivotChart summarize the sales by Supplier ( Figure 8 – 96a). The other PivotTable and PivotChart summarize the computer sales by month for the top computer supplier ( Figure 8 – 96b). Instructions: Perform the following tasks.
• 1. Open the workbook Lab 8- 1 RealQuick Connect from the Data Files for Students, and then save the workbook using the fi le name, Lab 8- 1 RealQuick Connect Complete.
• 2. Using the data in the Sales Results worksheet, create the PivotTable and associated PivotChart shown in Figure 8 – 96a in a separate sheet in the workbook. Name the worksheet Sales By Supplier.

3. If necessary, add two blank lines above the PivotTable, and then change the contents of cell A4 to Supplier and cell B3 to Stores. Apply the Pivot Style Dark 3 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Apply the chart Style 28 to the PivotChart. Resize the PivotChart to cover the range A20: G37 and then hide the fi eld buttons.

4. Create a second PivotTable and associated PivotChart as shown in Figure 8 – 96b in a separate sheet in the workbook. Name the worksheet Computer Sales By Month.

5. Change the contents of cell A4 to Month and cell B3 to Store. Apply the Pivot Style Dark 3 style to the Pivot Table. Format the values as Currency values with a dollar sign and no decimal places. Turn off the legend for the chart.

6. Filter the category by Computer. Filter the store to Green Spring Club.

7. Change the chart type to Line and then add a linear trendline that forecasts the trend for four more months. Apply the Chart Style 28 to the PivotChart, and hide the fi eld buttons.

8. Change the document properties as specifi ed by your instructor. Change the Computer Sales By Month worksheet header to include your name, course number, and other information as specifi ed by your instructor.

 

SPREAD SHEET 3

 

This week’s lab work is to construct a final project.

The final is broken up into two parts.

After reviewing the textbook chapters and links from previous weeks in eCollege, complete the final project as instructed. You will create two workbooks:

• A budget using:
  •  Cases and Places 3: Analyzing Profit Potential, EX 135.

  Analyzing Profi t Potential Professional You work for HumiCorp, an online retailer of home humidifi ers. Your manager wants to know the profi t potential of their inventory based on the items in inventory listed in Table 2– 12. Table 2– 12 contains the format of the desired report. The required formulas are shown in Table 2– 13. Use the concepts and techniques developed in this chapter to create and format the worksheet. The company just received 67 additional desk- sized humidifiers and shipped out 48 room- sized humidifiers. Update the appropriate cells to reflect the change in inventory.

• An Excel spreadsheet that uses functions and problem solving skills:
• Cases and Places 1: Inventory Level Priority, EX 359.

Apply your creative thinking and problem solving skills to design and implement a solution. 1: Inventory Level Priority Academic Create an Intro to Biology grade table from the data in Table 5 – 6. Also include a Final % fi eld and a Grade fi eld. Both are calculated columns. Final % is calculated with each quiz being worth 10% and each test being worth 35%. Create a Grade table in the range J6: K20 using the data shown in Table 5 – 7. Use the VLOOKUP function to determine the grade to assign to each record. Add the total row to the table. Show the averages of each quiz and test. In cell range A19: B19 show Mark Kennedy’s grade using the MATCH and INDEX functions.