Analyze And Chart Financial Data

If you need help for a similar finance assignment on excel about Analyze And Chart Financial Data; New Perspectives Excel 2019 | Module 4: SAM Project 1b, register & post your assignment to get instant help from our Finance and Excel experts!

Assignment Type: Finance Assignment on Excel

Assignment Category: Excel Assignment Help

Platform: Microsoft Excel – Microsoft Word

University: Unknown

Template: Unknown

Assignment Size: 2 Hours

Attached File for New Perspectives Excel 2019 | Module 4: SAM Project 1b

Analyze and chart financial data

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

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.

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

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. In the range E5:E9, add Conditional Formatting to compare the Quarter 4 expenses using Gradient Fill Blue Data Bars.

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.

Analyze And Chart Financial Data – Steps 1

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

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

Enter Quarter 1 Expenses as the chart title.

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

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

Kayla also wants to compare how each type of expense relates to the total expenses in Quarter 4. Create a chart as follows to compare the expenses:

Create a 2-D Pie chart based on data in the nonadjacent ranges A5:A9 and E5:E9.

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 O39, then left-align the chart with the pie chart above it in the range G4:O22.

Customize the new pie chart in the range G23:O39 as follows to clarify its purpose and match the format of the other pie chart:

Enter Quarter 4 Expenses as the chart title.

Apply Style 11 to the chart.

Kayla also wants to compare the five types of expenses in each quarter. Create a chart as follows to provide this information:

Create a Stacked Bar chart based on the data in the range A4:E9.

Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower-right corner is located within cell F28.

Cengage Steps 2 – New Perspectives Excel 2019 | Module 4: SAM Project 1b

Customize the stacked bar chart in the range A11:F28 as follows to make it easier to interpret:

Enter Expenses per Quarter as the chart title.

Change the Maximum value to $1,400,000 on the horizontal axis.

Apply a shape fill of Indigo, Text 2, Lighter 80% (4th column, 2nd row in the Theme Colors palette) to the chart to contrast with the other charts in the worksheet.

Kayla wants the Projected Expenses 2022 – 2025 combo chart to appear on a different worksheet. Move the chart as follows:

Move the combo chart in the range A35:F62 to the Projected Expenses worksheet.

Resize and reposition the chart so that the upper-left corner is located within cell A12 and the lower-right corner is located within cell J35.

Customize the combo chart as follows to clarify its data:

Apply the Monochromatic Palette 3 color scheme to the chart.

Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the format pane to add the axis.]

New Perspectives Excel 2019 | Module 4: SAM Project 1b – Cengage Steps 3

Add Vertical Axis Titles to the chart, using Expenses per Category as the Left Vertical Axis Title and Total Expenses as the Right Vertical Axis Title. Finally, delete the horizontal axis title placeholder because that axis clearly represents years.

Kayla wants to compare three options for Caretaker Mobile Apps to borrow $725,000 to fund the development of their new product. Switch to the Funding worksheet. She has already entered formulas in the range B11:D12 to calculate the quarterly and annual payments for each option.

In the range E11:E12, add Column sparklines using the data in the range B11:D12. Ungroup the sparklines, and then change the vertical axis maximum value to -16000 for cell E11 and to -60000 for cell E12.

Kayla wants a clustered column chart comparing the costs of the funding options.

Create a Clustered Column chart based on the values in the range A11:D12.

Resize and reposition the chart so that the upper-left corner is located within cell A13 and the lower-right corner is located within cell D30.

Customize the chart in the range A13:D30 as follows:

Enter Funding Options Comparison as the chart title.

Add a Data Table With Legend Keys to the chart.

Apply a 1½ point border to the chart area, and then change the border color to Blue, Accent 4 (8th column, 1st row of the Theme Colors palette).

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: Current Expenses Worksheet, Final Figure 2: Projected Expenses Worksheet, Final Figure 3: Funding Worksheet

Legal Note for Analyze And Chart Financial Data

The assignment file and details given on this page is just an example file for New Perspectives Excel 2019 | Module 4: SAM Project 1b. If any individual/institution believe publication of this assignment is offensive; please contact with us using Contact Form and we will get back to you in shortest possible notice.

Pure Assignment has established in Turkey. Pure Assignment is only charged of Turkish law and all matters discussed in Terms of Use and the other subtitles must be considered according to Turkish Law. According to related law (law number 5651) published in official paper;

MADDE 5 (Article number: 5)–

(1) Yer sağlayıcı, yer sağladığı içeriği kontrol etmek veya hukuka aykırı bir faaliyetin söz konusu olup olmadığını araştırmakla yükümlü değildir.

(1) The site provider is not obliged to check the content it provides or to investigate whether there is an illegal activity. (Translated by Google Translate)

(2) Yer sağlayıcı, yer sağladığı hukuka aykırı içerikten, ceza sorumluluğu ile ilgili hükümler saklı kalmak kaydıyla, bu Kanunun 8 inci ve 9 uncu maddelerine göre haberdar edilmesi halinde ve teknik olarak imkân bulunduğu ölçüde hukuka aykırı içeriği yayından kaldırmakla yükümlüdür.

(2) The site provider is obliged to remove the illegal content from the publication; if it is informed in accordance with the 8th and 9th articles of this Law. And to the extent technically possible, provided that the provisions regarding the criminal liability are reserved for the illegal content it provides. (Translated by Google Translate)

Pure assignment offers the Site and Site Services for clients to be able to share any freelance job to let them find the help from the right expert they need with their business or assignments; papers and projects; either for business purpose or educational purpose. Pure assignment aims to get clients and freelancers together to be able to help them each other. In this case, Pure assignment works as a service provider as mentioned in law (law number 5651).