University of North Texas Leveraging Information Technology Project
Description
| Case1 | ||
| You are working as an IT and Data analyst with Sullivan & Co., a leading management consulting firm. RMS has hired Sullivan & Co. to advise RMS on the restructuring of its IT systems and processes. Marc Ross, partner at Sullivan & Co. has asked you to analyze the sales, inventory and profit data. Based on your analysis, you should take decision and recommend them to RMS. |
||
| Steps to Perform: | ||
| Step | Instructions | Points |
| 1 | On the Pivot_table worksheet, perform the following | |
| Using the data given in the sheet SalesData, generate a PivotTable report. The report should display Total sales amount, COGS and unit sold of orders, grouped by category and sub-category. You can perform the following steps to create the pivot table report:- |
||
| (a) | Select the data in the range A1:F3201 in sheet SalesData and create a Pivot table in the cell range A4 in the sheet Pivot_table. | 15 |
| (b) | Add Category and Sub-category to Rows. | 3 |
| (c) | Add Sales amount, Cost and Unit Sold to values area and summarize value fields by Sum function. | 5 |
| (d) | Change the subtotals and show all subtotals at the bottom of the group. To do this, click on Pivot table, click on Design, click on Subtotals and select the subtotals at the bottom of the group. (Note: this is an important step to get the reports in other sheets) |
2 |
| Compare your answer with the sample answer given in sheet Sample_Answers | ||
| 2 | On the Inventory_Analysis worksheet, perform the following | |
| The Quantity in Units and Inventory Cost per unit are given in range B4:B15 and C4:C15 respectively. | ||
| (a) | Write a formula in D4 to calculate “Quantity Dollars” based on the Quantity Units and Inventory Cost per unit. Quantity Dollars = Quantity Units * Inventory Cost Per Unit Copy the formula down through to cell D15. |
3 |
| (b) | Write a formula in D17 to compute total Quantity Dollars. Total quantity dollars is the sum of quantity dollars, computed in Step 2(a) above. |
1 |
| (c) | Write a formula in E4 to calculate “% Dollars” based on the Quantity Dollars and Total Quantity Dollars. % Dollars = Quantity Dollars / Total Quantity Dollars. In the formula, make sure that you fix the row reference of cell Total Quantity Dollars (D17). Copy the formula down through to cell E15. |
3 |
| (d) | Write a formula in F4 to calculate “Cumulative Dollars” based on the % Dollars. Cumulative Dollars = Sum of all % Dollars up to the current row. It can be done in two ways, First Approach: Write a formula in F4 and refer to cell E4. In F5 cell, write formula to add F4 and E5. Copy the formula in F5 down to F15. Second Approach: Write a smart formula to achieve this in one single formula. In cell F4, enter =SUM($E$4:E4). Copy the formula down through to cell F15. |
4 |
| (e) | Perform ABC Analysis. In ABC analysis, we classify the category in A, B or C buckets. Its based on Pareto Principle. You can read about it here : https://whatis.techtarget.com/definition/ABC-analy… (i) If Cumulative Dollars is <=80% then classify category as A (ii) If Cumulative Dollars is > 80% and <=95% then classify category as B (iii) If Cumulative Dollars is >95% then classify category as C Using IF Write a formula in cell G4 to perform ABC analysis. Copy the formula down till G15. |
10 |
| Compare your answer with the sample answer given in sheet Sample_Answers | ||
| 3 | On the Sales_Analysis worksheet, perform the following | |
| The Category total names are given in the range B4:B26. You have to use the data in Pivot_table sheet to compute Sales – Dollars values. | ||
| (a) | Using VLOOKUP function, write a formula in C4 to calculate Sales dollars for each of the category. (i) The lookup value should be B4 (ii) The table_array should be an appropriate range in Pivot_table sheet (iii) Match type should be FALSE Copy the formula down to cell C26. |
6 |
| (b) | Write a formula in J5 to compute total Sales Dollars. Total quantity dollars is the sum of Sales dollars, computed in Step 3(a) above. |
2 |
| (c) | Write a formula in D4 to calculate “% of Sales Dollars” based on the Sales – Dollars and Total Sales Dollars. % of Sales Dollars = Sales – Dollars / Total Sales Dollars. In the formula, make sure that you fix the row reference of cell Total Sales Dollars (J5). Copy the formula down through to cell D26. |
3 |
| (d) | Write a formula in E4 to calculate “Cumulative Dollars” based on the % Dollars. Cumulative Dollars = Sum of all % Dollars up to the current row. It can be done in two ways, First Approach: Write a formula in E4 and refer to cell D4. In E5 cell, write formula to add E4 and D5. Copy the formula in E5 down to E26. Second Approach: Write a smart formula to achieve this in one single formula. In cell E4, enter =SUM($D$4:D4). Copy the formula down through to cell E26. |
4 |
| (e) | Perform ABC Analysis. In ABC analysis, we classify the category in A, B or C buckets. Its based on Pareto Principle. (i) If Cumulative Dollars is <=80% then classify category as A (ii) If Cumulative Dollars is > 80% and <=95% then classify category as B (iii) If Cumulative Dollars is >95% then classify category as C Using IF Write a formula in cell F4 to perform ABC analysis. Copy the formula down till F26. |
10 |
| Compare your answer with the sample answer given in sheet Sample_Answers | ||
| 4 | On the Profit_Analysis worksheet, perform the following | |
| The Category total names are given in the range B4:B26. You have to use the data in Pivot_table sheet to compute Sales – Dollars values. | ||
| (a) | Sort the data on Sales – Dollars, from highest to lowest | 2 |
| (b) | Using VLOOKUP function, write a formula in C4 to calculate Sales dollars for each of the category. (i) The lookup value should be B4 (ii) The table_array should be an appropriate range in Pivot_table sheet (iii) Match type should be FALSE Copy the formula down to cell C26. |
7 |
| (c) | Using VLOOKUP function, write a formula in D4 to calculate Sales dollars for each of the category. (i) The lookup value should be B4 (ii) The table_array should be an appropriate range in Pivot_table sheet (iii) Match type should be FALSE Copy the formula down to cell D26. |
7 |
| (d) | Write a formula in E4 to calculate “Gross Profit” based on the Sales – Dollars and COGS. Gross Profit = Sales Dollars – COGS Copy the formula down through to cell E26. |
2 |
| (e) | Write a formula in F4 to calculate “Gross Profit Margin” based on the Gross Profit and Sales Dollars. Gross Margin = Gross Profit / Sales Dollars Copy the formula down through to cell F26. |
2 |
| (f) | Apply conditional Formatting (i) Highlight in red fill all Gross Margin values which are 100% (ii) Highlight in Light red fill with red text all Gross Margin values which are less than 50% (iii) Highlight in Light green fill with green text all Gross Margin values which are more than 50% but less than 100% |
9 |
| Compare your answer with the sample answer given in sheet Sample_Answers | ||
| 6 | Save the file and upload it | 100 |
Have a similar assignment? "Place an order for your assignment and have exceptional work written by our team of experts, guaranteeing you A results."
Complete the 
Complete the order payment after filling in the order form. We’ll receive your order and assign it to a writer.
The assigned writer will complete your paper and forward it to our Editing Team for review and approval.
Once approved by the editor, the completed paper will be uploaded to your account for you to download, review and approve.