New Perspectives Excel 365 | Module 3: SAM Project A | Pro Gear скачать в хорошем качестве

New Perspectives Excel 365 | Module 3: SAM Project A | Pro Gear

New Perspectives Excel 365 | Module 3: SAM Project A | Pro Gear #newperspectives If you directly want to get the project from us then contact us on our Whatsapp. Link is given here, Whatsapp Contact Link: https://api.whatsapp.com/message/4B6N... Whatsapp Number: +919116641093 +918005564456 Gmail Id: [email protected] We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English. PROJECT STEPS 1. Latrice Ramsey is a financial analyst for Pro Gear in Jersey City, New Jersey. The company manufactures equipment and clothing for professional sports teams, and Latrice is analyzing its shipping and sales data in an Excel workbook. She asks for your help in completing the analysis. Go to the Shipping Analysis worksheet. Before you can complete the summary information for Latrice, you need to perform calculations in other parts of the worksheet. Start by determining the number of days in transit for each order as follows: a. In cell F10, insert a formula without using a function that subtracts the shipping date (cell D10) from the delivery date (cell E10). b. Use the Fill Handle to fill the range F11:F36 with the formula in cell F10. 2. If the shipper completed the delivery in two days or less, they can bill Pro Gear a surcharge of $14.00 per shipment. Calculate the total shipping charge as follows: a. In cell H10, insert a formula using the IF function that tests whether the number of days in transit (cell F10) is less than or equal to 2. b. If the number of days in transit is less than or equal to 2, add the standard shipping charge (cell G10) to 14. c. If the number of days in transit is greater than 2, display the standard shipping charge (cell G10). d. Use the Fill Handle to fill the range H11:H36 with the formula in cell H10. 3. Now you can complete the summary in the range A4:H7. In cell C5, insert a formula using the COUNTA function that counts the unique city names (the spill range J10#) to determine the number of cities receiving shipments. 4. In cell C6, enter a formula using the ROUNDUP and AVERAGE functions that averages the number of days in transit (the range F10:F36) and rounds the result to 0 decimal places to display a whole number of days. 5. In cell C7, enter a formula using the MAX function that identifies the most days a shipment spent in transit (the range F10:F36). 6. In cell G5, enter a formula using the MIN function that identifies the fewest days a shipment spent in transit (the range F10:F36). 7. Latrice wants to identify a shipper that delivered a shipment in the minimum number of days. Create a formula that provides this information as follows: a. In cell G6, begin to enter a formula using the XLOOKUP function. b. Use the minimum number of days in transit (cell G5) as the lookup value. c. Use the Days in Transit (the range F10:F36) as the lookup_array. d. Use the Shipper names (the range B10:B36) as the return_array. e. Display "Not found" if the lookup value is not found. f. Specify an exact match (0) as the match_mode. g. Use the default for the search_mode so Excel searches from the top of the lookup_array. 8. Go to the Sales Analysis worksheet. In cell B2, use the TODAY function to insert the current date as the report date. 9. Use the Quick Analysis tool to sum the quarterly sales revenue for each product category and the annual total (the range B5:E9) and display the results in the range F5:F9. [Mac Hint: In cell F5, insert a formula using the SUM function to total the footwear sales for each quarter (the range B5:E5), and then fill the range F6:F9 with the formula in cell F5, filling without formatting. Bold the range F5:F8 to make the totals stand out.] 10. Latrice wants to calculate the percentage each product category contributed to the total sales. She also wants to avoid a divide by zero error in the calculation. Complete the formula as follows: a. In cell G5, enter a formula using the IFERROR function that divides the amount of footwear sales for the year (cell F5) by the total sales (cell F9) and displays the text "Error" if the result is an error. b. Use an absolute reference to cell F9 in the formula. c. Use the Fill Handle to fill the range G6:G9 with the formula in cell G5, filling without formatting.