Homework answers / question archive / New Perspectives Excel 2019 Module 4:SAM Project lb sani Stuarts Bank ANALYZE AND CHART FINANCIAL DATA

New Perspectives Excel 2019 Module 4:SAM Project lb sani Stuarts Bank ANALYZE AND CHART FINANCIAL DATA

MS Excel

Share With

---

New Perspectives Excel 2019 Module 4:SAM Project lb

sani Stuarts Bank ANALYZE AND CHART FINANCIAL DATA . . .

GETTING STARTED

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

Save the file as NP_EX19_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 NP_EX19_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. Kayla Rowe is an investment banker at Stuarts Bank in New York City. She is working with a software company called Caretaker Mobile Apps, which is developing an app that detects and manages smart devices in a customer's home. To help Caretaker Mobile Apps secure funding for its new product, Kayla is using an Excel workbook to analyze their expenses and funding options and to create charts that illustrate the analysis. Switch to the Current Expenses worksheet. Iy the range ES: E9, add Conditional Formatting to compare the Quarter 4 expenses using Gradient Fill Blue Data Bars.

2. In the range F5:F10, add Line sparklines based on the data in the range B5:E10 to compare each category of expense and total expenses from Quarter 1 to Quarter 4.

3 Apply the Red, Accent 6 (10th column, ist row in the Theme Colors palette) sparkline color to the sparklines to contrast with the data in the worksheet.

4. Kayla created a ple chart comparing how each type of expense relates to the total expenses in Quarter 1. Modify the pie chart in the range G4:022 as follows make it more meaningful:

a. Enter Quarter 1 Expenses as the chart title.

b. Change the data labels to include the Category Name, and position the labels in the Inside End location to clarify what each piece represents.

c. Remove the Legend from the chart because it now repeats information in the data labels

5. Kayla also wants to compare how each type of expense relates to the total expenses in Quarter

a.  Create a chart as follows to compare the expenses:

b. Create a 2-D Pie chart based on data in the nonadjacent ranges AS:A9 and ES:E9. b. Resize and reposition the chart so that the upper left corner is located within cell G23 and the lower right corner is located within cell 039, then left-align the chart with the ple chart above it in the range G4:022.