Create a chart as follows to illustrate this information:
If present, remove the chart title which is not necessary for this chart.īao also wants to visualize the revenue for each type of media per quarter. Move and resize the chart so that the upper-left corner is in cell A15 and the lower-right corner is in cell E30.įormat the 3-D Pie chart as follows to make it easier to interpret:Īdd data labels to the chart on the Outside End of each slice.ĭisplay only the Category Name and Percentage amounts in the data labels.Ĭhange the number format of the data labels to Percentage with 1 decimal place.Įxplode the largest slice (Comedy) by 10 percent.Ĭhange the chart colors to Monochromatic Palette 1 to coordinate with the data source range.Īpply Style 8 to the chart to simplify the chart design. Create a chart as follows to illustrate this information:Ĭreate 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). 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.īao wants to visualize how the revenue for each type of media contributed to the total revenue for the four quarters. Open the file Support_EX19_CS4-7a_Revenue.xlsx. In cell F14, Bao wants to display the total revenue from the previous year. In cell B12, add the ROUNDUP function to display the total sales for Quarter 1 rounded up to 0 decimal places.įill the range C12:F12 with the formula in cell B12. 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.Ĭopy 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.īao wants to round the total sales values so that they are easier to remember. Ungroup the worksheets, go to the All Locations worksheet, and then consolidate the data as follows:
Project next year's revenue in the second projection (range H9:K9) based on a growth series using 1.02 as the step value.īao wants to consolidate the sales data in the U.S., Canada, and U.K. worksheets on the All Locations worksheet. He wants to calculate these revenue goals in a second projection. Project the revenue in Quarters 2 and 3 by filling the series for the first projection (range H7:K7) with a linear trend.īao is confident that revenue will increase by at least 2 percent per quarter next year. For Quarter 1, Bao estimates $84,307 in revenue, which is the average revenue per quarter from the current year. VSS has applied a goal of increasing revenue to $90,000 in the fourth quarter of next year. Use the text in cell H5 to fill the range I5:K5 with the names of the remaining quarters in the year. In cell H1 of the U.S. worksheet, enter a formula using the TODAY function to display today's date. The first change is to display today's date. Group the U.S., Canada, and U.K. worksheets to make changes to the three worksheets at the same time. The U.S., Canada, and U.K. worksheets have the same structure and contain similar data. He is tracking sales for the year and asks for your help in projecting future sales and visualizing the sales data.
If cell B6 does not display your name, delete the file and download a new copy from the SAM website.īao 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. 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.
To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer: The program will add the file extension for you automatically. xlsx file extension in the Save As dialog box, do not type it. Save the file as SC_EX19_CS4-7a_ FirstLastName_2.xlsx by changing the “1” to a “2”. Open the file SC_EX19_CS4-7a_ FirstLastName_1.xlsx, available for download from the SAM website. Open the file SC_EX19_CS4-7a_FirstLastName_1 MS ExcelĬONSOLIDATE 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 Shelly Cashman Excel 2019 | Modules 4-7: SAM Capstone Project 1aĬONSOLIDATE DATA AND USE FINANCIAL FUNCTIONS, TABLES, AND CHARTS