Illustrated Excel 2016 | Module 4: SAM Project 1a Felix's Finances Working with Charts   GETTING STARTED ·              Open the file IL_EX16_4a_FirstLastName_1

Subject:MS ExcelPrice:20.87 Bought21

Share With

Illustrated Excel 2016 | Module 4: SAM Project 1a

Felix's Finances

Working with Charts

 

GETTING STARTED

·              Open the file IL_EX16_4a_FirstLastName_1.xlsx, available for download from the SAM website.

·              Save the file as IL_EX16_4a_FirstLastName_2.xlsx by changing the "1" to a "2".

o     If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

·              With the file IL_EX16_4a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o     If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

 

 

PROJECT STEPS

1.       Felix works two jobs and tracks his yearly income and expenses in an Excel workbook. He uses charts to visualize his data, and he has asked you to help him format the charts.

Switch to the Income 2018-2021 worksheet. The stacked column chart on this worksheet allows Felix to see the contribution that each of his jobs had on combined income for the year.

In the Combined Income 2018-2021 chart, format the chart title border with a Blue, Accent 1 (5^th column, 1^st row of the Theme Colors palette) border color using the Solid line border option, then apply the Offset Bottom Shadow effect (in the Outer group) to the title. [Mac Hint: In the Format Chart Title pane, change the Shadow Distance to 3 pt.]

2.       In the Combined Income 2018-2021 chart, make the following changes:

a.       Remove the primary major vertical gridlines.

b.       Add primary major horizontal gridlines to the chart.

3.       In the Combined Income 2018-2021 chart, update the primary vertical axis as described below:

a.       Add a primary vertical axis title to the chart, and enter the text Amount ($) as the title.

b.       Change the font type of the primary vertical axis title to Verdana and the font size to 11 pt.

4.       Switch to the Expenses 2018-2021 worksheet. The 3-D pie chart on this worksheet shows how each of Felix's expenses affected his total budget from 2018 to 2021.

Enter the text Total Expenses 2018-2021 by Category as the chart title.

5.       Apply the Style 10 chart style to the 3-D pie chart.

6.       Felix notices that rent (the light blue slice) was his biggest expense.

In the 3-D pie chart, explode the largest slice of the pie (representing rent) by 15%.

7.       Switch to the Budget worksheet. The clustered column chart in this worksheet represents how Felix's expenses have changed.

Resize and reposition the clustered column chart so that its upper-left corner is within cell G2 and its lower-right corner is within cell R21.

8.       In the clustered column chart, enter Expenses 2018-2021 by Category as the chart title.

9.       In the clustered column chart, move the chart legend to the Bottom position.

10.    In the clustered column chart, select the data series representing FY 2021. Change the fill color of that data series to Yellow (4^th column, 1^st row of the Standard Colors palette).

11.    Felix would also like to create a chart representing how much his budgeting has contributed to his total savings each year.

Select the range A25:E26 and create a 2-D pie chart. Update the pie chart as described below:

a.       Enter 2018-2021 Savings as the chart title.

b.       Resize and reposition the chart so that its upper-left corner is within cell G22 and its lower-right corner is within cell M35. 

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Income 2018-2021 Worksheet

 

Final Figure 2: Expenses 2018-2021 Worksheet

 

Final Figure 3: Budget Worksheet