In this assignment, you will load sales data in a spreadsheet program $($e$.$g$.$ Microsoft Excel$),$ apply spreadsheet functions to data, and use the results of functional computations to answer questions about sales and performance at the various branch stores.

Follow the steps below to complete the assessment. You may wish to print this page $($the print button is found on the lower half of this webpage$).$

Load the data onto your computer.

Download the sales data document Download sales data document.

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."$).$

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 description, 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.

Module$-1-$Tech$-$Assgn$-$Figure$-1.$png

Figure $1$

$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 MINIFSLinks to an external site. and MAXIFSLinks to an external site..

$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\text{'}\text{'}$ 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\text{'}\text{'}$ 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?