question archive Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Victoria Streaming Service CONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS GETTING STARTED Open the file SC_EX19_CS4-7a_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought9

Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a

CONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS

Open the file **SC_EX19_CS4-7a_ FirstLastName_1.xlsx**, available for download from the SAM website.

Save the file as **SC_EX19_CS4-7a_ FirstLastName_2.xlsx** by changing the “1” to a “2”.

Support_EX19_CS4-7a_Properties.html

Support_EX19_CS4-7a_Revenue.xlsx

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

Bao wants to round the total sales values so that they are easier to remember.

Fill the range C12:F12 with the formula in cell B12.

Open the file **Support_EX19_CS4-7a_Revenue.xlsx**.

Format the 3-D Pie chart as follows to make it easier to interpret:

Add data labels to the chart on the **Outside End** of each slice.

Display only the **Category Name** and Percentage amounts in the data labels.

Change the number format of the data labels to **Percentage** with **1** decimal place.

Explode the largest slice (Comedy) by **10** percent.

Change the chart colors to **Monochromatic Palette 1** to coordinate with the data source range.

Apply **Style 8** to the chart to simplify the chart design.

If present, remove the chart title which is not necessary for this chart.

Switch the rows and columns to compare the four quarters of data rather than the six types of media.

Remove the **TV Shows** data series from the chart.

Add a **Data Table** with legend keys to the chart.

Use **Movie Revenue** as the chart title.

Remove the legend, which repeats information in the data table.

Get data from the Text/CSV file **Support_EX19_CS4-7a_Media.txt**.

Edit the text file before loading it to use the first row as headers.

In cell F26, type **Yes** as the value to filter on in the criteria range.

Create an advanced filter using the Projects table (range **A1:F23**) as the List range.

Use the range **A25:F26 **as the Criteria range.

Copy the results to another location, starting in the range **A28:F28**.

Insert a table using the range A28:F36 as the data and specifying that the table has a header row.

Filter the new table to display only data for TV shows.

Add a column to the right of the Approved? column.

Type **Delay?** as the column heading.

Display the green circled symbol in cells with a Number type value greater than or equal to **25000**.

Display the yellow circled symbol in cells with a Number type value greater than or equal to **15000**.

Display the red circled symbol in cells with a Number type value less than **15000**.

In cell J3, begin to enter a formula using the **VLOOKUP** function.

Use the Project ID (cell **J2**) as the lookup value.

Use the Projects table (range **A2:G23**) as the table_array.

Use the Project Name column (column **2**) as the col_index_num.

Specify an exact match (**FALSE**) for the range_lookup.

Fill the range J17:J19 with the formula in cell J16.

Fill the range K17:K19 with the formula in cell K16.

Fill the range L17:L19 with the formula in cell L16.

Insert a **Treemap** chart based on the range C1:E23.

Use **Projects by Date and Budget** as the chart title.

Change the font size of the chart title to 12 point.

Bao also wants to calculate subtotals for each funding type.

Insert a subtotal at each change in the **Project Type** value.

Use the **Sum** function to calculate the subtotals.

Add subtotals to the **Budget** values only.

Include a summary below the data.

Collapse the outline to display only the subtotals for each project type and the grand total.

Delete the Loan_Calculator defined name.

For cell B8, edit the defined name to use **Loan_Amount** as the name.

In the range D4:D8, create defined names based on the values in the range C4:C8.

In cell D6, start to enter a formula using the **PMT** function.

Divide the **Rate** (cell D4) by **12** to use the monthly interest rate.

Use the **Term_in_Months** (cell D5) to specify the number of periods.

Use the **Loan_Amount** (cell B8) to include the present value.

Display the result as a positive amount.

Calculate the total interest and cost as follows:

Fill the range F16:F19 with the formula in cell F15 to list the remaining property types.

Hide rows 26 to 32 so the worksheet does not display duplicated data.

Final Figure 1: U.S. Worksheet

Final Figure 2: Canada Worksheet

Final Figure 3: U.K. Worksheet

Final Figure 4: All Locations Worksheet

Final Figure 5: Original Content Worksheet

Final Figure 6: Subtotals Worksheet

Purchased 9 times