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".

oIf 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.

oIf 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

Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Illustrated Excel 2016 | Module 4: SAM Project 1a Felix's Finances WORKING WITH CHARTS Delvin Mines-Allen ot edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Combined Income 2018-2021 45,000 40,000 35,000 30,000 25,000 Valet Parking Waiter 20,000 15,000 10,000 5,000 - FY 2018 FY 2019 FY 2020 Year FY 2021 Chart Title Rent Car Payment Gas Utilities Food Tuition Books Other Felix's Finances Income Income Year Waiter Valet Parking Total FY 2018 FY 2019 FY 2020 FY 2021 20,420 24,500 22,300 25,650 10,960 12,300 12,950 13,420 31,380 36,800 35,250 39,070 Total 92,870 49,630 142,500 FY 2018 14,000 12,000 10,000 Expenses Year Expenses Rent Car Payment Gas Utilities Food Tuition Books Other Total FY 2018 FY 2019 FY 2020 FY 2021 6,600 7,500 12,000 12,000 4,200 4,200 5,500 5,500 2,200 2,300 2,000 2,000 600 700 750 760 4,800 5,000 5,200 5,250 2,800 3,000 3,100 3,120 650 600 750 780 3,600 3,650 3,500 3,850 25,450 26,950 32,800 33,260 Total 38,100 19,400 8,500 2,810 20,250 12,020 2,780 14,600 118,460 Savings Savings Year Amount Saved FY 2018 FY 2019 FY 2020 FY 2021 5,930 9,850 2,450 5,810 Total 24,040 Amount ($) 8,000 6,000 4,000 2,000 - Rent Car Payment Chart Title FY 2018 Rent Car Payment FY 2019 Gas FY 2020 FY 2021 Utilities Expense Type Food Tuition Books Other 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 (5th column, 1st 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 3 4 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. 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. 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. Illustrated Excel 2016 | Module 4: SAM Project 1a 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 (4th column, 1st 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. Illustrated Excel 2016 | Module 4: SAM Project 1a Final Figure 1: Income 2018-2021 Worksheet Final Figure 2: Expenses 2018-2021 Worksheet Illustrated Excel 2016 | Module 4: SAM Project 1a Final Figure 3: Budget Worksheet Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Illustrated Excel 2016 | Module 4: SAM Project 1b Yummy Gelato Stores WORKING WITH CHARTS Delvin Mines-Allen ot edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. January-April Revenue 60,000 50,000 40,000 Revenue ($) 25,641 12,864 30,000 13,768 11,221 20,000 24,788 10,000 - 20,707 January February Boardwalk Downtown 26,501 22,758 March April Chart Title 2%1% 5% 23% 8% 7% 26% Rent Ingredients Emp. Salaries 28% Utilities Taxes Local Fees State Fees Other Yummy Gelato Stores 6000 Revenue Store Boardwalk Downtown Total Month January February 20,707 24,788 11,221 12,864 31,929 37,652 March 22,758 13,768 36,526 April 26,501 25,641 52,142 Total 94,753 63,495 158,248 5000 4000 3000 Expenses Expenses Month Rent Ingredients Emp. Salaries Utilities Taxes Local Fees State Fees Other Total January February 4000 4000 4200 4200 4500 4600 1200 1300 1500 1400 320 325 120 140 1200 1000 17040 16965 March 4000 5500 4200 1250 1300 315 130 800 17495 April 4000 5500 4350 1100 1250 320 130 750 17400 Total 16000 19400 17650 4850 5450 1280 520 3750 68900 March 19,031 April 34,742 Total 89,348 Profit Profit Year Profit Amount January February 14,889 20,687 2000 1000 0 Rent Ingre Chart Title 6000 5000 Jan Feb Ma Apr 4000 3000 2000 1000 0 Rent Ingredients Emp. Salaries Utilities Taxes Local Fees State Fees Other January February March April Other Illustrated Excel 2016 | Module 4: SAM Project 1b Yummy Gelato Stores WORKING WITH CHARTS GETTING STARTED Open the file IL_EX16_4b_FirstLastName_1.xlsx, available for download from the SAM website. Save the file as IL_EX16_4b_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_4b_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. Lizzy is the manager for Yummy Gelato stores, and she keeps track of the sales revenue and expenses in an Excel workbook. She uses charts to visualize the data, and she has asked you to help her format the charts. Switch to the Jan-Apr Revenue worksheet. The stacked column chart titled January-April Revenue shows the combined sales revenue Yummy Gelato made monthly. In the January-April Revenue chart, format the chart title border with a Blue, Accent 1 (5th column, 1st 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. 3. 4. In the January-April Revenue chart, make the following changes: a. Remove the primary major vertical gridlines. b. Add primary major horizontal gridlines to the chart. In the January-April Revenue chart, update the primary horizontal axis as described below: a. Add a primary horizontal axis title to the chart, and enter the text Months as the title. b. Change the font type of the primary horizontal axis title to Verdana and the size to 12 pt. Switch to the Categorized Expenses worksheet. The 2-D pie chart on this worksheet shows how each of the store's expenses affected the total budget over the year. Illustrated Excel 2016 | Module 4: SAM Project 1b Enter the text Jan-Apr Expenses by Category as the chart title. 5. Apply the Style 3 chart style to the 2-D pie chart. 6. Lizzy can see that ingredients (the orange slice) was the store's biggest expense. In the 2-D pie chart, explode the largest slice of the pie (representing ingredients) by 18%. 7. Switch to the Profit Analysis worksheet. The clustered column chart in this worksheet shows how the company's expenses have changed. Resize and reposition the clustered column chart so that its upper-left corner is within cell H2, and its lower-right corner is within cell S20. 8. In the clustered column chart, enter January-April Expenses 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 April. Change the fill color of that data series to Yellow (4th column, 1st row of the Standard Colors palette). 11. Lizzy would also like to create a chart showing her monthly profits to quickly see which months have been more favorable. Select the range B25:E26 and create a doughnut chart (Hint: under Pie charts). Update the doughnut chart as described below: a. Enter January-April Profit as the chart title. b. Resize and reposition the chart so its upper-left corner is within cell H21 and its lower-right corner is within cell M34. 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: Jan-Apr Revenue Worksheet Illustrated Excel 2016 | Module 4: SAM Project 1b Final Figure 2: Categorized Expenses Worksheet Final Figure 3: Profit Analysis Worksheet