New Pespectives Excel$2019|$ Module $4$: End of Module Project $1$

FLO Biotech

ANALYZE AND CHART FINANCIAL DATA

GETIING STARTED

Open the file NP$\_$EX$19\_$EOM$4-1\_$FirstLastName$\_1.$xisx, avallable for download from the SAM website.

Save the file as NP$\_$EX$19\_$EOM$4-1\_$FIrstLastName$\_2.$xisx by changing the $"1"$ to a $"2".$

If you do not see the $.$xisx file extension in the Save As dlalog box, do not type it$.$ The program will add the file extension for you automatically.

With the file NP$\_$EX$19\_$EOM$4-1\_$FirstLastName$\_2.$xIsx still open, ensure that your first and last name is displayed in cell $B6$ of the Documentation sheet.

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

PROJECT STEPS

Elden Reyes is an intern with FLO Blotech. Elden is preparing a sales summary, as well as a financial project for a prospective equipment purchase. He wants to use Excel to create charts to illustrate some of his data and to apply a function to calculate payments.

Switch to the Sales by Product worksheet. In the range G$7$:G$18,$ add column sparklines based on the data in the range B$7$:F$18,$ and then apply the Green, Accent $6,$ Darker $25\%(10$th column, $5$ th row of the Theme Colors palette$)$ sparkline color.

Create a Ple chart based on the range $F7$:$F17.$ Specify the range $A7$:$A17$ as the horizontal axis labels. Resize and reposition the chart so that the upper$-$left corner is located within cell $H6$ and the lower$-$right corner is located within cell Q$30.$

Enter $2021$ Product Sales as Percentage of Total as the chart title and apply chart style $6.$

Create a $2-$D Line chart based on the range B$18$:F$18.$ Modify the chart by changing the horizontal axis labels to use the range $B6$:$F6.$ Enter Total Revenue by Year $($Mililons$)$ as the chart title, and resize and reposition the chart so that the upper$-$left corner is located within cell A$20$ and the lower$-$right corner is located within cell G$37.$

Apply chart style $13$ to the line chart you just created. Format the vertical axis to use a maximum value of $8000,$ change Display units to Thousands but don't show the units on the chart, and show $0$ decimal places in the axis labels.

Create a stacked column chart based on the range A$6$:F$17.$ Modiry the chart by switching the row and column so the horizontal axis shows years and the stacked components of each bar are products. Enter Product Contribution to Total Revenue $2017-2021($millions$)$ as the chart titie, and resize and reposition the chart so that the upper$-$lert corner is located within cell A$38$ and the lower$-$right corner is located within cell G$63.$

Apply chart styl$9$ to the stacked column chart you just created. Format the vertical axis to use a maximum value of $8000,$ change Display units to Thousands but don't show the units on the chart, and show o decimal places in the axis labels.