Get the “SalesCoData.xlsx” file and complete the exercises as indicated in these instructions. • Save the Excel file as “LastName-Final.xlsx.” (2) • Open the Excel file in the “Documentation” sheet, enter your name and date. (2) The Case: T5 Warehouse is an online retailer that sells gourmet products online. Customers order products online, agents process the orders, and T5 ships the products all over the USA. T5 also runs promotions that provide discounts for some products. All products incur shipping costs. Some customers charge to their account, and others pay for the items at order time. As a consultant, you must prepare an analysis of the sales for the last three years and provide some insights and recommendations. In addition, the consultant must provide some sales statistics, formulas for product packing, car loans, and financial investment options. PROJECT STEPS 1. Familiarize yourself with the range names in the “WorkTables” sheet. There are five cell ranges previously named as indicated below: Promo – lists the percentage discount based on the promotion code Tax – lists the percentage tax rate based on the customer’s state. Overdue – lists the groups based on the number of days overdue Interest – lists the annual interest rate based on the number of years of the loan WorkDate – contains the “system” date used to compute the numbers of days overdue. 2. The “Data” sheet contains order line-item sales data. You must complete the spreadsheet by entering all the required formulas in the required columns. Ensure you use formulas with absolute, range names, or relative addresses when needed. See the sample output below. (70 max) Instructions 200pts Page 2 of 7 3. In column P, enter the formula to compute the line subtotal (LineSubtot). The formula multiplies the UnitPrice and Quantity values. Apply the Currency format to the column. (5) 4. Copy the formula down to all rows. (5) 5. In column S, enter the formula to compute the discount percentage (DiscPerc). To compute the discount percentage, you must check if the item has a promotion (column Q). If the length of the value in column Q is greater than 0 (meaning there is a promotion), then you must get the percentage discount from the table Promo – must use an exact match. Otherwise, return 0. Apply the % format to the column. Hint: Use IF(), LEN(), and VLOOKUP functions. (5) 6. Copy the formula down to all rows. (5) 7. In column T, enter the formula to compute the discount amount (DiscountAmt) value for the line item. To calculate the discount amount multiply the LineSubtot (column P) by DiscPerc (column S). Apply the Currency format to the column. (5) 8. Copy the formula down to all rows. (5) 9. In column U, enter a formula to compute the total Tax amount for the line item. The tax applies to the line subtotal minus the discount. Use the VLOOKUP function to get the tax rate from the table Tax, using the customer’s State (column F) as the lookup value. Apply the Currency format to the column. (5) 10. Copy the formula down to all rows. (5) 11. In column V, enter the formula to compute the LineTotal. The line total should add up the subtotal, freight, tax values and subtract the discount amount. Apply the Currency format to the column. (5) 12. Copy the formula down to all rows. (5) 13. In column X, use a formula to compute the DaysDue for the line item. The Paid (column W) has a 1 if the line item is paid or 0 if not paid. If the line item has been paid, then the DaysDue is 0; otherwise, compute the DaysDue by subtracting the OrderDate (column B) from the WorkDate. Hint: Use IF() (5) 14. Copy the formula down to all rows. (5) 15. In column Y, use a formula to compute the OrderStatus. If the Paid value is 1 (column W), the OrderStatus is “OK.” If not, if Paid is 0 then, then lookup DaysDue (column X) in the Overdue table to get the OrderStatus. Hint: Use IF(), or IFS() and VLOOKUP() (5) 16. Copy the formula down to all rows. (5) Use these links: LEN(), VLOOKUP(), IF(LEN(),VLOOKUP()) to learn about the functions you need. Instructions 200pts Page 3 of 7 17. Management wants to analyze product sales. Your job is to generate a list of sales by product and a chart. Create a Pivot Table, based on the “Data” sheet, in a New Sheet and label it “PVT-SalesByProduct.” Use the following settings for the pivot table: (14 max.) a. Using Pivot Table Fields, add ProducName in the “Rows” section (2) b. In the “Values” section, add: i. Sum of LineTotal: (2) 1. Custom name: Sum of Orders (2) 2. Format Accounting with 2 decimals (2) c. Sort “Sum of Order” column, Smallest to Largest (2) d. Add the title “Sales By Product” in cell A1, use the Style Title style (2) e. Make column A1 width AutoFit (2) 18. Create a pivot chart graph. (12 max) a. Use graph style Bar, Clustered Bar (2) b. Chart Style 5 and do not display legends (2) c. Resize the graph to position cell C3 to cell N30 (2) d. Change the title to read “Total Sales by Product” (2) 19. Move the “PVT-SalesbyProduct” sheet after the “Data” sheet. (2) 20. Make sure your output matches the sample below. 21. Reflection: In Cell C1 provide a notable business finding from the PVT-SalesByProduct analysis. (2) Instructions 200pts Page 4 of 7 22. Next, you are asked to analyze sales by year. You decide to create a Pivot Table, based on the “Data,” in a New Sheet and label it “PVT-SalesByYear.” Use the following settings for the pivot table: (22 max) a. Using Pivot Table Fields, add OrderDate in the “Rows” section (2) i. Group by Months and Years (2) ii. Change Cell A3 to read “Years” (2) b. In the “Values” section, add: i. Sum of LineTotal: (2) 1. Custom name: Total Sales (2) 2. Format Accounting 2 decimals (2) ii. Sum of Tax: (2) 1. Custom name: Total Tax (2) 2. Format Accounting with 2 decimals (2) c. Set the Report Layout to Compact form and Subtotals to Do not show subtotals (2) d. Add the title “Sales by Year” in cell A1, Style Title (2) 23. Create a pivot chart graph. (22 max) * Must use Excel Windows version to create Combo Style Chart a. Style Combo (2) b. Total Sales series, clustered column (2) c. Total Tax series, line with markers and secondary axes (2) i. Add secondary axis for Total Tax, format axis to set Bounds Maximum value to 25,000 (4) d. Change title to “Sales By Year” (2) e. Set Legends to go on Top (2) f. Chart Style 4 (2) g. Resize the graph to position cell D3 to cell K22 (2) 24. Move the “PVT-SalesByYear” sheet after the “PVT-SalesByProduct” sheet (2) 25. Make sure your output matches the sample below. 26. Reflection: In Cell D1 provide a notable business finding from the PVT-SalesByYear analysis. (2) Use this link: PivotTable to learn about the function you need. V2105 INFS Instructions 200pts Page 5 of 7 27. Next, you are asked to analyze the Orders Balance Status. Management is concerned about the amount of orders overdue, and they want to get a clearer picture of the situation. You decide to create a Pivot Table based on the “Data” worksheet in a New Sheet and label it “PVT-OrderStatus.” Use the following settings for the pivot table: (12 max) a. Using Pivot Table Fields, add OrderStatus in the “Rows” section (2) b. Change Cell A3 to read “Late Status” (2) c. In the “Values” section, add: i. Sum of LineTotal: (2) 1. Custom name: Total of Orders (2) 2. Format Accounting 2 decimals (2) d. Add the title “Order Status” in cell A1, Style Title (2) 28. Create a pivot chart graph. (18 max) a. Style Pie (2) b. Change title to “Order Status” (2) c. Set Legends to go on Right (2) d. Data labels should show be Inside End and show only Percentage (4) e. Chart Style 8 (2) f. Resize the graph to position cell D2 to cell J20 (2) 29. Move the “PVT-OrderStatus” sheet after the “PVT-SalesByYear” sheet (2) 30. Make sure your output matches the sample below. 31. Reflection: In Cell D1 provide a notable business finding from the PVT-OrderStatus analysis. (2) V2105 IInstructions 200pts Page 6 of 7 Analysis In the “Analysis” sheet, the consultant must provide some sales statistics, formulas for product packing, car loans, and financial investment options. The cells with dim light yellow backgrounds represent input parameters based on the problem’s descriptions. (26 max) 32. Sales Analysis. Enter the corresponding Excel formulas (in cells B4, B5, B6, and B7) to compute the appropriate values using the LineTotal column from the “Data” sheet, column V. (2×4= 8) 33. Packing Options for Queso Cabrales. Enter the formulas required using the template shown. You want to find out the number of boxes needed to ship X number of Queso units. The variables are the number of slots available in a box, the number of units of Queso per ice pack, and the number of Queso units to ship. In cells B14 and B15, enter the formulas required to compute the number of Ice Packs needed and the actual number of boxes needed assuming the following: 9 slots per box, 10 units of Queso to ship, and 1 Ice Pack per every 2 Queso units. Test your formulas by changing the number of Queso units to confirm they provide the right answer- the results should be whole numbers, not fractions (i.e., 2.5.) Hint: Use the ROUND() and/or ROUNDUP() functions. (2+2= 4) Box Visualization 34. Car Loan Payment Calculation Problem. Compute the Monthly Loan Payment using the template shown. You want to buy a 2019 Toyota Highlander, priced at 47,605. You have a down payment of $5,000. You want the balance to be in a 5-year loan. In B22, enter the VLOOKUP formula to get the annual interest rate using the table INTEREST range name (“Work Tables” sheet.) In cell B23, enter a formula to compute the monthly interest rate. In cell B24, enter a formula to compute the total number of payments. In cell B25, enter PMT() function to compute the monthly loan payment amount. (2+2+2+2= 8) 35. Calculate monthly payment with a goal. You want to figure out how much you need to deposit monthly in an account with $400,000, so in 15 years, it reaches $1,000,000 (saving goal.) Using the template shown. In cell B31, enter the current balance in the account (negative value). In cell B32, enter the savings goal. In cell B33, enter the interest rate, in this case, is 3%. In cell B34, enter the number of years. In cell B35, enter the formula to compute the total number of monthly payments. In cell B36, enter the formula to calculate the monthly interest rate. In cell B37, enter the function to calculate the monthly deposit. Hint: use the PMT function, read the description and use the first four parameters. (2+2+2=6) 36. See the sample below.