This week, you will complete another practice activity using MS Excel. See and follow the instructions in the attached document: Excel_Activity_2.pdf. Before you start, review the resources provided above.
Follow each of these steps in creating your own Excel spreadsheet.
Online Sales
This is a project where you will make up the data, but the analysis will be genuine.
You are the expert. You have been asked to develop a spreadsheet to automatically calculate
daily and weekly statistics about 5 products that are sold online. The information you will need
to include is listed below, however you will have to determine which mathematical formulas to
include in order to get the appropriate statistical results. Hints will be provided on where to
enter the data in your chart in order to get the best overall layout for your spreadsheet.
Remember to use appropriate formatting tools in order to make your spreadsheets look
attractive (merge and center, borders, bold, highlighting, etc.).
Go to any online store (like Walmart, Target, or Hayneedle) and select a department you are
interested in. Choose 5 items for sale from that one department. (Example: 5 different kinds of
Dog Food from the Pet Department). Note the Product Name and Price information about that
product.
Section 1:
• Set up an Excel Spreadsheet to show the Product information.
• Include the following information at the top of your spreadsheet (format appropriately).
• Your name, with your title (Mr., Ms., or professional title).
• Title of your spreadsheet (make it appropriate for the products you chose – Example:
Dry Dog Food Sales).
Section 2 (Sum function):
• Create Rows for each of your Products (5 products – you fill in the correct product
name).
• Create a Column with the Price of each of the 5 products (you can get this from the
website).
• Create a Column for Total Quantity Sold for each product (you will make up these
numbers)
• Create a Column for Total Dollar Sales for each product (you will use a formula to
multiply the Price X Quantity).
o For example, if the price for Purina Dog food is $8.00 and you sold 10 bags, the
Sales would be $80.00. (Be sure you use a formula and let Excel do the
calculating. Don’t calculate it yourself and just type in the number.)
Section 3 (Min and Max function):
• Identify which Product sold the most Sales – you must use the MAX function.
• Identify which Product sold the least Sales – you must use the MIN function.
[Hint: On the main bar, use the drop down next to the Sum icon.]
You should see something like =MAX(D5:D10) and =MIN(D5:D10).
Section 4 (Average function):
Calculate the average Sales for all the products – you must use the “AVERAGE” function.
Section 5 (Charts):
• Create a graph/chart to compare the Dollar Sales of all your products.
• Ensure the title and labels are appropriate and that all numeric values use accounting
notation. (Example: 5 dollars is listed as “$5.00”)
[Hint: Highlight the cells you want in your chart. Click on Insert at the top and select a chart
in the Charts section. You can use any type of chart you want. Below is an example.]
Section 6 (Name and Finish):
• Give your Worksheet an appropriate name (on the tab at the bottom) and Save your
Workbook with your name in the title.