PROJECT STEPS
- 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.
a. 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.
b. Fill row 5 under 2025 Revenue Projections with the remaining quarters in
the year.
c. VSS has applied a goal of increasing revenue to $90,000 in the fourth
quarter of 2025. For Quarter 1 and 4, Bao has beginning estimates in H7
and desired ending estimates in K7. Project the revenue in Quarters 2 and
3 by filling the series for the first projection with a linear trend.
d. Bao is confident that revenue will increase by at least 2 percent per
quarter in 2025. He wants to calculate these revenue goals in a second
projection.
Project the revenue for 2025 in the second projection based on a 2
percent growth series.
e. Ungroup the worksheets. - 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 in the U.S., Canada, and U.K. Be sure not to mess up the formatting as
you copy formulas. The All Locations table should remain looking like a nice
table with alternate colors in each row. - Bao wants to round the total sales values so that they are easier to remember.
Add a row under the table and round the answer in row 12 (the total) to the
nearest hundred dollars). - 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_EX365_2021_CT_CS4-7c_Revenue.xlsx workbook. - 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:
a. 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
A19:E35.
b. 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 rename the chart title to something
appropriate.
- Bao also wants to visualize the revenue for each type of media per quarter.
To illustrate this information, create a Clustered Column chart that:
a. compares the revenue from each type of media for Quarters 1–4. You
should have quarters 1 through 4 on the x axis. Move and resize the chart
so that it covers the range F20:K40.
b. Bao wants the chart to compare revenue from movies only. Modify the
Stacked Column chart by removing 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. - Bao has a text file that describes the types of media the company provides.
Import the data from the file Support_EX365_2021_CT_CS4-7c_Media.txt.
a. Use the first row as headers, and load the data as a table next to the All
Locations 2024 Revenue table but leave a couple blank columns between
the two tables.
b. Apply the Blue, Table Style Medium 2 table style to the imported table
to coordinate with the rest of the worksheet contents. - Bao might want to sort and filter the revenue data as well. Format the data
under the cell labeled “2024 Revenue” as a table with headers and using the
Blue, Table Style Medium 2 table style. (so rows 5 through 12 are your table) - 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.
a. Use an advanced filter to list these projects in a new range. Space is
already set aside in rows 24, 25, and after. In the appropriate cell, enter a
value to ensure that the filter returns only projects that have been
approved.
b. Apply the advanced filter to the Projects table using the appropriate
specified criteria. Copy the results to another location, starting at A28.
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. - 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.
a. Determine which projects meet the criteria. Insert a column named
Delay? to the right of the Approved? column.
b. 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.
- 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, create a new conditional formatting rule using the 3
Symbols (Circled) icon set.
b. 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. - 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.
a. 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.
b. 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. - 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.
a. 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.
b. In the appropriate cell, use a function to find the average budget of
comedy projects, using the entire Projects table as the database. - Finally, Bao wants to summarize the number of projects proposed by the project
type and calculate their total and average budget amounts.
a. 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.
b. 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. - 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.
a. Sort the data in the table in ascending order first by project type and then
by start date, both in ascending order.
b. 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.
c. 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 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 man Excel 365/2021 | Modules 4-7: SAM Critical Thinking Capstone Project 1c 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.
- a. Create defined names for all the numbers in the top left table (Loan
- Payment Calculator). The names for column B are in column A and the
- names for column D are in column C.
- b. Calculate the Loan Amount in B8. Calculate the monthly payment for a
- loan to purchase the distribution center.
- c. Without using a function,
- i. calculate the total interest (multiply the monthly payment value by
- the value for the term in months, and then subtract the loan
- amount.)
- ii. calculate the total cost. (add the price to the total interest.)
- Bao has three other options for purchasing the distribution center.
a. 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.
b. 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.
c. 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. - Bao imported the original candidate properties table but now wants to list the
candidates’ information in the prepared area using his preferred format. Under
the area “candidate properties modified”
a. Complete the Type column by using a function to capitalize the first letter
in each word from the “original candidate” table
b. Use a function to consolidate the first candidate’s first and last name
information with one space in between.
c. In the State column, use a function to display the last two characters of
the Location for each of the candidates.
d. 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.
e. Hide rows 13 to 20 so the worksheet does not display duplicated data.
Save your changes, close the workbook, and then exit Excel. Upload your file to Canvas
for manual grading.
