Illustrated Excel 2019 | Module 11: SAM Project 1a SmartEnergy Services скачать в хорошем качестве

Illustrated Excel 2019 | Module 11: SAM Project 1a SmartEnergy Services

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 Gmail Id: singhal.agrawal.bharati@gmail.com 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. Illustrated Excel 2019 | Module 11: SAM Project 1a SmartEnergy Services ANALYZE DATA AND CORRECT FORMULAS PROJECT STEPS Darius Beckman is the service manager for SmartEnergy Services in Tampa, Florida. The company provides electrical energy equipment and services to cities and other organizations. In an Excel workbook, Darius is tracking the service contracts he manages in Tampa and Orlando. He asks for your help in analyzing the contract data and correcting errors. Go to the Tampa worksheet. Correct the errors in the worksheet as follows: In cell G6, trace the errors in the formula to identify the cause of the error message. Correct the formula so that the IF function tests whether the contract date (the range named Contract_Tampa) is less than the date in cell B3. If it is, multiply the standard price (cell F6) by the sale price rate (cell G3) and return the result. If it is not, return the standard price (cell F6) in cell G6. Copy the formula in cell G6 into the range G7:G14 to fix the errors in that range. In cell G15, use Error Checking to find the formula error, and then correct the formula. Correct the formula error in cell F15. After entering the equipment code data in the range D6:D14, Darius applied data validation to the range to make sure he and others entered the correct codes. Check for data-entry errors and correct them as follows: Circle invalid data in the worksheet. Change the circled value to 104 to use the correct equipment code. In the range A18:B23, Darius has created an area for analyzing the service agreements for specific types of equipment. He wants to analyze the sales of substation agreements because they are the most expensive. Create formulas to analyze this data as follows: In cell B20, enter a formula using the COUNTIF function to count the number of substation agreements sold. Use the list of equipment codes (range D6:D14) as the range and the code for substations (104) as the criteria in your formula. In cell B21, enter a formula using the AVERAGEIF function to find the average sale price of a substation agreement. Use the list of equipment codes as the range, the code for substations as the criteria, and the sale prices (range G6:G14) as the range to average in your formula. In cell B22, use the SUMIF function to find the total sale price of substation agreements. Use the list of equipment codes as the range, the code for substations as the criteria, and the sale prices as the range to sum in your formula. In cell B23, Darius wants to display the profit margin for substations, which is contained in another worksheet. Create a formula using an external reference as follows to include the profit margin: Open the workbook Support_EX19_11a_Substations.xlsx. In cell B23 of the Tampa worksheet, insert a formula that references cell B6 in the Support_EX19_11a_Substations.xlsx workbook. Close the Support_EX19_11a_Substations.xlsx workbook. Darius has defined a name for cell B23, but wants to change it to one more similar to the text in cell A23. Edit the defined name for cell B23 to use Profit_Margin as the name. [Mac Hint: Delete the old defined name and create a new defined name.] Go to the Orlando worksheet. In the range E6:E14, Darius needs to enter a formula to calculate the expiration dates for the service agreements, which expire after one year. He wants to use a defined name in the formula as he did on the Tampa worksheet. Calculate the expiration dates as follows: Examine the formula in cell G6, which uses a defined name to calculate the sale price. Assign the same defined name (Contract_Orlando) to the range C6:C14, which resolves the errors in column G. In cell E6, enter a formula without using a function to determine the expiration date by adding 365 to the defined name Contract_Orlando. Fill the range E7:E14 with the formula in cell E6. The workbook contains a defined name Darius no longer needs. Delete the defined name invoice_number (but not the data) from the workbook. In the range I5:L10, Darius wants to list data about new Orlando customers who have not signed a contract yet. On a separate worksheet named New Clients, he imported the data, but it di #samproject1a #samprojects #cengageassignments #IL_EX19_11a #IL_EXCEL #excel_assignments #excel_sam_projects #Module11SamProjecta #smartenergy_services #illustrated_excel #IL_EX19_11a_FirstLastName_1.xlsx #IL_EX19_11a_FirstLastName_2.xlsx