CIS101 Introduction to Microcomputer Applications Excel 2013 Project Instructions CIS101 Professor Katz Page 1 of 6 7g General Instructions You will be creating an Excel workbook file that stores course and grade information for four semesters at school. Part A – First Semester Worksheet 1. Create a new blank workbook file in the CIS101xx\Excel folder on your flash drive with the following data on Sheet1: Credits Total Course ID Course Title Grade Earned Points CIS101 Intro to MS Office 3.50 3 EN101 Composition I 2.50 3 MA120 Business Math 2.50 3 BU201 Intro to Business 4.00 4 FR101 French I 3.25 3 Semester Total: Cumulative Total: 2. When creating this worksheet, autofit the columns so that the contents of each column are fully visible. 3. Save the workbook using the following filename: GPA_xxxx_yyyy.xlsx Where xxxx represents your first name and yyyy represents your last name. 4. Add a new column at the right that will be used (see Step 10c below) to calculate the Grade Point Average for the semester. Name the column heading GPA. 5. Add a new row above the first row a. In cell B1, include the title Fall Grades, Year 1 for the worksheet in this new row. b. Merge and center the title across columns B through F of the worksheet. c. Format the title text using a font face of your choice (do NOT use the default) and a font size of 14. Bold this text. d. Adjust the height of row 1 to approximately 65 and the width of column A to approximately 15. e. Insert a relevant picture file from the internet and resize it to fit in cell A1. f. Vertically center the title text. 6. Increase the font size of the rest of the worksheet to 12, and bold the cells that contain text data. If necessary, autofit the columns. 7. Total Points per course calculations: a. Multiply the Grade by the Credits Earned to calculate the Total Points for CIS101. Then copy that formula down the column for all the courses. (Remember, only use cell references in the formula, do NOT use the values within the cells.) (Continued on next page) CIS101 Introduction to Microcomputer Applications Excel 2013 Project Instructions CIS101 Professor Katz Page 2 of 6 7g 8. Semester Total calculations: a. Credits Earned – use arithmetic operators or a function to add up the credits earned for all the courses in the semester. b. Total Points – use arithmetic operators or a function to add up the total points for all the courses in the semester. c. GPA - Divide the Semester Total’s Total Points by the Semester Total’s Credits Earned. 9. Cumulative Total calculations (The Cumulative values represent the values from the current semester values, plus all the previous semester values. Since this worksheet represents the first semester, the Cumulative values will be the same as the Current Semester values): a. Credits Earned – use a cell reference to refer to the Current Semester Credits Earned. b. Total Points – use a cell reference to refer to the Current Semester Total Points. c. GPA - use a cell reference to refer to the Current Semester GPA. 10. Formatting: a. Center align the numeric data in each of the cells. b. All numeric data should have 2 decimal places visible, except the Credits Earned, which should not show any decimal places. c. Apply cell borders around all of the worksheet cell data. (Hint: when selecting all the cells, do not click on the cell with the picture as your first selected cell – the picture will be the only item that gets selected. Instead, begin your selection by selecting the cells from right to left or from bottom to top.) d. Apply a fill color(s) of your choice to the worksheet data. 11. Use Conditional Formatting and use the Color Scale of your choice to format the five grade cells. 12. Generate a Pie Chart of the Total Points Earned for each of the courses taken that semester, by performing the following steps: a. Select cells containing the five Course IDs and the cells containing the Total Points for each of the five courses taken (hold down the CTRL key when you make your selection) and generate a 3D Pie Chart. b. Move the chart so that it is below the worksheet data. c. Change the chart title to Points Earned - Fall Semester, Year 1. Use the same font face for your chart title as you used for your worksheet title. d. Display data labels outside the slices for the category names and percentages. Select New Line for the separator. e. Choose a Fill option for the data labels. f. Remove the legend. g. Explode the largest slice of the pie at 20%. h. Rotate the pie 30 degrees. (Continued on next page) CIS101 Introduction to Microcomputer Applications Excel 2013 Project Instructions CIS101 Professor Katz Page 3 of 6 7g Part B – Second, Third and Fourth Semester Worksheets 1. Copy the worksheet data from Sheet1 to and place the new sheet at the end of the workbook. 2. On the new sheet, Sheet1 (2), make the following modifications: a. Change the title in in merged cell B1 to Spring Grades, Year 1. b. Change the course, grade and credit information to values of your choice, and adjust column width if necessary. DO NOT use the original values! 3. Total Points for each course calculation – the formulas should be the same as those on Sheet1. (Since you created the worksheet by copying from Sheet1, the formulas should have been copied as well.) 4. Semester Total calculations – the formulas should be the same as those on Sheet1. 5. Cumulative Total calculations – the formulas will be different from those on Sheet1. (The Cumulative values represent the values from the current semester values, plus all the previous semester values. Since this worksheet represents the second semester, the Cumulative values will be different from the Current Semester values): a. Credits Earned – Add the Semester Total Credits Earned from the first semester worksheet and the Semester Total Credits Earned from the second semester worksheet. (You may use the SUM function if you like. Use sheet references to refer to the cells on the different worksheets.) b. Total Points – Add the Semester Total Total Points from the first semester worksheet and the Semester Total Total Points from the second semester worksheet. (You may use the SUM function if you like. Use sheet references to refer to the cells on the different worksheets.) c. GPA - Divide the Cumulative Total’s Total Points by the Cumulative Total’s Credits Earned. 6. Clear the Conditional Formatting of the Grades on this worksheet and re-apply Conditional Formatting using your choice of Data Bars. 7. Adjust the pie chart so that it has an appropriate title and the largest slice of the pie is exploded. 8. Copy the worksheet data from Sheet1 (2) to Sheet1 (3). 9. On Sheet1 (3), make the following modifications: a. Change the title in in merged cell B1 to Fall Grades, Year 2. b. Change the course, grade and credit information to values of your choice, and adjust column width if necessary. DO NOT use the same values! 10. Total Points and Semester Total calculations – the formulas should be the same as those on Sheet1(2). 11. Cumulative Total calculations – the formulas will be different from those on Sheet1 (2). (You will have to update these values to include all the previous semester values.) 12. Clear the Conditional Formatting of the Grades on this worksheet and re-apply Conditional Formatting using Highlight Cells Rules for those values greater than 3.25. 13. Adjust the pie chart so that it has an appropriate title and the largest slice of the pie is exploded. 14. Copy the worksheet data from Sheet1 (3) to Sheet1 (4). (Continued on next page) CIS101 Introduction to Microcomputer Applications Excel 2013 Project Instructions CIS101 Professor Katz Page 4 of 6 7g 15. On Sheet1 (4), make the following modifications: a. Change the title in in merged cell B1 to Spring Grades, Year 2. b. Change the course, grade and credit information to values of your choice, and adjust column width if necessary. DO NOT use the same values! 16. Total Points and Semester Total calculations – the formulas should be the same as those on Sheet1(3). 17. Cumulative Total calculations – the formulas will be different from those on Sheet1 (3). (You will have to update these values to include all the previous semester values.) 18. Clear the Conditional Formatting of the Grades and re-apply Conditional Formatting using Top/Bottom Rules to format cells that are Above Average. 19. Adjust the pie chart so that it has an appropriate title and the largest slice of the pie is exploded. 20. Rename the worksheets Fall1, Spring1, Fall2, Spring2, respectively. Part C – GPA by Semester Worksheet 1. Add a new worksheet at the end of the workbook file and name it Averages. 2. Include the following data on this worksheet: GPA by Semester Semester GPA Fall1 Spring1 Fall2 Spring2 Average GPA Minimum GPA Maximum GPA 3. Use the same formatting (font face, font sizes, text and background colors, picture, etc.) as you did for the semester worksheets. a. Merge and center the title across columns A and B of the worksheet. b. Format the title text as you did on the semester worksheets. c. Adjust the height of the first row to approximately 65. d. Vertically center the title text. e. Increase the font size of the rest of the worksheet to 12, and bold the cells that contain text data. If necessary, autofit the columns. f. Center align the GPA text and numeric data. g. All numeric data should have 2 decimal places visible. h. Use the same fill and borders as you did on the semester worksheets. 4. Semester GPA - use a cell reference to refer to each Semester GPA. (Continued on next page) CIS101 Introduction to Microcomputer Applications Excel 2013 Project Instructions CIS101 Professor Katz Page 5 of 6 7g 5. Average, Minimum and Maximum GPA’s – use the appropriate function that refers to the cells representing the GPA’s on this worksheet. (If you use the automatic function, make sure that Excel suggests the correct range of cells.) 6. Generate a bar chart of the various GPA data, by performing the following steps: a. Select all the cells on the worksheet (except the title) and generate a 2D Clustered Bar Chart. b. Move the chart so that it is below the worksheet data. c. Choose a chart style that displays a data label with the data series value at the end of each bar. d. Change the chart title to GPA by Semester. Use the same font face for your chart title as you used for your worksheet title. e. Do not include a legend or horizontal/vertical axis title. Part D – What-If Analysis Worksheet 1. Copy the worksheet data from Fall1 to a new worksheet and name it Fall1 Updated. 2. Move the Fall1 Updated worksheet to the end of the workbook file. 3. On Fall1 Updated worksheet, change the title in merged cell B1 to Fall Grades, Year 1 Updated 4. Clear the conditional formatting. 5. Delete the pie chart. 6. Use What-If Analysis with the Goal Seek tool to determine the grade you would have to have earned in Business Math to receive a 3.40 GPA for the semester. 7. Capture the Goal Seek screens (with the appropriate values filled in) that you used to determine the above result and print them in a new blank Word document. 8. Select the worksheet cells where the Business Math grade and Semester GPA have changed and modify the background color of those cells. Below the main portion of the worksheet, create a “legend” which identifies the cells with the updated values and fill color. 9. Increase the height of the row with the legend and wrap the descriptive text in the cell. (Continued on next page) CIS101 Introduction to Microcomputer Applications Excel 2013 Project Instructions CIS101 Professor Katz Page 6 of 6 7g Part D – Printing 1. Document the workbook file by adding your name as the author. 2. Group all the worksheets. a. Add a predefined header that displays your name, page number and date. b. Center the worksheet horizontally on the page. c. Scale the worksheet to fit on one page. 3. Print the entire workbook file using Portrait orientation. 4. While your document is still open, display the formulas on all your worksheets. 5. Change the page orientation to Landscape mode. 6. Ungroup the worksheets 7. Manually resize the columns so that all the formulas are visible. Some of the columns without formulas may be too wide and others too narrow. Adjust them as necessary. If needed, scale the worksheets to fit on one page. Display row and column headings. 8. Print the entire workbook file again with the formulas displayed. You do not need to save this view of the worksheet. To be submitted:  Hand in (you will have 13 printed pages in total): o The formatted printed version of the entire workbook file. (6 pages) o The version of each worksheet with the formulas displayed. (6 pages) o The Word document with your Goal Seek screen captures with your name. (1 page)  Upload your Excel document as named above to the BlackBoard Assignment named GPA Excel Project.