Shelly Cashman Excel 2019 | Modules 4-7: SAM Critical Thinking Capstone Project 1c Victoria Streaming Service CONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS   GETTING STARTED Open the file SC_EX19_CT_CS4-7c_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Shelly Cashman Excel 2019 | Modules 4-7: SAM Critical Thinking Capstone Project 1c

Victoria Streaming Service

CONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS

 

• GETTING STARTED

• Open the file SC_EX19_CT_CS4-7c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT_CS4-7c_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.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_CT_CS4-7c_Media.txt
  • Support_EX19_CT_CS4-7c_Properties.html
  • Support_EX19_CT_CS4-7c_Revenue.xlsx
• With the file SC_EX19_CT_CS4-7c_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. 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 the cell to the right of the cell labeled "Date", use a function to display today's date without the time.
2. Fill the row under 2022 Revenue Projections with the remaining quarters in the year.
3. VSS has applied a goal of increasing revenue to $90,000 in the fourth quarter of 2022. 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 with a linear trend.
4. Bao is confident that revenue will increase by at least 2 percent per quarter in 2022. He wants to calculate these revenue goals in a second projection.
   Project the revenue for 2022 in the second projection based on a 2 percent growth series. Ungroup the worksheets.
5. Bao wants to consolidate the sales data in the U.S., Canada, and U.K. worksheets on the All Locations worksheet.
   Go to the All Locations worksheet, and then consolidate the data. Use a function and 3D references to total the revenue from Action movies in Quarter 1 in the U.S., Canada, and U.K. Use the formula to calculate the remaining revenues from the other types of movies for all four quarters, pasting the formula only.
6. Bao wants to round the total sales values so that they are easier to remember.
   In the appropriate cell, use a function to display the total sales for Quarter 1 rounded up to a whole number. Fill the remaining totals using the formula from Quarter 1.
7. Bao wants to display the total revenue from the previous year for comparison. This data is stored in another workbook.
   In the appropriate cell, insert a formula using an external reference to the total revenue in the All Locations worksheet in the Support_EX19_CT_CS4-7c_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. To illustrate this information, create a 3-D Pie chart that shows how the revenue from each type of media contributed to the total revenue. Move and resize the chart so that it covers the range A15:E30.
9. Format the 3-D pie chart 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 Comedy slice 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, and remove the chart title, if necessary.
10. Bao also wants to visualize the revenue for each type of media per quarter.
    To illustrate this information, create a Stacked Column chart that compares the revenue from each type of media for Quarters 1–4. [Mac Hint: Select Stacked Column chart from Recommended Charts on the Insert tab.] Switch the rows and columns to compare the four quarters of data rather than the six types of media. Move and resize the chart so that it covers the range F15:K37.
11. Bao wants the chart to compare revenue from movies only. Modify the Stacked Column chart to meet his request and make the chart more meaningful.
    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, and 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 data from the file Support_EX19_CT_CS4-7c_Media.txt. Use the first row as headers, and load the data as a table two columns over from and top aligned to the table for 2021 Revenue. Apply the Blue, Table Style Medium 2 table style to the imported table to coordinate with the rest of the worksheet contents. [Mac Hint: Import the data as tab delimited text. Format the text as a table using Blue, Table Style Medium 2.]
13. Bao might want to sort and filter the revenue data as well. Format the data under the cell labeled "2021 Revenue" as a table with headers and using the Blue, Table Style Medium 2 table style.
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 to list these projects in a new range. In the appropriate cell, enter a value to ensure that the filter returns only projects that have been approved. Apply the advanced filter to the Projects table using the appropriate specified criteria. Copy the results to another location, starting in the range A28:F28. Format the copied results as a table with headers, using the Blue, Table Style Medium 2 table style, and filter it 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.
    Determine which projects meet the criteria. Insert a column named Delay? to the right of the Approved? column. For the first entry, use a function and structured references to display TRUE if a project is a drama and it has not been approved. Fill the formula to the rest of the column, if necessary.
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.
    In the Budget column, create a new conditional formatting rule using the 3 Symbols (Circled) icon set. Display the green circled symbol for cells with a number value greater than or equal to 25000, the yellow circled symbol in cells with a number value greater than or equal to 15000, and the red circled symbol in cells with a number value less than 15000.
17. The Project Information section 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.
    In the appropriate cell, use a function that looks up the listed project ID in the Projects table, and then returns the corresponding project name as an exact match.
18. Bao also wants to list the start date using the same method.
    In the appropriate cell, use a function that looks up the listed project ID 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.
    1. In the appropriate cell, use a function to count the number of projects with budget amounts more than $15,000, using the entire Projects table as the database.
    2. In the appropriate cell, use a function to find the average budget of comedy projects, using the entire Projects table as the database.
20. Finally, Bao wants to summarize the number of projects proposed by the project type and calculate their total and average budget amounts.
    1. In the appropriate cell, use a function to count the number of action movie projects in the Project Type column of the Projects table. Use the formula to display the results for the other project types listed in their appropriate cells.
    2. In a similar way, use the appropriate function to display the total budgets of all four project types, and use the appropriate function to average all four project types to fill the remaining values.
21. Bao wants to compare the projects by project type, start date, and budget.
    Provide this comparison by inserting a Treemap chart based on the entire Project Type, Start Date, and Budget columns in the Projects table. Use Projects by Date and Budget as the chart title, and change the font size of the chart title to 12 points. Move and size the chart so that it covers the range I21:O37.
22. Go to the Subtotals worksheet, which lists the same projects as on the Original Content worksheet. Bao wants to display the data by project type, and then list the projects by start date. Sort the data in the table in ascending order first by project type and then by start date, both in ascending order. (Hint: You must complete all actions in this step and the following step correctly to receive full credit.)
23. Bao also wants to calculate subtotals for each project type.
    Convert the table to a range, and then insert subtotals for the budget below each change in the project type. Collapse the outline to display only the subtotals for each project type and the grand total.
24. Go to the Expansion Funding worksheet. VSS is considering whether to expand into selling portable media devices for viewing its streaming content. The company would buy the devices from a manufacturer and then ship them from a distribution center. Bao is seeking funding for the expansion and wants to create a loan analysis to cover the cost of the distribution center.
    First, Bao wants to update and define names in the Loan Payment Calculator section.
    1. Delete the Loan_Calculator defined name.
    2. For the loan amount value, edit the defined name to use Loan_Amount as the name. [Mac hint: You cannot edit a defined name in Excel for Mac, so delete the old one and create a new one.]
    3. For the data on rate to the total cost, create defined names for the value cells based on the labels in the left column.
25. Bao needs to calculate the monthly payment for a loan to purchase the distribution center.
    Use a function, named ranges, and the Loan Payment Calculator data to calculate the payment in the appropriate cell. Divide the rate by 12 to use the monthly interest rate. Use the value for the term in months as well as the loan amount, and display the result as a positive amount.
26. Without using a function, use named ranges, and the Loan Payment Calculator data to find the total interest and the total cost. For the interest, multiply the monthly payment value by the value for the term in months, and then subtract the loan amount. For the cost, add the price to the total interest.
27. Bao wants to compare monthly payments for interest rates that vary from 3.85 to 5.05 percent and for terms of 120, 180, and 240 months. He has already set up the structure for a data table in the Varying Interest Rates and Terms section. Set up and create a two-variable data table to provide the comparison that Bao requests.
    1. Under the cell marked "Rate", create a formula using named ranges that is a reference to the monthly payment, as Bao wants to compare the monthly payments.
    2. Complete the two-variable data table using the term in months and the rate from the Loan Payment Calculator section as the row and column input cells, respectively.
28. Bao has three other options for purchasing the distribution center. In the first scenario, he would pay off the loan in 10 years at an interest rate of 4.45 percent. He wants to determine the monthly payment for the first scenario.
    Use a function to calculate the monthly payment for the 10 Years scenario using the monthly interest rate, the loan period in months, and the loan amount.
29. In the second scenario, Bao could pay back the loan in 15 years and make a monthly payment of $16,000 at an annual interest rate of 4.4 percent. He wants to know the loan amount he should request with those conditions.
    Use a function to calculate the loan amount for the 15 Years scenario using the monthly interest rate, the loan period in months, and the monthly payment.
30. In the third scenario, Bao could pay back the loan for 5 years with a monthly payment of $20,000 at an annual interest rate of 4.55 percent and then renegotiate better terms. He wants to know the amount remaining on the loan after 5 years, or the future value of the loan.
    Use a function to calculate the future value for the 5 Years scenario using the monthly interest rate, the loan period in months, the monthly payment, and the loan amount.
31. Bao is also considering other properties to purchase. The worksheet should list information about these properties, which is contained in a webpage.
    In the Expansion Funding worksheet, get data from the webpage Support_EX19_CT_CS4-7c_Properties.html. Import only the Candidate Properties data (not the title), and load it beginning in cell F26 of the existing worksheet as a table. Format the table using the Blue, Table Style Medium 2. [Mac Hint: Open the Support_EX19_CT_CS4-7c_Properties.html file in Excel. Copy the table data, and then paste it beginning in cell F26 of the Expansion Funding worksheet. Format the data as a table using the Blue, Table Style Medium 2 table style.]
32. Bao wants to list the candidates' information in the prepared area using his preferred format.
    1. Under the cell labelled Type, use a function to capitalize the first letter in each word in the table you just created. Copy the formula down to list the remaining property types.
    2. Use a function to consolidate the first candidate's first and last name information with one space in between. Copy the formula down to list the remaining candidates.
    3. In the State column, use a function to display the last two characters of the Location for each of the candidates. In the Months on Mkt column, use a function to display the first two characters in the On Market column for each of the candidates.
    4. Hide the rows containing the imported table so the worksheet does not display duplicated data.

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.