New Perspectives Excel 2016 | Module 4: SAM Project 1b Calorie Courier ANALYZING AND CHARTING FINANCIAL DATA   GETTING STARTED Open the file NP_EX16_4b_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Excel 2016 | Module 4: SAM Project 1b

Calorie Courier

ANALYZING AND CHARTING FINANCIAL DATA

GETTING STARTED

• Open the file NP_EX16_4b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX16_4b_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 NP_EX16_4b_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. Rosella Hunter is the founder of Calorie Courier, a food-delivery service focusing on meals produced with locally-sourced ingredients. Rosella wants to expand her business and wants to pull her company’s financial information together before proposing her idea to her business partners.

Rosella wants a chart representing the distribution of average number of monthly meal orders made by customers in 2024, so she can better understand her customers. Switch to the 2024 Average Monthly Order worksheet. Select the range A4:B230 and create a Histogram chart. (Hint: Use the Name box to select the range.) Modify the chart as described below:

1. Resize and reposition the chart so that the upper-left corner is located within cell D2 and the lower-right corner is located within cell L21.
2. Enter Average Monthly Orders in 2024 as the title of the chart.
3. Modify the bins used in the chart by setting the Bin Width axis option to 5.
4. Calorie Courier offers a number of meal plans to its customers. Rosella wants to graphically represent how those meal plans impacted Calorie Courier’s total annual income between 2019 and 2024.

Switch to the 2019-2024 Annual Income worksheet. Insert Column sparklines into the range H4:H9 based on the data in the range B4:G9, and then apply the Orange, Accent 4 sparkline color.

1. Apply a Solid Fill, Orange Data Bar conditional formatting ruleinto the range I4:I9.
2. Rosella wants a pie chart representing how each meal plan contributed to the Calorie Courier’s total annual income in 2019.

Select the range A4:B8, and then create a 2-D Pie chart. Modify the chart as described below:

1. Resize and reposition the chart so that the upper-left corner is located within cell J1 and the lower-right corner is located within cell Q11.
2. Enter 2019 Annual Income by Meal Plan as the chart title.
3. Apply the Style 12 chart style.
4. In the 2024 Annual Income by Meal Plan 3-D pie chart(located in the range J12:Q25), position the chart legend using the Bottom option.
5. In the 3-D pie chart, add data labelsto the chart using the following options:
   1. The data labels should display using the Outside End position option.
   2. The data labels should only display the Percentage associated with slice of the 3-D pie chart. (Hint: You may need to uncheck the Valuedata label option.)
   3. The data label should use the Percentage number format with 1 decimal place.
   4. Update the Annual Income: 2019 – 2024 line chart in the range A10:H25 by editing the Horizontal (Category) Axis labels to display using the values in the range B3:G3.
   5. In the line chart, modify the Minimum boundsof the vertical axis to be 20000.
   6. Update the line chart by adding Primary Major Horizontal gridlinesand Primary Major Vertical gridlines to the chart area.
   7. Format the line chart as described below:
      1. Apply a solid fill using the Red, Accent 5, Lighter 80% fill color to the chart area.
      2. Apply the Arial font and the Red, Accent 5 font color to the chart title.
      3. Rosella created a stacked column chart representing how the income generated by each membership package contributed to the total annual income. She now needs to modify the data and formatting used in the chart.

Update the Meal Plan Contribution to Annual Income: 2019 – 2024 stacked column chart (in the range A26:H45)by removing the data series labeled “Total”from the chart. (Hint: Do not filter out or hide this data.)

1. In the stacked column chart, format the chart legend as described below:
   1. Apply a Shape Fill using the White, Background 1 fill color.
   2. Apply a Solid Line border with a Black, Text 1 border color.
   3. Apply a Shadow Shape Effect from the Outer section using the Offset Diagonal Bottom Right option. (Hint: Depending on your version of Office, this may be displayed as “Offset: Bottom Right”.)
   4. Rosella wants to create a chart that displays both the annual income generated by each meal plan and Calorie Courier’s total annual income. Because of the large difference between the meal plan income and total income values, Rosella decides that a combo chart is the most appropriate option.

Select the range A3:G9 and create a Custom Combination Combo chart as described below:

1. Represent the following data series as a Clustered Column chart: Two Day, Three Day, Five Day, Seven Day, and On Demand.
2. Represent the Total data series as a Line chart using the Secondary axis, as shown in Figure 1 below.

Figure 1: Combo Chart Setup

1. Move the combo chart to the Income Overview worksheet, and then resize and reposition the chart so that the upper-left corner is located within cell A3 and the lower-right corner is located within cell K24.
2. Enter Meal Plan and Annual Income as the chart title.
3. Add axis titles to the chart, then enter Meal Plan Income as the left vertical axis title, and then enter Annual Income as the right vertical axis title. Finally, delete the horizontal axis title placeholder.
4. Rosella wants to calculate the monthly payments for each loan option that she is considering.

Switch to the Loan Optionsworksheet. In cell B11, create a formula using the PMT function to calculate the monthly payments for loan Option 1. Use the values in cells B7, B9, and B4 for the Rate, Nper, and Pv arguments, respectively, and do not enter any values for the optional arguments. Copy the formula you created in cell B11into the range C11:D11.

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.

Final Figure 1: 2024 Average Monthly Order Worksheet (Range A1:L24)

Final Figure 2: 2019-2024 Annual Income Worksheet

Final Figure 3: Income Overview Worksheet

Final Figure 4: Loan Options Worksheet