ExcelExam Final Asap

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 1 of 8

Get the "SalesCoData.xlsx" file and complete the exercises as indicated in these instructions.

Save your file as LastName-Final.xlsx". Submit your file by the deadline. (2)

The Case: T5 Warehouse is an online retailer that sales 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. You have been asked to prepare an analysis of the sales for the last

three years.

1. In the “Documentation” sheet, enter your name and date, format the date as a short date. (2) 2. 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 state of the customer

Overdue – lists the overdue group 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.

3. The “Data” sheet contains order line-item sales data. You are tasked with completing the spreadsheet by entering all the required formulas in the required columns. Make sure to use the proper formulas with

absolute (or range names) and relative address ranges. See sample output below. (70 max)

4. 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)

5. Copy the formula down to all rows. (5) 6. 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)

7. Copy the formula down to all rows. (5) 8. In column T, enter the formula to compute the discount amount value for the line item. The discount amount

is computed using the LineSubtot (column P) and discount percentage (column S). Apply the Currency

format to the column. (5)

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 2 of 8

9. Copy the formula down to all rows. (5) 10. 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)

11. Copy the formula down to all rows. (5) 12. 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)

13. Copy the formula down to all rows. (5) 14. 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 was 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. (5)

15. Copy the formula down to all rows. (5) 16. In column Y, use a formula to compute the OrderStatus. The OrderStatus is OK if the line item has been

Paid (column W). If Paid is 0 then, then lookup DaysDue (column X) in the Overdue table to get the

OrderStatus. (5)

17. Copy the formula down to all rows. (5)

18. Now that you have completed the calculations, the company wants to get a summary of sales figures by salesperson to compare their productivity. To do this you decide to use the subtotals feature in Excel.

Create a subtotals page (20 points)

a. Copy the “Data” sheet as a new sheet and rename it “Subtotals”. (2) b. Move the “Subtotals” sheet after the “Data” sheet. (2) c. Sort the data in the new Subtotals sheet by “Salesperson”, in ascending order (2) d. Use the Subtotals Excel option to create subtotals by Salesperson with Sum totals for LineSubtot,

Freight, DiscountAmt, Tax and LineTotal columns. (6)

e. Hide columns A through G, I through O, Q, S, W through Y. (2) f. Autofit all other columns to show totals correctly (2)

19. Using the left margin level numbers [1][2][3], set the report to level 2 only. (2)

20. Reflection: In cell AA1: describe an interesting finding from the Subtotals analysis. (2)

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 3 of 8

21. Management wants to analyze product sales. You are tasked 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. 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, Style Title (2) e. Make column A1 width AutoFit (2)

22. Create a pivot chart graph. (12 max) a. Style Bar Clustered Bar (2) b. Chart Style 5 (2) c. Resize the graph to position cell C3 to cell N30 (2) d. Change title to read “Total Sales by Product” (2)

23. Move the “PVT-SalesbyProduct” sheet after the “Subtotals” sheet. (2) 24. Make sure your output matches the sample below.

25. Reflection: In Cell C1 describe an interesting finding from the PVT-SalesByProduct analysis. (2)

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 4 of 8

26. 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. 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 Show in Outline form (2) d. Add the title “Sales by Year” in cell A1, Style Title (2)

27. Create a pivot chart graph. (18 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 (2) 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)

28. Move the “PVT-SalesByYear” sheet after the “PVT-SalesByProduct” sheet (2) 29. Make sure your output matches the sample below.

30. Reflection: In Cell D1 describe an interesting finding from the PVT-SalesByYear analysis. (2)

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 5 of 8

31. 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”, in a New Sheet and label it “PVT-OrderStatus”. Use the following settings for the

pivot table: (12 max)

a. 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 Balance Status” in cell A1, Style Title (2) 32. Create a pivot chart graph. (18 max)

a. Style Pie (2) b. Change title to “Order Balance 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)

33. Move the “PVT-OrderStatus” sheet after the “PVT-SalesByYear” sheet (2) 34. Make sure your output matches the sample below.

35. Reflection: In Cell D1 describe an interesting finding from the PVT-OrderStatus analysis. (2)

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 6 of 8

36. Next, you are asked to analyze the Paid vs Unpaid Orders. 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”, in a New Sheet and label it “PVT-PaidUnpaid”. Use the following settings for the

pivot table: (18 max)

a. OrderDate in the “Rows” section (2) i. Group by Months and Years (2)

ii. Make sure Cell A4 reads “Dates” (2) b. In the Columns, add the Paid field (2) c. In the Values section add:

i. Sum of LineTotal: (2) 1. Format Accounting 2 decimals (2)

d. Add the title “Paid vs UnPaid” in cell A1, Style Title (2) e. In A32 add the label “Average” and compute the average for both Paid and Unpaid columns (2) f. In A33 add the label “Percentage” and compute the percentage of paid and unpaid columns (2)

37. Create a pivot chart graph. (14 max) a. Style Column Clustered Column (2) b. Change title to “Paid vs UnPaid” (2) c. Set Legends to go on Bottom (2) d. Chart Style 6 (2) e. Resize the graph to position cell F3 to cell O26 (2)

38. Move the “PVT-PaidUnpaid” sheet after the “PVT-OrderStatus” sheet (2) 39. Make sure your output matches the sample below.

40. Reflection: In Cell F1 describe an interesting finding from the PVT-PaidUnpaid analysis. (2)

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 7 of 8

Analysis The following exercises are located in the “Analysis” sheet. The cells with shaded light yellow background

represent input values deducted from the problems’ descriptions. (28 max)

41. Sales Analysis. Enter the corresponding Excel formulas (in cells B4, B5, B6, B7, and B8) to compute the appropriate values using the LineTotal column from the “Data” sheet. Note that for the Percentage of

UnPaid Sales, you must refer to the cells in the PVT-PaidUnpaid sheet. (2x5= 10)

42. Packing Options for Queso Cabrales. Enter the formulas required using the template shown. Basically, 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 number of Queso units to

ship. In cells, B15 and B16 enter the formulas required to compute the number of Ice Packs needed and 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. Use the ROUND() function. (2+2= 4)

43. Car Loan Payment Calculation Problem. Compute the Monthly Loan Payment using the template shown. You want to buy a 2019 Toyota Highlander that is priced at 47,605. You have a down payment of $5,000.

You want the balance to be in a 5 year loan. In B23, enter VLOOKUP formula to get the annual interest rate

using the table INTEREST range name (“Work Tables” sheet.) In cell B24 enter formula to compute the

monthly interest rate. In cell B25 enter formula to compute the total number of payments. In cell B26 enter

PMT() function to compute the monthly loan payment amount. (2+2+2+2= 8)

44. Calculating a periodic 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 dollars (saving goal). Using the

template shown. In cell B32 enter the current balance in the account (in negative). In cell B33 enter the

savings goal. In cell B34 enter the interest rate, in this case is 3%. In cell B35 enter the number of years. In

cell B36 enter the formula to compute the total number of monthly payments. In cell B37 enter the formula

to calculate the monthly interest rate. In cell B38 enter the function to calculate the monthly deposit. Hint:

use the PMT function. (2+2+2=6)

45. See sample below.

V2109 INFS 2200 T5-Final Exam Instructions 250pts

Page 8 of 8