newperspective 365 excel module 3 sam project B скачать в хорошем качестве

newperspective 365 excel module 3 sam project B

Please contact us if you need this project or any other project in any subject to be completed. Whatsapp: +1-343-321-3276 Email: essayserviceusa@gmail.com WhatsApp Link: https://wa.me/13433213276 #newperspectivesexcel365 #module3samprojectb #TucsonKitchenSupplies New Perspectives Excel 365 | Module 3: SAM Project B Tucson Kitchen Supplies PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS PROJECT STEPS 1. Adrian Wong is a financial analyst for Tucson Kitchen Supplies in Tucson, Arizona. The company manufactures high quality kitchen equipment for professional chefs and bakers, and Adrian is analyzing its shipping and sales data in an Excel workbook. He asks for your help in completing the analysis. Go to the Shipping Analysis worksheet. Before you can complete the summary information for Adrian, 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 three days or less, they can bill Tucson Kitchen Supplies a surcharge of $11.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 3. b. If the number of days in transit is less than or equal to 3, add the standard shipping charge (cell G10) to 11. c. If the number of days in transit is greater than 3, 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. Adrian 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 cookware sales for each quarter (the range B5:E5), and then fill the range F6:F9 with the formula in cell F5, filling without formatting.] 10. Adrian wants to calculate the percentage each product category contributed to the total sales. He 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 cookware 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. 11. Adrian set a goal for the current year of increasing sales revenue by 3% percent over last year. Enter the revenue goals as follows: a. In cell I5, enter a formula without using a function that multiplies the cookware revenue for the previous year (cell H5) by the target increase percentage (cell B11), and then adds the result to the cookware revenue for the previous year (cell H5). b. Use an absolute reference to cell B11 in the formula. c. Use the Fill Handle to fill the range I6:I9 with the formula in cell I5, filling without formatting.