The actual assignment closely mirrors the practice assignment, and is not accompanied by detailed instructions. It is highly recommended that you first complete the practice assignment in order to smoothly complete the ACTUAL assignment. Please submit ONLY the ACTUAL Excel assignment in the form of two attachments here — your .xlsx. Excel file and your Word report. Do NOT submit the practice assignment. Work not deemed original will receive no credit. Submissions in any other formats will not be graded. A Word report unaccompanied by an Excel file will not be graded. Note:10% will be deducted for each day past the deadline.70% will be deducted for a week late. Assignments will not be accepted after a week. There will be no make-up assignments if you fail to submit this assessment. There will be no exceptions to the above rules. Additional resources for assignment are attached below
It is highly recommended that you first complete the practice assignment (see
assignment instructions in Sakai) in order to smoothly complete this module.
This assignment is based on the Microsoft Excel spreadsheet called
Orders_Truncated.xlsx also posted with your assignment. In this assignment, you will first add several new columns to the existing data fields and populate them with computed variables using appropriate Excel formulas. Next, you will analyze the data in the spreadsheet using Excel features such as data filters, conditional formatting, and pivot charts. You will also prepare a word-processed report documenting your answers. The attached spreadsheet contains sales orders data for a specialty products company that offers numerous products under several product categories through three channels
– phone (PH), mail order (ML), and online/Web (WE). The original spreadsheet
contained hundreds of thousands of records. It has been pared down to 4068 records for the purpose of this assignment. Each row pertains to a single item ordered by a single customer. There are 22 columns (data fields), as follows:
PART A: Data Preparation (this part is the same as practice problem)
Add the following new columns to the right end of the Excel spreadsheet, and populate them with computed data by applying appropriate formulas, based on the following information.
i. NET_REVENUE in Column W, defined as: (Extended Price in column O times
Quantity in column Q) minus Return Revenue in column V.
ii. NET_PROFIT in Column X, defined as: (Shipped Quantity in column S minus
Returned Quantity in column U) times (Extended Price in column O minus
Extended Cost in column N).
iii. SALE_YEAR in Column Y, defined as: The four digit year contained in the Order date in column M.
iv. PERCENT_RETURNS in Column Z, defined as: Returned Quantity in column U divided by Shipped Quantity in column S, but only if Shipped Quantity is greater than zero. (If Shipped Quantity is zero, the value in Column Z is to be entered as zero also.)
v. NET_QUANTITY in Column AA, defined as: Shipped Quantity in column S minus Returned Quantity in column U.
vi. PRICE_CATEGORY in Column AB, defined as: ‘1’ if Extended Price in column O is less than $100, ‘2’ if Extended Price is at least $100 but less than $200, ‘3’ if Extended Price is at least $200 but less than $300, and ‘4’ if Extended Price is greater than $300.
PART B: Analysis (this part is different but similar to practice problem)
1. Copy the original sheet (‘Sheet1’) containing data in all of your columns A
through AB into a new sheet called ‘Filter1.’ Then apply a filter to the copied data in this new sheet in order to answer the following question: Which year had the
highest net quantity in a single order? Provide the pertinent details.
2. Now consider ALL orders in the year you found in Question 1 above. Compare them collectively with orders from other years. Provide two meaningful
observations about this particular year from your comparative analysis.
3. Copy the original sheet (‘Sheet1’) containing data in all of your columns A
through AB into a new sheet called ‘Filter2.’ Then apply a filter to the copied data in this new sheet in order to answer the following question: Which product
categories pertained to orders with the highest net revenue (top 2%)? Provide
the pertinent details.
4. Copy the original sheet (‘Sheet1’) containing data in all of your columns A
through AB into a new sheet called ‘What-IF.’ Idea is to study the What is the
impact on net profit if price is raised by 10%. (hint: create a new column with
10% price increase, calculate profits based on new price –same as what you did in part ii of data preparation — and then find % change in new and old profit.
PART C: Pivot Charts –Create pivot charts to answer questions 5 through 8.
5. In how many distinct years during the period 2009-2012 did product category P generate the highest total net profit, compared to other product categories?
Which years were they, and what were the total net profits for product category P in those years?
6. How does the answer to Question 5 above change if each of the four price
categories in column AB are analyzed separately?
7. Which specific combination of sales channel and year had the highest average percent returns, compared to all other combinations of sales channels and years (e.g., Web sales in 2010, or, mail order sales in 2009)? What was the percent value of this highest average return rate?
8. Create a bar or column chart of average of net quantity on the vertical axis
against price categories on the horizontal axis. Do you observe a distinct
pattern? Suggest a possible business or consumer rationale for this pattern.
Submit your Word report containing your answers AND your Excel file containing your analysis. Upload first the Word file, then upload the Excel file and, finally, submit your assignment.
Part A: Data Preparation
8(make sure to provide
rationale; see question)
Page 3 of 4
Additional Resources available:
..Achievement and Learning Center (410-837-5383)