(Get Answer) - Shelly Cashman Excel 2019 Modules 4-7: SAM Capstone Project 1a 1....| Transtutors (2024)

• 

  27+ Users Viewed

• 

  10+ Downloaded Solutions

• 

  New Jersey, US Mostly Asked From

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

1. Bao Phan is a financial analyst for Victoria Streaming Service (VSS), a website that streams movies and IV shows to subscribers in the United States, Canada, and the United Kingdom. He ls 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 TODAV function to display today's date. 2. Use the text In cellHSto fill the range15:KSwith the names of the remaining quarters in the year. 3. VSS has applied a goal of increasing revenue to$90,000in the fourth quarter of next year. For Quarter 1, Bao estimates$84,309in revenue, which is the average revenue per quarter from the current year. Project the revenue in Quarters 2 and 3 by flling the series for the first projection (rangeH7: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 Ha:kg) based on a gronth series using1.02as the step value. 5. Bab wants to consolidate the cales data in the U,S., Canada, andU, K workaheeta ea the AN tocations warksheet. Ungroup the worksheets, go to the Ari Locations worksheet, and then concolldate the data as followst 3. In cell tis, enter a formula uing the SUM function and a 30 reference to total the : b. Copy the formula in cell E6 to calculate the revenue from the other types of movies for all four quarters (range87:611and C6.E11), pasting the formula ori.l. 6. Bao wants to round the total sales values so that they are easier te remember. a. In cell B12, add the Roundup function to display the total sales for quarter 1 rounded up to 0 decimal places. b. Tili the rangeC12:F12with the formula in cell B12. 7. In celi F14, Bao wants to display the total revenue from the previotib year. This data is stored in another workbook Insert the total as follows: a. Open the file Support__-X19_CS4-7a_Revenue.xds×. b. In cellF14of Bao's workbook, insert a formala tising an external reference to the total revemue (celf F12) in theAILocations worksheet in the Support EX19_CS4-7a_Rervenue, Xes. worhbock. ?. Bao wants to visualize how the revenue for each type of medi contributed to the total revenve for the four quarters. Create a chart as follows to illustrate this intormation: a. Create a 3-0 Pre chart that shows how the revenue from each trpe of media (range A6:A11) contributed to the total revemue (range F6:F11). b. Move and resize the chart sa that the upper-left corner is in cell A1s and the tower-right corner is in cell E30. 9. Format the 3-D Fre chart as follows ta make it easier to interpret: a. Add data tabels to the chart on the Outside End of each sion. ti. Disolary only the Category Name and Percentage amounts in the data labels. c. Change the number format of the data labels to Percentage with 1 dedimal place. d. Explode the largest sllte (Comedy) by 10 percent. e. Change the chart colors to Monochromatic Palette 1 to coorsinate with the data source range: t. Apply style 8 to the chart to simplify the chart desiga. g. If present, remove the chart titie which is not necessary for this chart. 10. Bao atso wants to visualize the revenue for each type of media per quarter. Create a chart as follows to lillustrate 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 1-4 (range AS:E11). [MAC HINT: Select Stacked Column chart from Recominended Charte 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 fowerright corner is in cell Ka7. 11. Bao decides he wants the chart to compare revenue from movies only. Modlly the Stacked Column chart as follows to meet his request and make the chart more meaninglul: 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. Bab 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_CSA-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 dose and load to a location in the worksheet. (MAC Hint: Use Text Import Wizard to import data as tab dellmited text.] d. View the imported data as a table and insert the data in cell H5 of the extsting worksheet. [MAC Hint: Import data as text and update the table name from Table_2" to "Support_Exio_CS4_7a_Media*.] e. Apply Blue, Table Style Medium 2 to the imported table to coordinate with the rest of the worksheet contents. IMAC 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 cellF26, type Yes as the value to filter on in the criteria range. b. Create an advanced filter using the Projects table (rangeA1: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 rangeA28:F28. e. Insert a table using the rangeA28:F36as 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 weil. 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,000or more, those with budgets of$15,000or 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 dircled 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:313 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 33 , begin to enter a formula using the VLOOKUP function. b. Use the Project ID (cell 32) as the lookup value. c. Use the Projects table (rangeA2: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. 19. Bao also wants to calculate the number of projects that have a budget of more than$15,000and determine the average budget amount for comedy projects. Create formulas that provide this information as follows: a. In cell 38, 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[#AII]) as the database, "Budget" as the field, and the range I6:I7 as the criteria. b. In cell 313, create a formula using the DAVERAGE function to average the budget amounts for Comedy projects in the Projects table, using the range I11:112 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-316, enter a formula using the COUNTIF function that counts the number of Action movie projects, using Projects[Project Type] as the range and cell 116 as the criteria. b. Fill the range 317:119 with the formula in cell 316 . c. In-cellK16, enter a formula using the SUMIF function that totals the budget for Action movie projects, using Projects[Project Type] as the range, cell I16 as the criteria, and Projects[Budget] as the sum_range. d. Fill the rangeK17:K19with the formula in cellK16. e. In cell L16, enter a formula using the AVERAGEIF function that averages the budget amounts for Action movie projects. f. Fill the range L17:L19 with the formula in cell L16. 21. Bao wants to compare the projects by project type, start date, and budget. Insert a chart as follows to provide this comparison: a. Insert a Treemap chart based on the range C1:E23. b. Use Projects by Date and Budget as the chart title. c. Change the font size of the chart title to 12 point. d. Move the chart so that its upper-left comer is in cell 121 and its lower-right comer is in cell 037. 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. (Hint: You must complete all actions in this step and the following step correctly to receive full credit.) Sort the data in the table in ascending order first by project type and then by start date, both in ascending order. 23. Bao also wants to calculate subtotals for each funding type. a. Convert the table to a range. b. Insert a subtotal at each change in the Project Type value. c. Use the Sum function to calculate the subtotals. d. Add subtotals to the Budget values only. e. Indude a summary below the data. f. 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 their 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 worksheet as follows: a. Delete the Loan_Calculator defined name. b. For cell B8, edit the defined name to use Loan_Amount as the name. c. In the range D4:D8, create defined names based on the values in the range C4:C8. 25. Bao needs to calculate the monthly payment for a loan to purchase the distribution center. Calculate the payment as follows: a. In cell D6, start to enter a formula using the PMT function. b. Divide the Rate (cell D4) by 12 to use the monthly interest rate. c. Use the Term_in_Months (cell D5) to specify the number of periods. d. Use the Loan_Amount (cell B8) to indude the present value. e. Display the result as a positive amount. 26. Calculate the total interest and cost as follows: a. In cell D7, enter a formula without using a function that multiples the Monthly_Payment (cell D6) by the Term_in_Months (cell D5), and then subtracts the Loan_Amount (cell B8) from the result to determine the total interest. b. In cell D8, enter a formula without using a function that adds the Price (cell B6) to the Total_Interest (cell D7) to determine the total cost. 27. Bao wants to compare monthly payments for interest rates that vary from3.85to5.05percent and for terms of 120,180 , and 240 months. He has already set up the structure for a data table in the range A12:D25. Create a two-variable data table as follows to provide the comparison that Bao requests: a. In cell A12, enter a formula without 45 ing a function that references the Monthly Payment amount (cell D6) because Bao wants to compare the monthly payments. b. Based on the range A12:025, create a two-variable data table that uses the term in months (cell D5) as the row input cell and the rate (cell D4) as the column input cell. 28. Bac 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 of4.45percent. He wants to determine the monthly payment for the first scenario. In cell G10, insert a formula using the PMT function using the monthly interest rate (cell) G6), the loan period in months (cell G8), and the loan amount (cell G4) to calculate the monthly payment for the 10 Years scenario. 29. In the second scenario, Bao could pay back the loan in 15 years and make a monthly payment of$16,000at an annual interest rate of4.4percent. He wants to know the loan amount he should request with those conditions. In cellH4, insert a formula using thePVfunction and the monthly interest rate (cell) H6), the loan period in months (cell H8), and the monthly payment (cell H10) to calculate the loan amount for the 15 Years scenario. 30. In the third scenario, Bao could pay back the loan for 5 years with a monthly payment of$20,000at an annual interest rate of4.55percent 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. In cell 111, insert a formula using the FV function and the rate (cell I6), the number of periods (cell I8), and the monthly payment (cell I10) to calculate the future value of the loan for the 5 Years scenario. 31. Bao is also considering other properties to purchase. The worksheet should list information about these properties, which is contained in a webpage. Import data from the webpage as follows: a. In the Expansion Funding worksheet, get data from the webpage Support_EX19_CS4-7a_Properties.htmL. (Hint: Use Windows Explorer to Copy the path to the webpage, and then type ISupport EX19_CS47a. Properties, html at the end of the path.) [MAC Hint: Open the Support_EX19_CS4-7a_Properties.html file in Excel.] b. Import only the Candidate Properties data. [MAC Hint: Copy the range A1:E] from the Support_EX19_CS4-7a_Properties.html file.] c. Load the webpage data as a table to cellF26in the existing worksheet. [MAC Hint: Paste the range copied above into cell F26 of the Expansion Funding worksheet.] d. Format the imported data in the range F26 using Blue, Table Style Medium 2 . [MAC Hint: Format as Table using Blue, Table Style Medium 2.] 32. Bao wants to list the property information in the rangeF15:119using his preferred format. Incornorate then imported data in the targen fis:t19 as followit. a. In coll Fas, entet in formula uting the proper function to capitalizn the fint inter is ench woed in the Type teit in cell?=27. 6. Iili the range I16.F19 with the locmula in cel2+15to list the remaining property tront: name showi in coll 427 fobowed by a spach(-7), and then the fast name showat in cell 127 d. Fin the range6162Gs9with the formula in celt 615 to tist the full names at the temainlifo contadt e. In cell 115 , enter a formila ining the R1Gut finction to insert the tast 7 tharaoters on the right of ceit 627 . Copr the formuda in centit* to the range H10:H10. 1. In cenl 115, enten a formuda wsing the LLF function to intert the fire 2 . characters on the left of cell 177. Copy the formula in cell f1s to the range ftost19. 0. Hide rows 26 to 32 sa the worksheet does not display dupticated data, fou workbok should look like the final floures on the fotiowing poget. Save your changes. che the werkbook, ahd then exit. Fxce. Follow the directions on the5.1Mwebsite to submit your ompleted project. Final Figure 1: U.S. Worksheet Final Figure 2: Canada Worksheet Final Figure 4: All Locations Worksheef Final Figure 6: Subtotals Worksheet - Business Expansion Lasn Calculater