Reference no: EM131119890

Excel Unit Exam

Print the instruction sheet and then open up the College Resources and Expenses data file in Excel 2013. When you have completed all of the steps, upload the finished file into the appropriate D2L dropbox.

1. Using Column F, calculate the totals for each of the resources and expenses. Label the column appropriately. Format the total values with an accounting style and two decimal places. Format all other values with comma style and two decimal places.

2. Using Column G, calculate the average for each of the resources and expenses. Label the new column appropriately. Format the average values with an accounting style and two decimal places.

3. Add three blank rows above Row 9.

4. Using Rows 9, 10, 19, 20, calculate the total and average for each of the four years in school. Label the rows appropriately. Format the average values with an accounting style and two decimal places.

5. Insert a bottom border in Row 8 and 18.

6. Using an absolute reference for Row 22, calculate a 4% increase from Row 19. Label and format the row appropriately. Apply a cell style of your choice.

7. Merge and center the contents of A1 over all columns. Apply a fill color and font color of your choice. Increase the point size of the row to 18 and bold.

8. Merge and center the contents of A3 over all columns. Apply a fill color and font color of your choice. Increase the point size of the row to 16 and bold.

9. Merge and center Row 12 across all columns. Apply a fill color and font color of your choice. Increase the point size of the row to 16 and bold. Center and bold the column headings. Center, bold, and italicize the row headings.

10. Apply Conditional Formatting as follows:

a. Use light red fill with dark red text for the range B5:E8 for resources less than $2,000.
b. Use green fill with dark green text for the range B14:E18 for expenses between 500 and 3,000.
c. Use Icon Sets 5 Arrows for Column F.
d. Use Color Scales Green-Yellow-Red Color Scale for Column G.

11. Create four charts. One must be a clustered column chart and a 3D pie chart. The other two you can decide. Two charts must be from the Resources Section and the other two from the Expense section. At a minimum, each chart must have the following:

• Chart Title
• Footer with the name of the file and a header with your name
• Legend (each chart should have the legend in a different location)
• Horizontal and Vertical axis labels (Except the pie chart)
• Change the value axis to zero decimal places. (Except the pie chart)
• For the Pie chart include a percent label.
• One chart is embedded and the other three charts on individual worksheets that have tab labels.
• Two charts must have an annotation of your choice with an arrow. Include an appropriate shape with a text box on a third chart of your choice. Two charts should be on separate sheets.
• Modify each chart as necessary to improve its appearance and effectiveness.

Attachment:- College_Resources_and_Expenses_Data_File_2016.xls