**
Fill This Form To Receive Instant Help**

Homework answers / 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

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.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”. o • • 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: 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. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a 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: 6. 7. 8. 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. 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. 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. 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: 9. 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. 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. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a 10. Bao also wants to visualize the revenue for each type of media per quarter. Create a chart as follows to illustrate this information: 11. 12. 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 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 lowerright corner is in cell K37. 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. 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. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Use an advanced filter as follows to list these projects in a new range: 15. 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. 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: 16. 17. 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. 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. 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. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a 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: 20. 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. 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: 21. 22. 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 movie projects, using Projects[Project Type] as the range, cell I16 as the criteria, and Projects[Budget] as the sum_range. d. Fill the range K17:K19 with the formula in cell K16. 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. 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 corner is in cell I21 and its lower-right corner is in cell O37. 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.) Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Sort the data in the table in ascending order first by project type and then by start date, both in ascending order. 23. 24. 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. Include a summary below the data. f. Collapse the outline to display only the subtotals for each project type and the grand total. 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: 25. 26. 27. 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. 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 include the present value. e. Display the result as a positive amount. 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. 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 range A12:D25. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Create a two-variable data table as follows to provide the comparison that Bao requests: 28. a. In cell A12, enter a formula without using a function that references the Monthly_Payment amount (cell D6) because Bao wants to compare the monthly payments. b. Based on the range A12:D25, 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. 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. 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,000 at an annual interest rate of 4.4 percent. He wants to know the loan amount he should request with those conditions. In cell H4, insert a formula using the PV function 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,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. In cell I11, 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: 32. 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 \Support_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:E7 from the Support_EX19_CS4-7a_Properties.html file.] c. Load the webpage data as a table to cell F26 in 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.] Bao wants to list the property information in the range F15:I19 using his preferred format. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Incorporate the imported data in the range F15:I19 as follows: a. In cell F15, enter a formula using the PROPER function to capitalize the first letter in each word in the Type text in cell F27. b. Fill the range F16:F19 with the formula in cell F15 to list the remaining property types. c. In cell G15, enter a formula using the CONCAT function that displays the first name shown in cell H27 followed by a space (" "), and then the last name shown in cell I27. d. Fill the range G16:G19 with the formula in cell G15 to list the full names of the remaining contacts. e. In cell H15, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell G27. Copy the formula in cell H15 to the range H16:H19. f. In cell I15, enter a formula using the LEFT function to insert the first 2 characters on the left of cell J27. Copy the formula in cell I15 to the range I16:I19. g. Hide rows 26 to 32 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. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Final Figure 1: U.S. Worksheet Final Figure 2: Canada Worksheet Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Final Figure 3: U.K. Worksheet Final Figure 4: All Locations Worksheet Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Final Figure 5: Original Content Worksheet Final Figure 6: Subtotals Worksheet Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Final Figure 7: Expansion Funding Worksheet Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Victoria Streaming Service CONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS Van Thang Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Victoria Streaming Service United States in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 12,873 10,027 20,194 9,489 6,406 20,397 $ 79,386.60 Quarter 2 13,869 11,096 21,282 10,036 6,789 21,656 $ 84,727.86 Quarter 3 15,904 11,124 21,130 10,799 5,239 22,244 $ 86,441.55 Quarter 4 15,874 11,006 21,216 9,803 6,011 22,770 $ 86,680.59 Date: Standard: $ 9.95 Premium: $ 12.95 2022 Revenue Projections $ Total 58,521.16 43,253.26 83,821.72 40,127.56 24,445.40 87,067.50 337,236.60 Quarter 1 Increase revenue to 90,000 in Q4 84,309 Increase revenue by 2% per quarter 84,309 90,000 Victoria Streaming Service Canada in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 9,069 6,190 12,006 10,908 7,731 9,707 $ 55,610.71 Quarter 2 9,083 6,836 12,488 11,056 7,792 10,796 $ 58,051.43 Quarter 3 9,120 7,084 12,339 11,144 8,046 10,824 $ 58,558.03 Quarter 4 10,015 6,408 12,411 11,470 8,174 10,866 $ 59,344.79 Date: Standard: $ 10.95 Premium: $ 13.95 2022 Revenue Projections $ Total 37,287.30 26,518.84 49,244.36 44,577.60 31,743.15 42,193.71 231,564.96 Quarter 1 Increase revenue to 60,000 in Q4 57,891 Increase revenue by 2% per quarter 57,891 Victoria Streaming Service United Kingdom in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 9,653 7,190 12,805 10,498 9,713 10,228 $ 60,085.85 Quarter 2 9,705 7,227 12,928 11,611 9,799 10,282 $ 61,552.39 Quarter 3 9,808 8,265 13,007 11,652 9,886 10,335 $ 62,954.31 Quarter 4 9,812 8,303 13,176 10,736 9,073 10,389 $ 61,489.84 Date: Standard: $ 11.95 Premium: $ 14.95 2022 Revenue Projections $ Total 38,978 30,986 51,916 44,497 38,472 41,234 246,082.39 Quarter 1 Increase revenue to 65,000 in Q4 61,521 Increase revenue by 2% per quarter 61,521 Victoria Streaming Service All Locations in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 $ - Quarter 2 $ - Quarter 3 $ - Quarter 4 $ - Total revenue last year: Total $ last year: - ID Project Name AC533 AC210 TV453 TV697 Just Spirits Tension Sunrise Against the Wall Bayridge The Castle Behind Me Mack and Mack Space Plum Karate Night Never in Common All My Friends A Great Getaway Teenage Miracle Mile The Great Grasshopper Downtown Bike Path Because of Winston Mad Music A Slight Case of Spring Paradise Moon Good Gold Bad Blue Shark Myths This Guy Laughed Celeste and Cecilia AC328 CO720 CO789 CO329 DR887 DR543 DR521 CO325 DR512 DR622 TV482 AC736 AC447 TV245 TV598 CO305 CO220 TV552 Project Type Action movie Action movie TV show TV show Action movie Comedy Comedy Comedy Drama Drama Drama Comedy Drama Drama TV show Action movie Action movie TV show TV show Comedy Comedy TV show Start Date 6/22 6/22 7/22 7/22 9/22 9/22 10/22 10/22 11/22 11/22 1/23 1/23 2/23 2/23 3/23 3/23 3/23 3/23 3/23 4/23 4/23 4/23 Budget $45,500 $12,000 $22,500 $28,500 $23,010 $18,555 $25,000 $20,000 $19,700 $12,200 $31,500 $24,000 $12,500 $15,000 $24,500 $10,000 $15,000 $14,000 $22,500 $19,500 $16,000 $50,000 Approved? Yes No No Yes No No No Yes No No Yes No No Yes No No No Yes No Yes No Yes Approved Projects ID Project Name Project Type Approval Date Budget Approved? Project Information Project ID Project Name Start Date Budget > 15000 Number of Projects Project Type Comedy Average Budget Project Type Action movie Comedy Drama TV show Project Information TV245 Number Total Budget Average Budget ID AC533 AC210 TV453 TV697 AC328 CO720 CO789 CO329 DR887 DR543 DR521 CO325 DR512 DR622 TV482 AC736 AC447 TV245 TV598 CO305 CO220 TV552 Project Name Just Spirits Tension Sunrise Against the Wall Bay ridge The Castle Behind Me Mack and Mack Space Plum Karate Night Never in Common All My Friends A Great Getaway Teenage Miracle Mile The Great Grasshopper Downtown Bike Path Because of Winston Mad Music A Slight Case of Spring Paradise Moon Good Gold Bad Blue Shark Myths This Guy Laughed Celeste and Cecilia Project Type Action movie Action movie TV show TV show Action movie Comedy Comedy Comedy Drama Drama Drama Comedy Drama Drama TV show Action movie Action movie TV show TV show Comedy Comedy TV show Start Date 6/2/2022 6/13/2022 7/12/2022 7/28/2022 9/1/2022 9/20/2022 10/3/2022 10/9/2022 11/1/2022 11/18/2022 1/10/2023 1/13/2023 2/22/2023 2/28/2023 3/3/2023 3/13/2023 3/19/2023 3/20/2023 3/23/2023 4/9/2023 4/12/2023 4/18/2023 Budget $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 45,500 12,000 22,500 28,500 23,010 18,555 25,000 20,000 19,700 12,200 31,500 24,000 12,500 15,000 24,500 10,000 15,000 14,000 22,500 19,500 16,000 50,000 Business Expansion Loan Calculator Date Purchase Price Down Payment Loan Amount Rate 3.85% 3.95% 4.05% 4.15% 4.25% 4.35% 4.45% 4.55% 4.65% 4.75% 4.85% 4.95% 5.05% Loan Payment Calculator 10/12/2021 Rate Distribution center Term in Months $ 2,150,000 Monthly Payment $ 430,000 Total Interest $ 1,720,000 Total Cost Varying Interest Rates and Terms Number of Months 120 180 4.35% 180 240 Scenarios 10 Years 15 Years 5 Years Loan Amount $ 1,720,000 $ 1,720,000 Annual Interest Rate 4.45% 4.40% 4.55% Monthly Interest Rate 0.37% 0.37% 0.38% Loan Period in Years 10 15 5 Loan Period in Months 120 180 60 Start Date 1/3/2022 1/3/2022 1/3/2022 Monthly Payment $ (16,000) $ (20,000) Future Value n/a n/a Candidate Properties Type Contact State Months on Mkt Media type Description Action High-energy movie with lots of conflict and special effects Animation Animated movie, usually with elements of comedy Comedy Movie with comic situations and funny characters Drama Serious, plot-driven movie, often with a realistic plot Foreign Movie created outside of the U.S. TV shows Episodic television series 1 Victoria Streaming Service All Locations in 000s 2020 Revenue Type Action Animation Comedy Drama Foreign TV shows Total $ Quarter 1 27,873 17,911 53,493 16,028 5,237 54,207 174,749.10 $ Quarter 2 31,358 21,651 57,304 17,942 6,577 58,611 193,443.51 $ Quarter 3 38,481 21,751 56,773 20,612 1,154 60,671 199,441.43 $ Quarter 4 38,377 21,337 57,071 17,128 3,855 62,511 200,278.07 $ Total 136,088.06 82,650.41 224,640.02 71,710.46 16,822.90 236,000.25 767,912.10 Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Van Thang SUBMISSION #1 | SCORE IS: 42 OUT OF 50 1. Bao Phan is a financial analyst for Victoria Streaming Serv ice (VSS), a website that streams mov ies 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 v isualizing the sales data. GE v er. 8.0.0 1/1 The U.S., Canada , and U.K. worksheets hav e 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. Create a formula using a function. 2. Use the text in cell H5 to fill the range I5:K5 with the names of the remaining quarters in the year. AutoFill v alues in a range. In the U.S., Canada, and U.K. worksheets, cells in the range I5:K5 should contain the auto-filled v alues. 0/1 3. VSS has applied a goal of increasing rev enue to $90,000 in the fourth quarter of next year. For Quarter 1, Bao estimates $84,309 in rev enue, which is the av erage rev enue per quarter from the current year. 1/1 Project the rev enue in Quarters 2 and 3 by filling the series for the first projection (range H7:K7) with a linear trend. AutoFill v alues in a range. 4. Bao is confident that rev enue will increase by at least 2 percent per quarter next year. He wants to calculate these rev enue goals in a second projection. 1/1 Project next year's rev enue in the second projection (range H9:K9) based on a growth series using 1.02 as the step v alue. AutoFill v alues in a range. 5. Bao wants to consolidate the sales data in the U.S., Canada , and U.K. worksheets on the All Locations worksheet. 0/1 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 rev enue from Action mov ies in Quarter 1 (cell B6) in the U.S., Canada, and U.K. b. Copy the formula in cell B6 to calculate the rev enue from the other types of mov ies for all four quarters (range B7:B11 and C6:E11), pasting the formula only. Create a formula using a function. In the All Locations worksheet, the formula in cell B6 should use the SUM function to calculate the total rev enue from Action mov ies in Quarter 1. Copy a formula into a range. In the All Locations worksheet, cell B6 contains an incorrect formula. 6. Bao wants to round the total sales v alues 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. Create a formula using a function. Copy a formula into a range. 7. In cell F14, Bao wants to display the total rev enue from the prev ious 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 rev enue (cell F12) in the All Locations worksheet in the Support_EX19_CS4-7a_Revenue.xlsx workbook. Create a link to an external workbook. The workbook should contain a link to the Support_EX19_CS4-7a_Rev enue workbook. Enter a number in a cell. 8. Bao wants to v isualize how the rev enue for each type of media contributed to the total rev enue for the four quarters. 1/1 0/1 1/1 Create a chart as follows to illustrate this information: a. Create a 3-D Pie chart that shows how the rev enue from each type of media (range A6:A11) contributed to the total rev enue (range F6:F11). b. Mov e and resize the chart so that the upper-left corner is in cell A15 and the lower-right corner is in cell E30. Insert a chart. Resize and reposition a chart. 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, remov e the chart title which is not necessary for this chart. Change the position of the data labels. Update the data labels in a chart. Update the number format of data labels in a chart. Explode a data point in a chart. Change the chart colors. Change the chart style. Remov e a chart title. 10. Bao also wants to v isualize the rev enue for each type of media per quarter. 1/1 1/1 Create a chart as follows to illustrate this information: a. Use the Quick Analysis tool to create a Stacked Column chart that compares the rev enue from each type of media for Quarters 1–4 (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. Mov e and resize the chart so that the upper-left corner is in cell F15 and the lower-right corner is in cell K37. Use the Quick Analysis took to create a chart. Switch the rows and columns of data in a chart. Resize and reposition a chart. 11. Bao decides he wants the chart to compare rev enue from mov ies only. Modify the Stacked Column chart as follows to meet his request and make the chart more meaningful: a. Remov e 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. Remov e the legend, which repeats information in the data table. Update the data series in a chart. Change the type of data table displayed in a chart. Insert a chart title. Remov e a legend from a chart. 12. Bao has a text file that describes the types of media the company prov ides. 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 W izard 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.] Import data from a text file. In the All Locations worksheet, the table from the file Support_EX19_CS4_7a_Media.txt should be inserted in cell H5. Apply a table style. In the All Locations worksheet, the table from the file Support_EX19_CS4_7a_Media.txt should be inserted in cell H5. 13. Bao decides that he might want to sort and filter the rev enue data. Format the range A5:F12 as a table with headers. Format a range as a table. 14. Go to the Original Content worksheet, which lists mov ies and TV shows that VSS is dev eloping itself and indicates whether each project is approv ed for production. Bao wants to list the approv ed projects in a separate part of the worksheet. 1/1 0/1 1/1 1/1 Use an adv anced filter as follows to list these projects in a new range: a. In cell F26, type Yes as the v alue to filter on in the criteria range. b. Create an adv anced 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. Use an Adv anced filter to copy data. Format a range as a table. Filter a table. 15. VSS wants to delay the dev elopment of dramas that hav e not been approv ed because dramas hav e not been selling well. Bao asks you to identify these projects on the Original Content worksheet. 2/2 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 Approv ed? 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?] v alue of "No". Fill the range G3:G23 with the formula in cell G2 if Excel does not do so automatically. Add a column to a table. Create a formula using a function. 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 v alue greater than or equal to 25000. c. Display the yellow circled symbol in cells with a Number type v alue greater than or equal to 15000. d. Display the red circled symbol in cells with a Number type v alue less than 15000. Apply a conditional formatting rule that uses an icon set. 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. 2/2 2/2 Create a formula that prov ides 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 v alue. 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. Create a formula using a function. 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 v alue in cell J2 in the Projects table, and then returns the corresponding start date as an exact match. Create a formula using a function. 19. Bao also wants to calculate the number of projects that hav e a budget of more than $15,000 and determine the av erage budget amount for comedy projects. 2/2 2/2 Create formulas that prov ide 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 av erage the budget amounts for Comedy projects in the Projects table, using the range I11:I12 as the criteria. Create a formula using a function. Create a formula using a function. 20. Finally, Bao wants to summarize the number of projects proposed by the project type and calculate their total and av erage budget amounts. 1/2 Calculate this information for Bao as follows: a. In cell J16, enter a formula using the COUNTIF function that counts the number of Action mov ie 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 mov ie projects, using Projects[Project Type] as the range, cell I16 as the criteria, and Projects[Budget] as the sum_range. d. Fill the range K17:K19 with the formula in cell K16. e. In cell L16, enter a formula using the AVERAGEIF function that av erages the budget amounts for Action mov ie projects. f. Fill the range L17:L19 with the formula in cell L16. Create a formula using a function. AutoFill a formula in a range. In the Original Content worksheet, one or more cells in the range J17:J19 contains an incorrect formula. Create a formula using a function. AutoFill v alues in a range. In the Original Content worksheet, one or more cells in the range K17:K19 contains an incorrect formula. Create a formula using a function. AutoFill v alues in a range. In the Original Content worksheet, one or more cells in the range L17:L19 contains an incorrect formula. 21. Bao wants to compare the projects by project type, start date, and budget. Insert a chart as follows to prov ide 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. Mov e the chart so that its upper-left corner is in cell I21 and its lower-right corner is in cell O37. 1/2 Insert a chart. Insert a chart title. Change the font size of the chart title. Resize and reposition a chart. In the Original Content worksheet, the Treemap chart should be repositioned and resized so that the upper-left corner is located within cell I21 and the lower-right corner is located within cell 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. (Hint : You must complete all actions in this step and the following step correctly to receiv e full credit.) 2/2 Sort the data in the table in ascending order first by project type and then by start date, both in ascending order. Sort a range of cells. 23. Bao also wants to calculate subtotals for each funding type. a. Conv ert the table to a range. b. Insert a subtotal at each change in the Project Type v alue. c. Use the Sum function to calculate the subtotals. d. Add subtotals to the Budget v alues only. e. Include a summary below the data. f. Collapse the outline to display only the subtotals for each project type and the grand total. Conv ert a table to a range Set options for subtotaling data. Create a formula using a function. Display summary v alues. View data in an outline. 24. Go to the Expansion Funding worksheet. VSS is considering whether to expand into selling portable media dev ices for v iewing their streaming content. The company would buy the dev ices 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 cov er the cost of the distribution center. 2/2 2/2 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 v alues in the range C4:C8. Create defined names for a cell. Create defined names for a cell. Create defined names for a cell. 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. Div ide 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 include the present v alue. e. Display the result as a positiv e amount. Create a formula using a function. 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. 2/2 0/2 Create a formula. In the Expansion Funding worksheet, the formula in cell D7 should calculate the total interest of the loan. Create a formula. In the Loan Calculator worksheet, the formula in cell D8 should calculate the total cost of the loan. 27. Bao wants to compare monthly payments for interest rates that v ary 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 range A12:D25. 2/2 Create a two-v ariable data table as follows to prov ide the comparison that Bao requests: a. In cell A12, enter a formula without using a function that references the Monthly_Payment amount (cell D6) because Bao wants to compare the monthly payments. b. Based on the range A12:D25, create a two-v ariable 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. Create a formula. Create a two-v ariable data table. 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. 2/2 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. Create a formula using a function. 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. 2/2 In cell H4, insert a formula using the PV function 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. Create a formula using a function. 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 v alue of the loan. 2/2 In cell I11, 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 v alue of the loan for the 5 Years scenario. Create a formula using a function. 31. Bao is also considering other properties to purchase. The worksheet should list information about these properties, which is contained in a webpage. 2/2 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 W indows Explorer to copy the path to the webpage, and then type \Support_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:E7 from the Support_EX19_CS47a_Properties.html file.] c. Load the webpage data as a table to cell F26 in the existing worksheet. [MAC Hint: Paste the range copied abov e 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.] Format a table. Apply a table style. 32. Bao wants to list the property information in the range F15:I19 using his preferred format. Incorporate the imported data in the range F15:I19 as follows: a. In cell F15, enter a formula using the PROPER function to capitalize the first letter in each word in the Type text in cell F27. b. Fill the range F16:F19 with the formula in cell F15 to list the remaining property types. c. In cell G15, enter a formula using the CONCAT function that displays the first name shown in cell H27 followed by a space (" "), and then the last name shown in cell I27. d. Fill the range G16:G19 with the formula in cell G15 to list the full names of the remaining contacts. e. In cell H15, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell G27. Copy the formula in cell H15 to the range H16:H19. f. In cell I15, enter a formula using the LEFT function to insert the first 2 characters on the left of cell J27. Copy the formula in cell I15 to the range I16:I19. g. Hide rows 26 to 32 so the worksheet does not display duplicated data. Create a formula using a function. Copy a formula into a range. Create a formula using a function. Copy a formula into a range. Create a formula using a function. Copy a formula into a range. Create a formula using a function. Copy a formula into a range. Hide a row or column. 2/2 Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1a Victoria Streaming Service CONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS Van Thang Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Victoria Streaming Service United States in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 12,873 10,027 20,194 9,489 6,406 20,397 $ 79,386.60 Quarter 2 13,869 11,096 21,282 10,036 6,789 21,656 $ 84,727.86 Quarter 3 15,904 11,124 21,130 10,799 5,239 22,244 $ 86,441.55 Quarter 4 15,874 11,006 21,216 9,803 6,011 22,770 $ 86,680.59 Date: Standard: $ 5/12/2021 9.95 Premium: $ 12.95 2022 Revenue Projections $ Total 58,521.16 43,253.26 83,821.72 40,127.56 24,445.40 87,067.50 337,236.60 Quarter 1 Quarter 2 Increase revenue to 90,000 in Q4 84,309 86,206 Increase revenue by 2% per quarter 84,309 85,995 Quarter 3 Quarter 4 88,103 90,000 87,715 89,470 Victoria Streaming Service Canada in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 9,069 6,190 12,006 10,908 7,731 9,707 $ 55,610.71 Quarter 2 9,083 6,836 12,488 11,056 7,792 10,796 $ 58,051.43 Quarter 3 9,120 7,084 12,339 11,144 8,046 10,824 $ 58,558.03 Quarter 4 10,015 6,408 12,411 11,470 8,174 10,866 $ 59,344.79 Date: Standard: $ 5/12/2021 10.95 Premium: $ 13.95 2022 Revenue Projections $ Total 37,287.30 26,518.84 49,244.36 44,577.60 31,743.15 42,193.71 231,564.96 Quarter 1 Increase revenue to 60,000 in Q4 57,891 57,892 Increase revenue by 2% per quarter 57,891 59,049 57,893 60,230 57,894 61,435 Victoria Streaming Service United Kingdom in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 9,653 7,190 12,805 10,498 9,713 10,228 $ 60,085.85 Quarter 2 9,705 7,227 12,928 11,611 9,799 10,282 $ 61,552.39 Quarter 3 9,808 8,265 13,007 11,652 9,886 10,335 $ 62,954.31 Quarter 4 9,812 8,303 13,176 10,736 9,073 10,389 $ 61,489.84 Date: Standard: $ 5/12/2021 11.95 Premium: $ 14.95 2022 Revenue Projections $ Total 38,978 30,986 51,916 44,497 38,472 41,234 246,082.39 Quarter 1 Increase revenue to 65,000 in Q4 61,521 61,522 Increase revenue by 2% per quarter 61,521 62,751 61,523 64,006 61,524 65,286 Victoria Streaming Service All Locations in 000s 2021 Revenue Type Action Animation Comedy Drama Foreign TV shows Total Quarter 1 31,594.80 23,407.00 45,004.37 30,894.80 23,849.88 40,332.31 $ 195,084.00 Quarter 2 32,657.24 25,159.39 46,698.55 32,702.56 24,380.33 42,733.61 $ 204,332.00 Quarter 3 34,832.15 26,473.95 46,476.99 33,595.51 23,171.36 43,403.93 $ 207,954.00 Quarter 4 35,702.04 25,717.36 46,802.44 32,009.58 23,258.62 44,025.18 $ 207,516.00 Total revenue last year: TV shows 20.9% Action 16.5% Animation 12.4% Foreign 11.6% Drama 15.9% Comedy 22.7% $ Total 134,786.23 100,757.70 184,982.35 129,202.45 94,660.19 170,495.03 814,884.00 $ 767,912.10 Media type Action Animation Comedy Drama Foreign TV shows 180,000.00 160,000.00 140,000.00 120,000.00 100,000.00 80,000.00 60,000.00 40,000.00 20,000.00 - Quarter 1 Foreign 23,849.88 Drama 30,894.80 Comedy 45,004.37 Animation 23,407.00 Action 31,594.80 Description High-energy movie with lots of conflict and special effects Animated movie, usually with elements of comedy Movie with comic situations and funny characters Serious, plot-driven movie, often with a realistic plot Movie created outside of the U.S. Episodic television series Movie Revenue Quarter 2 Quarter 3 Quarter 4 24,380.33 23,171.36 23,258.62 32,702.56 33,595.51 32,009.58 46,698.55 46,476.99 46,802.44 25,159.39 26,473.95 25,717.36 32,657.24 34,832.15 35,702.04 ID Project Name AC533 AC210 TV453 TV697 Just Spirits Tension Sunrise Against the Wall Bayridge The Castle Behind Me Mack and Mack Space Plum Karate Night Never in Common All My Friends A Great Getaway Teenage Miracle Mile The Great Grasshopper Downtown Bike Path Because of Winston Mad Music A Slight Case of Spring Paradise Moon Good Gold Bad Blue Shark Myths This Guy Laughed Celeste and Cecilia AC328 CO720 CO789 CO329 DR887 DR543 DR521 CO325 DR512 DR622 TV482 AC736 AC447 TV245 TV598 CO305 CO220 TV552 Project Type Action movie Action movie TV show TV show Action movie Comedy Comedy Comedy Drama Drama Drama Comedy Drama Drama TV show Action movie Action movie TV show TV show Comedy Comedy TV show Start Date 6/22 6/22 7/22 7/22 9/22 9/22 10/22 10/22 11/22 11/22 1/23 1/23 2/23 2/23 3/23 3/23 3/23 3/23 3/23 4/23 4/23 4/23 Budget $45,500 $12,000 $22,500 $28,500 $23,010 $18,555 $25,000 $20,000 $19,700 $12,200 $31,500 $24,000 $12,500 $15,000 $24,500 $10,000 $15,000 $14,000 $22,500 $19,500 $16,000 $50,000 Approved? Delay? Yes No No Yes No No No Yes No No Yes No No Yes No No No Yes No Yes No Yes FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Approved Projects ID Project Name Project Type Approval Date Budget Approved? Yes ID Project Name TV697 Bayridge TV245 Paradise Moon TV552 Celeste and Cecilia Project Type TV show TV show TV show Start Date 7/22 3/23 4/23 Budget $28,500 $14,000 $50,000 Approved? Yes Yes Yes Project Information Project ID Project Name Start Date Budget > 15000 Number of Projects Project Type Comedy Average Budget $ Project Type Action movie Comedy Drama TV show TV245 Paradise Moon 3/23 15 20,509 Number 5 6 5 6 Total Budget Average Budget $ 105,510 $ 21,102 $ 123,055 $ 20,509 $ 90,900 $ 18,180 $ 162,000 $ 27,000 ID Project Name Project Type Action movie Total Comedy Total Drama Total TV show Total Grand Total Start Date Budget $ $ $ $ $ 105,510 123,055 90,900 162,000 481,465 Business Expansion Loan Calculator Date Purchase Price Down Payment Loan Amount $ Loan Payment Calculator 10/12/2021 Rate Distribution center Term in Months $ 2,150,000 Monthly Payment $ 430,000 Total Interest $ 1,720,000 Total Cost 4.35% 180 $13,026.41 $ 624,753.96 $ 2,344,753.96 Varying Interest Rates and Terms Rate Number of Months 13,026 120 180 3.85% $ 17,291.81 $ 12,593.73 $ 240 10,287.42 3.95% 4.05% 4.15% 4.25% 4.35% 4.45% 4.55% 4.65% 4.75% 4.85% 4.95% 5.05% $ $ $ $ $ $ $ $ $ $ $ $ 10,377.60 10,468.23 10,559.31 10,650.83 10,742.80 10,835.20 10,928.05 11,021.33 11,115.05 11,209.20 11,303.78 11,398.80 $ $ $ $ $ $ $ $ $ $ $ $ 17,373.32 17,455.07 17,537.04 17,619.26 17,701.70 17,784.38 17,867.29 17,950.44 18,033.81 18,117.42 18,201.26 18,285.33 $ $ $ $ $ $ $ $ $ $ $ $ 12,679.58 12,765.77 12,852.31 12,939.19 13,026.41 13,113.97 13,201.88 13,290.12 13,378.71 13,467.63 13,556.89 13,646.49 Scenarios Loan Amount Annual Interest Rate Monthly Interest Rate Loan Period in Years Loan Period in Months Start Date Monthly Payment Future Value 10 Years 15 Years 5 Years 1,720,000 $ 2,105,561 $ 1,720,000 4.45% 4.40% 4.55% 0.37% 0.37% 0.38% 10 15 5 120 180 60 1/3/2022 1/3/2022 1/3/2022 $ (17,784) $ (16,000) $ (20,000) n/a n/a $1,344,619.46 $ Candidate Properties Type Warehouse Distribution Center Warehouse Manufacturing Center Distribution Center Contact State Terry McGregor TN Anita Arnaz AZ Hector Murillo TX Padma Shalikar SC Brad Schultz IL Months on Mkt 8 14 22 10 5

Already member? Sign In