(Solved): Excel Project- Pivot Tables, Functions, Etc.

Project 3 – Spring 2021 BMGT 301 Dr. Karake Due by 11:59 PM, April 11th, 2021 8 Points – This in an individual assignment Narrative: Karake, Inc. has lately seen increased demand for its ErgoBeds line. Over the last year, the company started accepting orders from countries outside the United States; specifically, the company has seen increased demand from 11 different locations. All types of ErgoBeds are demanded from across the world. Customers are willing to pay higher prices for these products and hence it seems like a profitable new service. The price of the full service is based on two main determinants: (1) the type of bed by destination, and, (2) whether the customer is requesting expedited shipping. Initially you were tasked to analyze the income distribution among the different bed types and destinations. For expedited shipping, the price of the bed increases by 25%. Questions: Open the Project 3 Data Spring 2021 file and save it on your system as Project3_YourFirstName_YourLastName (Example “Project3_Zeinab_Karake.xlsx”) 1. Change the name of Sheet1 to Transactions. (0.25 Points) 2. Name the range L4:R14 Prices. (0.25 Points) 3. Use an Excel built-In function in column F to match airports with the corresponding destinations. (0.75 Points) 4. Use an Excel built-in function to calculate prices in column G based on the two determinants highlighted above. (1.25 Points) 5. On a new sheet, use the PivotTable application to find the total income per destination and per bed type (take into consideration the full price computed in column G). If management wishes to discontinue sales to one destination based solely on the income data, which destination would you recommend to discontinue? Give your reasoning in the range J3:L5 of the PivotTable sheet. Name this sheet Total_Income. (0.5 Points) 6. On a new sheet, build a PivotTable which shows the average income per Salesperson per destination. Who is the salesperson who generated the highest average income for sales to Rome? Name this sheet Average_Income_Salesperson. (0.5 Points) 7. Karake, Inc. is interested in finding out what percent of all orders to different destinations are expedited shipping orders (not regular). Use the PivotTable application to answer this question. Name the new sheet Shipping_Expedited. (0.5 Points) 8. Liezel Ayomide, a member of the top-level management team, wants to identify which products have the highest and the lowest demand. She wishes to look at demand in the Spring months only (April, May and June). Using the PivotTable application, find the demand per destination and bed type for the Spring months. Which product had the highest Spring demand? Which product had the lowest Spring demand? Name this sheet Spring_Demand. (0.5 Points) 9. Karake, Inc. wants to identify every single customer ordering an ErgoKing bed from New York City in the Spring season. Create this table and name the sheet ErgoKing_NYC_Spring. (0.5 Points) 10. The Marketing Team wants you to find the average income per month. Create a pivot table to show this and create a PivotChart for the data. Identify the most applicable type of a chart to use. Name this sheet Average_Income_Chart. (0.75 Points). 11. It is expected that total income is going to increase by 6 percent per destination in the year 2021. On a new sheet named Expected_2021_Income, use the Calculated Field command of the Pivot Table application to compute the expected income per destination in 2021. (0.75 Point) 12. On a new sheet named Growth_Rate_changes, create a one-way table to assess the change of growth in 2021 for Baltimore City by varying the growth rate from 5% to 15% in an increment of 1%. Create a name sticker for cell C1 (the cell that contains the Expected Income Variable). (1.5 Points) Late Policy: Your work is due by 11:59 PM on April 11th. Any late submission will be penalized based on the flowing schedule: - less than 2 hours late: 5% deduction - less than 5 hours late: 10% deduction - less than 24 hours late: 50 % deduction. (No work is accepted after 24 hours of the due date. These are unprecedented times. Please use you time productively and wisely.) IMPORTANT: IT IS YOUR RESPONSIBILITY TO ENSURE THE FILE UPLOADED IS THE CORRECT ONE. IF AN INCORRECT FILE IS UPLOADED, THE LATE SUBMISSION POLICY ABOVE WILL BE APPLIED.

Excel Project- Pivot Tables, Functions, Etc.

Related Study Services

Post your Answer - free or at a fee

Answer Review

Excel Project- Pivot Tables, Functions, Etc.

Related Study Services

Post your Answer - free or at a fee

Login to your tutor account to post an answer