Excel Modules 4-7: SAM Capstone Project

**Check The Instruction File and other files**  

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

o Support_EX19_CS4-7a_Media.txt

o Support_EX19_CS4-7a_Properties.html

o Support_EX19_CS4-7a_Revenue.xlsx

With the file SC_EX19_CS4-7a_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. Bao Phan is a financial analyst for Victoria Streaming Service (VSS), a website that streams movies and TV shows to subscribers in the United States, Canada, and the United Kingdom. He is tracking sales for the year and asks for your help in projecting future sales and visualizing the sales data.
 

The U.S., Canada, and U.K. worksheets have the same structure and contain similar data. Group the U.S., Canada, and U.K. worksheets to make changes to the three worksheets at the same time. The first change is to display today’s date.
 

In cell H1 of the U.S. worksheet, enter a formula using the TODAY function to display today’s date.

2. Use the text in cell H5 to fill the range I5:K5 with the names of the remaining quarters in the year. 

3. VSS has applied a goal of increasing revenue to $90,000 in the fourth quarter of next year. For Quarter 1, Bao estimates $84,309 in revenue, which is the average revenue per quarter from the current year.
 

Project the revenue in Quarters 2 and 3 by filling the series for the first projection (range H7:K7) with a linear trend. 

4. Bao is confident that revenue will increase by at least 2 percent per quarter next year. He wants to calculate these revenue goals in a second projection.
 

Project next year’s revenue in the second projection (range H9:K9) based on a growth series using 1.02 as the step value. 

5. Bao wants to consolidate the sales data in the U.S., Canada, and U.K. worksheets on the All Locations worksheet.
 

Ungroup the worksheets, go to the All Locations worksheet, and then consolidate the data as follows:

a. In cell B6, enter a formula using the SUM function and a 3D reference to total the revenue from Action movies in Quarter 1 (cell B6) in the U.S., Canada, and U.K. 

b. Copy the formula in cell B6 to calculate the revenue from the other types of movies for all four quarters (range B7:B11 and C6:E11), pasting the formula only.

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

a. In cell B12, add the ROUNDUP function to display the total sales for Quarter 1 rounded up to 0 decimal places.

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

7. In cell F14, Bao wants to display the total revenue from the previous year. This data is stored in another workbook. Insert the total as follows:

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

b. In cell F14 of Bao’s workbook, insert a formula using an external reference to the total revenue (cell F12) in the All Locations worksheet in the Support_EX19_CS4-7a_Revenue.xlsx workbook.

8. Bao wants to visualize how the revenue for each type of media contributed to the total revenue for the four quarters.
 

Create a chart as follows to illustrate this information:

a. Create a 3-D Pie chart that shows how the revenue from each type of media (range A6:A11) contributed to the total revenue (range F6:F11).

b. Move and resize the chart so that the upper-left corner is in cell A15 and the lower-right corner is in cell E30.

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

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

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

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

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

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

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

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

10. Bao also wants to visualize the revenue for each type of media per quarter.
 

Create a chart as follows to illustrate this information:

a. Use the Quick Analysis tool to create a Stacked Column chart that compares the revenue from each type of media for Quarters 14 (range A5:E11). [MAC HINT: Select Stacked Column chart from Recommended Charts under the Insert tab.]

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

c. Move and resize the chart so that the upper-left corner is in cell F15 and the lower-right corner is in cell K37.

11. Bao decides he wants the chart to compare revenue from movies only. Modify the Stacked Column chart as follows to meet his request and make the chart more meaningful:

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

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

c. Use Movie Revenue as the chart title.

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

12. Bao has a text file that describes the types of media the company provides. Import the text file as follows:

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

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

c. In the Power Query Editor window, choose to close and load to a location in the worksheet. [MAC Hint: Use Text Import Wizard to import data as tab delimited text.]

d. View the imported data as a table and insert the data in cell H5 of the existing worksheet. [MAC Hint: Import data as text and update the table name from “Table_2” to “Support_EX19_CS4_7a_Media”.]

e. Apply Blue, Table Style Medium 2 to the imported table to coordinate with the rest of the worksheet contents. [MAC Hint: Format as Table using Blue, Table Style Medium 2.]

13. Bao decides that he might want to sort and filter the revenue data. Format the range A5:F12 as a table with headers.

14. Go to the Original Content worksheet, which lists movies and TV shows that VSS is developing itself and indicates whether each project is approved for production. Bao wants to list the approved projects in a separate part of the worksheet.
 

Use an advanced filter as follows to list these projects in a new range:

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

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

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

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

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

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

15. VSS wants to delay the development of dramas that have not been approved because dramas have not been selling well. Bao asks you to identify these projects on the Original Content worksheet.
 

Add a column to the Projects table and determine which projects meet the criteria as follows:

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

b. Type Delay? as the column heading.

c. In cell G2, enter a formula using the AND function that includes structured references to display TRUE if a project has a [Project Type] of “Drama” and an [Approved?] value of “No”. Fill the range G3:G23 with the formula in cell G2 if Excel does not do so automatically.

16. Bao asks you to identify the projects with budgets of $25,000 or more, those with budgets of $15,000 or more, and those with budgets less than $15,000. 

a. In the Budget column (range E2:E23), create a new Icon Set Conditional Formatting rule using the 3 Symbols (Circled) indicators.

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

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

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

17. The range I1:J13 lists project details, including the ID code that VSS producers use to refer to the projects. Bao wants to find a simple way to look up a project name based on its ID.
 

Create a formula that provides this information as follows:

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

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

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

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

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

18. Bao also wants to list the start date of the project identified in cell J2.
 

In cell J4, enter a formula using the VLOOKUP function that looks up the value in cell J2 in the Projects table, and then returns the corresponding start date as an exact match.

19. Bao also wants to calculate the number of projects that have a budget of more than $15,000 and determine the average budget amount for comedy projects.
 

Create formulas that provide this information as follows:

a. In cell J8, create a formula using the DCOUNT function to count the number of projects with budget amounts more than $15,000, using the Projects table (Projects[#All]) as the database, “Budget” as the field, and the range I6:I7 as the criteria.

b. In cell J13, create a formula using the DAVERAGE function to average the budget amounts for Comedy projects in the Projects table, using the range I11:I12 as the criteria.

20. Finally, Bao wants to summarize the number of projects proposed by the project type and calculate their total and average budget amounts.
 

Calculate this information for Bao as follows:

a. In cell J16, enter a formula using the COUNTIF function that counts the number of Action movie projects, using Projects[Project Type] as the range and cell I16 as the criteria. 

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

c. In cell K16, enter a formula using the SUMIF function that totals the budget for Action…