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.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Our Services
Ace Writing Center has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.
Essays
At Ace Writing Center, Nowadays, students normally have extremely busy schedules. You will note that some of them have to take on some evening or weekend jobs in order to get some income that can help them to sustain in college or in the university. This can deny them a chance to write all the essays given. Others usually get bombarded with a lot of work by their lecturers. This can still delay such students from working on all their essays. However, some of them usually try to work on all these essays but end up delivering their work late. This can prevent them from graduating since most lecturers are strict on deadlines. If you want to write a business essay, the wise way is to hire an outstanding essay writing service like us, so that you can get the best results. If you are keen, you will note that many companies usually overcharge their customers. Some of them are there only to make money. And in reality, they really don’t care to build a long term commitment with students. You should not choose such companies. You should take your time and choose a reliable company to work with. Ace Writing Center is the ultimate solution for you. We have been offering our writing service for more than 7 years. This is a clear indication that you will get quality essay writing service. We have a wide range of writers who can work on any business essay that you might have. We believe in doing extensive research so that we can provide quality work to all our clients. .
Admissions
Admission and Business Papers
Have you ever had to write an admission essay for college? The majority of students face the same issues when applying to a university or college and many in such situations decide they need professional help to cope with this matter. They get in a situation when the deadline keeps coming closer but lack motivation to start because they are just not sure if their writing skills are strong enough. We have a solution for you! Ace Writing Center is the best admission essay writing service with a large professional team and years of experience in providing high-quality papers to students of all levels and faculties. The mission of our team is to help students make their dreams of entering a good college come true and that’s what we offer!.
Editing
Editing and Proofreading
Sometimes all the words for your paper just flow out of your mind and into your fingers. You type quickly at your keyboard and there they are, your beautiful words right there on the screen. But you have no idea how to polish it up. You may be wishing there was a paper writing service that offered this type of writing service. Look no more! Here at Ace Writing Center, we offer you an editing and proofreading option that you can't find anywhere else..
Coursework
College Essay Writing
In case you are familiar Ace Writing Center, you know the way to distinguish a better company from a cheap one exactly. First of all, poor service website does not have a sufficient support. We think support team is an essential part of success; it has to answer all clients’ questions and be a connecting link between clients and their writers. On our web-service you will get answers about anything you need and your writer will receive all your instructions, assignments and requirements exactly and swiftly. A writing service that we run has got a flexible pricing system that will save you from senseless wastes and many bonus systems that let you sparing money for something important for you.