Excel Data Analysis Assignment:
This assignment is an individual assignment. For this assignment you will need to download the file SectionESpring15.xlsx from Blackboard, analyze the data using Microsoft Excel and answer questions based on your findings from your manipulations of the data. Your Excel spreadsheet should be submitted on Blackboard no later than 11:59 PM on April 29, 2015.
1. Download the spreadsheet SectionESpring15.xlsx from Blackboard. Save this file as: E_FirstName_LastName _Excel.xlsx. For example, if I were to submit this assignment I would need to save and rename the spreadsheet as E_Natasha_Veltri _Excel.xlsx. Save a copy of the original data sheet to a new page in the workbook and rename that data sheet Working Copy of Data. Format the sheet so that the reader can see all of the column names and all of the data in the columns so that the words aren't cut off or hidden. On a separate work sheet create a List Definition Table to explain to users what the data in the columns of the Original Data worksheet mean.
2. Use the data on the Working Copy of Data sheet along with the techniques learned in class (filtering, autofiltering, conditional formatting and pivot tables) to answer the following
questions: (Create a work sheet named Answers and place your answers on this sheet (the answers must be clearly marked so I know what the answer is to each part of question 2.)
a. Create a copy of your Working Copy of Data and re-name it 2a. Use conditional formatting to highlight in red the team name for all sales made by your muesli company. For example, if my company was A, I would highlight all cells that contained AA under Team (5 points)
b. Create a copy of your Working Copy of Data and re-name it 2b. Use filtering to filter out all of the cereals except those sold by your company. Find the smallest number of boxes purchased from your company based on the Quantity column. Use fill color red to highlight this quantity. Which cereal is this?
c. Create a copy of your Working Copy of Data and re-name it 2c. What was the highest Price charged by any company for a box of muesli? Change the fill color to green for those prices. Which company or companies sold those boxes? (5 points)
d. Using the Working Copy of Data, create a pivot table to answer the following questions. Name the sheet containing the pivot table 2d. Which product sold for the highest average price? Highlight the muesli and amount in red. Which product sold the least based on quantity (count) of sales transactions? Highlight the muesli and amount in green.
3. Create the following Pivot Tables: (Also place your answers on a sheet you created named Answers (the answers must be clearly marked so I know what the answer is to each part of question 3.)
a. Using data from the Working Copy of Data sheet create a pivot table showing Distribution Channel (DC) and Area in the Columns and Team in the Rows. Place Sum of Units in the body of the table. Name the sheet containing this table 3a. How many boxes of muesli did your team sell in Distribution Channel 12 in the South?
Highlight this Quantity in Red.
b. Using data from the Working Copy of Data sheet create a pivot table showing Team in the Columns and Product in Rows. Place Count of Price in the body of the table. Name the sheet containing this table 3b. How many sales did your team make for 1 kg Blueberry Muesli? Highlight this Quantity in green. (5 points)
c. Using data from the Working Copy of Data sheet create a pivot table showing Team in the Columns and Round in the Rows. Place Sum of Value in the body of the table. Name the sheet containing this table 3c. What was value of sales for your team in round 3? Highlight this value in Blue.
4. Create a copy of your Working Copy of Data and re-name it 4.
a. Create the following lookup table inside this worksheet (you can place it anywhere you want where there is no data, but you must leave at least one empty column between the existing data and this lookup table). Give this table a name like we did in class practice (or refer to the comment about absolute referencing below)
b. Create a new column to the right of column Area in this worksheet using the insert command and name it Place. Fill the new column you created with the name of the place by using the LOOKUP function. This function will convert the area code (NO, SO, etc.) from column Area into the appropriate place name (North, South, etc.) using the lookup table above.
Hint: Make sure to either name your table (array) using Name Box in Excel as was shown in class (Then you will simply reference an array (=LOOKUP(F2,area)) or to set the cell addresses for the referenced table to absolute addressing BEFORE copying the formula to other cells by typing in the dollar signs as shown below : If you are using absolute referencing your output should look like the table below when using show formula mode (cell address will vary depending on where you built your lookup table).
When in normal mode the spreadsheet should look like this table below.
c. Copy this formula down so that the Place shows for all of the Sales.
5. What SUP manufactures rigid standup paddleboards and inflatable boards in a variety of colors and component combinations. Identical paddleboards are produced in lot sizes listed below. The projected demand, lot size, and time standards are shown in the following table:
The shop currently works 8 hours a day, 5 days a week, 51 weeks a year. It operates five workstations each producing one paddleboard in the time shown in the table. The shop maintains a 20 percent capacity cushion (i.e. it expects not to operate 20% of the available
time).
How many workstations will be required next year to meet expected demand for without using overtime and without decreasing the firm's current capacity cushion? Show your work on a worksheet in your workbook named 5 and put your answer on your answer sheet.
6. Copy your worksheet from problem 5 three times and name the new worksheets 6A, 6B, and 6C. Using Goal Seek from What-If Analysis answer the following questions:
a. On the worksheet 6a, if I set the capacity requirement to 8, how many days must my plant operate per year? Fill the answer cell on the Goal Seek 1 worksheet in yellow.
b. On the worksheet 6b, if I set the capacity requirement to 7, how many hours per day must the process operate? Fill the answer cell on the Goal Seek 2 worksheet in red.
c. On the worksheet 6c, if I set the capacity requirement to 9, what will my capacity cushion be? Fill the answer cell on the Goal Seek 3 worksheet in blue.
Place the answers to 6a, 6b, and 6c on your answer sheet.
7. Place all of your worksheets in a row where I see the Answer Sheet when I open the workbook, followed by the original data, Working Copy of Data, List Definition Table, and then the rest of the sheets should be in order by question number. (5 points)
8. Upload the workbook containing your answers and your work sheets with pivot tables, charts, etc. to Blackboard using the Assignment upload feature. This assignment is due April 29, 2015.