Excel 2016 capstone project ex 1 working with sales data

SIMnet 2016: Excel 2016 Capstone Project Level 3

1 | P a g e Last Modified: 8/2/2016

Excel 2016 Capstone Project EX-3 Working with Sales Data 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. You 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. 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

 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

 Change the chart title

 Hide the chart legend

 Show chart data labels

 Move a chart

 Insert a column chart

 Switch the row/column in a column chart

 Apply a chart Quick Style

 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

 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

 Use the MIN function in a formula

 Use the MAX function in a formula

 Wrap text

 Analyze data with Goal Seek

 Convert data into a table

 Apply a table Quick Style

 Use the table Total row