Illustrated Excel 2019 Module 4:End of Module Project 2

Lawson Financials
INSERT AND MODIFY CHARTS

GETTING STARTED

• Open the file IL_EX19_EOM4-2_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM4-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
• 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_EX19_EOM4-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
• If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. As a consultant for Lawson Financials in Alpharetta, Georgia, Keisha Nickels is evaluating the sales and expenses for a local financial planning office. She has created an Excel workbook to maintain the sales and expense data, and asks for your help in formatting and creating charts to better visualize the information.

Go to the Sales and Expenses worksheet. Keisha wants to show the trend of each expense type for the four quarters of 2021. In cell F5, insert a Line sparkline for the range B5:E5. Copy the sparkline in cell F5 into the range F6:F10.

2. ormat the Line sparklines in the range F5:F10 by showing their high and low points. Change the sparkline style to Rose, Sparkline Style Colorful #1, and then change the color of the High Point marker to Dark Green, Text 2 (4th column, 1st row in the Theme Colors palette).
3. The Expenses 2021 clustered column chart shows five categories of expenses in each quarter of 2021. Move the chart to a new sheet and use Expense Chart as the sheet name.
4. Change the chart type to a Stacked Column chart, and then switch the row and column data in the Expenses 2021 chart so the chart shows Quarters 1–4 instead of expense categories on the horizontal axis to make the data easier to interpret.
5. Change the format of the numbers on the vertical axis so they use the Accounting number format with a dollar sign ($) and 0 decimal points. (Hint: Use the Format Axis task pane.) Add a primary vertical axis title and use Expense Amount as the title text.
6. Add a Data Table With Legend Keys to the chart, and then remove the Legend.
7. Change the font size of all the chart text to 12 point, and then change the font size of the chart title to 20 point.
8. Go to the Sales and Expenses worksheet. The line with markers chart in the range A19:G33 shows the trend of net profit or loss over time. Add a title above the chart and use Net Profit/Loss 2021 as the title text.
9. Change the maximum bounds of the vertical axis to 5000 to improve the scale of the chart and reflect the data more precisely.

10. Format the line chart to make it easier to understand and to improve its appearance: Add data labels above each marker to show the net profit amount. Change the Shape Outline color of the line to Dark Teal, Accent 4 (6th column, 1st row in the Theme Colors palette). Add a linear trendline to the chart.
11. The 2-D pie chart in the range H19:O33 compares total sales from three sources. Keisha wants to display the percentages on the wedges of the chart. Add data labels to the chart using the Best Fit position option, and then change the values to only display percentages. Change the font color of the largest percentage to White, Background 1 (1st column, 1st row in the Theme Colors palette).
12. Improve the appearance of the pie chart by changing the shape style of the legend to Colored Outline – Dark Teal, Accent 4. Change the position of the chart title to Centered Overlay.
13. Add a border to the line chart and the pie chart using the White, Background 1, Darker 50% (1st column, 6th row in the Theme Colors palette) shape outline color.
14. Keisha noticed an error in the sales data. Change the value in cell E14 to 2000 to increase the speaking fees for Quarter 4.

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