Using “Save As,” save the file as an .xlsx file type, and name it with your last name, for example “callahan.xlsx” (as a “Microsoft Excel Worksheet.”). Use the last name TRINIDAD
Add column headers to match the data. In other words, the first row of the Excel spreadsheet should include the column headings store number, sales region, item number, item descriiption, unit price, units sold, and month-year.
Manipulate the data
Next, you will use Microsoft Excel’s formula functions to sort, group, and manipulate data. For guidance using the functions described in the following steps, refer to the Learning Resources page that is found in this “Using Spreadsheets” module. Before attempting the following steps, you may wish to complete the “Practice: Using Spreadsheets” activity, which is also found in the “Using Spreadsheets” module.
Note: Steps 5 through 13 below should be completed on one worksheet in the Excel spreadsheet. Label the worksheet “5-to-13”. Steps 14, 15, and 16 should be on separate worksheets of their own. For Step 17, each pivot table and the corresponding chart should be on its own worksheet.
5. Add a new columns to this spreadsheet — name the new column “Sale Amount”. In this column, add a formula for computing the sale amount for each row (each sales record) by multiplying unit price and units sold.
6. What are the total sales for the quarter? Use the SUM function to accomplish this.
7. Find the minimum sale amount at each store and the maximum sale amount at each store. In other words, compute the lowest dollar amount of sale at each store and the highest dollar amount of sale at each store. You can accomplish this either by constructing pivot tables or by using built-in Excel formula such as MINIFS and MAXIFS.
8. Use the SUMIF function to find the total sales for each store.
9. Use the SUMIF function to find the total sale for each region.
10. Assume that we have the following profit margins: desktop CPU (or laptop) profit margin is 25%, 101 keyboard’s profit margin is 10%, 17” monitor is 10%, pc mouse is 5%. How much profit did we make this quarter? What is our total profit margin?
Profit Margin = Total Profit/Total Sales
Round the profit margin to the nearest hundredth of a percentage.
11. Based on the profits computed in step 10, which is the best performing region in terms of profit, and what were the quarterly profits for that region?
12. Based on the profits computed in step 10, which is the best performing store in terms of profit, and what were the profits for that store?
13. Suppose we are able to increase our profit margins so that the new profit margins are indicated as follows: desktop CPU (or laptop) profit margin is 26%, 101 keyboard’s profit margin is 15%, 17” monitor is 15%, pc mouse is 15%. What is the new profit amount? Compared to the profit you computed in Step 10, how much (in dollar figures as well as in percentage of change) does our total profit increase? Percentage of change from the original value to the new value is computed as follows.
Percentage of change =(New Value − Original Value)*100/(Original Value)
14. Based on total quarterly sales, what percentage of total sales does each region contribute? Submit your answer on a separate worksheet.
15. Based on total quarterly sales, what percentage of total sales does each store contribute? Submit your answer on a separate worksheet.
16. What are the total sales by month? Submit your answer on a separate worksheet.
Sorting Data
17. Create pivot tables for the following: (i) Total sales by Region; (ii) Total sales by Store; (iii) Total sales by Item number; (iv) Total sales by Month. For each Pivot table, also construct a chart (graph) of your choice for the data. For example, for (i), you can construct a bar graph with region on the x-axis and total sales on the y-axis. For (ii), you may choose to construct a pie-chart depicting total sales by store. Each pivot table and the corresponding chart should be on its own worksheet. Do your answers from steps 8 and 9 above match the answers you arrived at by using pivot tables?