ISM 3011 Assignment 3 Excel Case 3 Instructions: Build the initial worksheet then using that as a basis do each Deliverable on a separate tab of 1 Excel worksheet. Jackson manufacturing is a precision milling company in the Pensacola Florida area. As part of your work assignment, you have been requested to do some basic analytics on some of the companies’ sales data. Management is interested in 5 specific products which are identified by their SKU number. There are two data files, customers, and invoices from the week of 3-7 February 2020. The first data file is customers which has 50 unique records and the second is invoices which is composed of 111 transactions.

Due: in Canvas Drop Box by 11pm on the due date (see course schedule).

Instructions: Build the initial worksheet then using that as a basis do each Deliverable on a separate tab of 1 Excel worksheet.

Jackson manufacturing is a precision milling company in the Pensacola Florida area. As part of your work assignment, you have been requested to do some basic analytics on some of the companies’ sales data. Management is interested in 5 specific products which are identified by their SKU number. There are two data files, customers, and invoices from the week of 3-7 February 2020. The first data file is customers which has 50 unique records and the second is invoices which is composed of 111 transactions.

The customer data tab is organized in the following manner:

Phone: format (999) 999-9999 - the phone number is unique in the customer data worksheet and it is used as the primary identifier

FirstName: character – the first name of the customer

LastName: character – the last name of the customer

Sex: character – coded M for Male and F for Female

Age: numeric – number of years old the customer was in February 2020

Street#: character – street address of the house that the customer lives in

Street: character – the name of the street that the customer lives on

City: character – the name of the city the customer lives in

State: character – the name of the state where the city is located

Zip: numeric – the 5-digit zip code where the customers’ house is located

The second data tab is the FebInvoices tab which is structured in the following manner:

Seq #: numeric – the transaction number in sequential order for the week of 3-7 February 2020

Phone: format (999) 999-9999 – the phone number of the customer who made the purchase, note that a customer can make multiple purchases during the month

Date/Time: Julian date formatted to appear as DD/MM/YY

SKU: character – format 99999-99999 – the product identification number

Payment: character options Credit or Cash

Qty: numeric – the number of items that the customer purchased – all items in a single invoice will have the same SKU

Management has also given you their retail pricing structure for the sale of 1 item:

                                                                                                                        SKU                    Price

25641-48975          24.95

54127-84671          28.95

27541-54761          21.95

58561-87624          26.95

36761-87616          22.95

There is a 5% discount from the single item price if 2 are purchased and a 10% discount if 3 or more are purchased during a single transaction. There is also a 3% convenience fee on the total transaction for the use of a credit card.

Deliverables:

Each deliverable should be on a separate tab in the workbook. Formatting of the data should meet the following company standards:

Currency should be formatted with 2 decimal places, a comma if necessary, and any negative numbers should be in red with parenthesis around the number.

All dates should be in DD/MM/YY format.

Integer values should be formatted with no decimal places.

All data columns should have short descriptive titles

Deliverable 1: Copy the FebInvoices data to a new tab named Deliverable1 and insert the customer’s FirstName and LastName between the Seq # and Phone columns. Use a vlookup to copy the names from the customers worksheet to the deliverable1 worksheet. Use a range name to identify the customer data.

Deliverable 2: Copy the data you created in Deliverable 1 to a new tab named Deliverable2. Use the paste special values option so no formulas are copied. Create a table to the right of the data for the price structure. Be sure to include the 5% and 10% quantity discounts. Using a vlookup place the per unit cost for each transaction in Column I. Label the new column Price/unit.

Deliverable 3: Copy the data you created in Deliverable 2 to a new tab named Deliverable3. Use the paste special values option so no formulas are copied. In the J column calculate the extended price. Calculate the credit card convenience fee if appropriate add it to the extended price and place the result in column K. Place the label “Grand Total” in cell P2. In cell Q2 place the total amount of sales for February.

Deliverable 4: Using the data you created in Deliverable 3 create a pivot table on a tab named Deliverable4 that is filtered to show the customers last name who purchased 3 or more items. Show the SKUs that they purchased, the quantity of each SKU, and the total number of items that they purchased.

Deliverable 5: Using the data you created in Deliverable 3 create a pivot table on a tab named Deliverable5 that has the count and sum of the qty sold by SKU. On this pivot table the total sales and the percent of sales by SKU should be included.

Deliverable 6: Using the data you created in Deliverable 3 create a pivot table on a tab named Deliverable5 that organizes the data by SKU and within each SKU the payment method. Each detail line should have how many transactions fit that category and the total sales in that category.

Deliverable 7: Using the data you created in Deliverable 3 create stacked bar chart for each SKU showing the breakdown of method of sales on a tab labeled Deliverable 7. The chart should have a title on each axis and title for the chart. This is probably most easily done by creating a pivot table then using that summarized data to make the chart.

pur-new-sol

Related Questions