Excel 365/2019 Capstone Level 3 Working With Sales Data Alternate With VLOOKUP

Excel 365/2019 Capstone
Level 3 Working With
Sales Data Alternate With
VLOOKUP
In this project, you will work with sales data from Top’t Corn, a popcorn
company with an online store, multiple food trucks, and two retail stores. You
will begin by copying the sales data for one of the retail stores from another
workbook. Next, you will insert a new worksheet and enter sales data for the
four food truck locations, formatting the data, and calculating totals. You will
create a pie chart to represent the total units sold by location and a column
chart to represent sales by popcorn type. You will format the charts, and then set
up the worksheet for printing. Next, you help Top’t Corn calculate payments for
a loan and decide whether or not the purchase is a good idea. Working with the
daily sales data for one of the brick-and-mortar stores, you will apply
conditional formatting to find the top 10 sales dates. Y ou will also calculate the
sales for each date, and the average, minimum, and maximum sales. You will use
Goal Seek to find the appropriate price to reach a higher daily average sales
goal. You will use VLOOKUP to look up sales data for a specific date. Finally, you
will work with their online sales data to format it as an Excel table and apply
sorting and filtering. You will create a PivotTable and a PivotChart from a copy of
the online sales data to summarize the sales.
Skills needed to complete this project:
Open a workbook
Copy a worksheet to another workbook
Close a workbook
Insert a worksheet
Name a worksheet
Move a worksheet
Enter text
2/10
https://pmu.simnetonline.com/sp/assignments/projects/details/9496948
03/12/2024, 22:37
Excel 365/2019 - Level 2 Yellow Belt - Capstone Project - Working with Sales Data Alternate with VLOOKUP - SIMnet
Enter numbers
Edit text
Autofit a column
Apply a cell style
Add cell shading
Change font color
Merge and center text across cells
Apply bold formatting
Apply number formatting
Enter a SUM function
Copy formula using AutoFill
Insert a pie chart
Apply a chart Quick Layout
Move a chart
Insert a column chart
Switch the row/column in a column chart
Change the chart title
Apply a chart Quick Style
Show chart data labels
Preview how a worksheet will look when printed
Change worksheet orientation
Change the print margins
Scale a worksheet for printing
Change the color of a worksheet tab
Apply a column width
Calculate a loan payment with PMT
Enter a simple formula using multiplication
Enter a simple formula using subtraction
Add cell borders
Create a formula referencing cells in another worksheet
Enter an AVERAGE function
Use the IF function
Hide a worksheet
Apply date formats
Apply Top Ten conditional formatting
Use an absolute reference in a formula
Name a range of cells
Use a named range in a formula
3/10
https://pmu.simnetonline.com/sp/assignments/projects/details/9496948
03/12/2024, 22:37
Excel 365/2019 - Level 2 Yellow Belt - Capstone Project - Working with Sales Data Alternate with VLOOKUP - SIMnet
Use the MIN function in a formula
Use the MAX function in a formula
Wrap text
Analyze data with Goal Seek
Use the VLOOKUP function in a formula
Convert data into a table
Apply a table Quick Style
Use the table Total row
Sort data in a table
Filter data in a table
Create a PivotTable
Create a PivotChart
Unhide a worksheet
Steps to complete this project:
Mark the steps as checked when you complete them.
Important: Download the resource files needed for this project from the
Resources link. If the files download in a zipped folder, make sure to extract the
files after downloading the resources zipped folder. Visit the SIMnet instant help
for step-by-step instruction.
1. Open the start file EX2019-Capstone-Level3.
Note: If the workbook opens in Protected View, click the Enable Editing
button in the Message Bar at the top of the worksheet so you can modify it
2. The file will be renamed automatically to include your name. Change the
project file name if directed to do so by your instructor, and save it
3. Copy the OldTownStore worksheet from the OldTownSales workbook
(downloaded from the Resources link) to the capstone project.
a. Open the Excel file OldTownSales.
b. Copy the worksheet OldTownStore. In the Move or Copy dialog, be sure
to check the Create a copy check box and select your capstone project
Excel file from the Move selected sheets to book drop-down list. Make
the correct selection to ensure the copied worksheet will appear at the
end after the TysonsStore2018 worksheet in your capstone workbook.
c. Close the OldTownSales workbook when you have successfully copied
the OldTownStore worksheet to the capstone workbook.
d. Before continuing, verify that you are working in the capstone project that
you downloaded and not the OldTownSales workbook that you