Unformatted text preview: CIS 310
Assignment 3 Spreadsheet Design and Modification Project Dr. Manson; Winter 2011 DUE DATE: Tuesday, March 8th 2011 by 5 p.m. Academic Integrity Note: this is an individual assignment. Collaboration with another student is not allowed. Any sharing of work or factual data between students constitutes academic dishonesty as defined in the University Catalog. All work must be original for this project in this quarter. You may not use work from other classes or courses to fulfill this assignment. Project Objectives: This project is designed to give you a better understanding of how data can be organized into a relational database and then used to provide answers to management queries and to extract information for reports. The software we will be using for Assignment 3 is Microsoft Excel 2007, which is part of the Microsoft Office 2007 Suite. You will be submitting 2 spreadsheets, completed as shown below in Task 1 and Task 2. Task 1: The Registrar office at the Lifetime Learning College has asked for an analysis of the following enrollment data: The Lifetime Learning College Fall 12,344 6,534 4,422 3,224 1,231 Spring 14,813 7,841 5,306 3,869 1,477 Summer 9,234 5,332 3,892 2,367 835 Total Full
time Students Part time Students On
line Students Distant Learning Students Out Of State Residents Term Total 1 INSTRUCTIONS: 1. Enter the data above into a blank worksheet. Save the spreadsheet file and name the file XYEnrollment (where “X” is the first initial of your first name, and “Y” is the first initial of your last name). [for example: a spreadsheet file for Alexander Smith will be named ASEnrollment). Merge and center the heading across the columns of your worksheet. Make the heading bold and change the font size of 12. Center and make bold the column headings. Use the AutoSum button to enter the SUM function to calculate Term Total for Fall, Spring and Summer columns. Use the AutoSum button to enter a SUM function in the Total column for Full
time Students. Then, use the fill handle to fill in the SUM function to the remaining rows under Total. Chart the data using an embedded Clustered Column chart. Save the spreadsheet. 2. 3. 4. 5. 6. 7. Task 2: INSTRUCTIONS: 1. Download and open the worksheet, Excel_Data_3.2.xlsx. Save it as Excel_Case_Project_3.2.xlsx. Save the spreadsheet file and name the file XYGlobal (where “X” is the first initial of your first name, and “Y” is the first initial of your last name). [for example: a spreadsheet file for Alexander Smith will be named ASGlobal). Merge and Center the worksheet heading across the columns of your worksheet. Use the fill handle in the cell containing January to fill
in the remaining months in the Usage section of the spreadsheet. Sum the Total Units for the January column. Copy the Total Units formula to the remaining months of the worksheet. Sum all Residential Units for January through June in the Total column. Copy the Total formula to the Commercial Units row. Average all the Residential Units for January though June in the Average column. 2 2. 3. 4. 5. 6. 7. 8. 9. 10. Copy the Average formula to the Commercial Units row. Use the fill handle in the cell containing January to fill
in the remaining months in the Revenues section of the spreadsheet. 11. Calculate the Residential Revenues for January by multiplying January's Residential Units in the Usage section by the Residential Rate. Make the reference to the Residential Rate cell an absolute reference. 12. Copy the Residential Revenues formula for January to the remaining months. 13. Calculate the Commercial Revenues for January by multiplying January's Commercial Units in the Usage section by the Commercial Rate. Make the reference to the Commercial Rate cell an absolute reference. 14. Copy the Commercial Revenues formula for January to the remaining months. 15. Sum the Monthly Total for the January column. 16. Copy the Monthly Total formula to the remaining months of the worksheet. 17. Sum all Residential Units for January through June in the Total column. 18. Copy the Total formula to the Commercial Units row. 19. Average all the Residential Units for January though June in the Average column. 20. Copy the Average formula to the Commercial Units row. 21. Use the Currency button to display the numbers as currency. Use the Decrease Decimal button so that the decimal places are no longer displayed. 22. Chart the data for the Residential and Commercial revenues for January through June using a stacked column chart with a 3–D visual effect. Include a chart title and a legend with the chart. 23. Change the name of Sheet1 to Revenues. 24. Print the worksheet. 25. Save and close the worksheet. 3 ...
View
Full Document
 Winter '07
 Onlineclass
 the00

Click to edit the document details