Illustrated Excel 2019 | Module 11: SAM Project 1a SmartEnergy Services ANALYZE DATA AND CORRECT FORMULAS     GETTING STARTED Open the file IL_EX19_11a_FirstLastName_1

Subject:MS ExcelPrice:20.01 Bought10

Share With

Illustrated Excel 2019 | Module 11: SAM Project 1a

SmartEnergy Services

ANALYZE DATA AND CORRECT FORMULAS

• GETTING STARTED

• Open the file IL_EX19_11a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_11a_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_11a_Substations.xlsx
• With the file IL_EX19_11a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. 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:
   1. 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.
   2. Copy the formula in cell G6 into the range G7:G14 to fix the errors in that range.
   3. In cell G15, use Error Checking to find the formula error, and then correct the formula.
   4. Correct the formula error in cell F15.
2. 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:
   1. Circle invalid data in the worksheet.
   2. Change the circled value to 104 to use the correct equipment code.
3. 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:
   1. 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.
   2. 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.
   3. 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.
4. 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:
   1. Open the workbook Support_EX19_11a_Substations.xlsx.
   2. In cell B23 of the Tampa worksheet, insert a formula that references cell B6 in the Support_EX19_11a_Substations.xlsx workbook.
   3. Close the Support_EX19_11a_Substations.xlsx workbook.
5. 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.]
6. 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:
   1. Examine the formula in cell G6, which uses a defined name to calculate the sale price.
   2. Assign the same defined name (Contract_Orlando) to the range C6:C14, which resolves the errors in column G.
   3. In cell E6, enter a formula without using a function to determine the expiration date by adding 365 to the defined name Contract_Orlando.
   4. Fill the range E7:E14 with the formula in cell E6.
7. The workbook contains a defined name Darius no longer needs.
   Delete the defined name invoice_number (but not the data) from the workbook.
8. 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 did not appear in the correct format. For example, the company names appear in all lowercase, though each name should begin with an uppercase letter.
   Examine the imported data on the New Clients worksheet, and then return to the Orlando worksheet to incorporate the imported company name data in the range I6:I10 using worksheet references as follows:
   1. In cell I6 on the Orlando worksheet, enter a formula using the PROPER function to capitalize the first letter in each word in the Company text in cell B3 of the New Clients worksheet.
   2. Fill the range I7:I10 with the formula in cell I6 to show the company names.
9. The imported contact name data separates the first and last names, but Darius wants to list the full name in one cell.
   List the first and last names of each contact in a single cell using worksheet references as follows:
   1. In cell J6, enter a formula using the CONCAT function that displays the first name shown in cell C3 of the New Clients worksheet followed by a space (" "), and then the last name shown in cell D3 of the New Clients worksheet.
   2. Fill the range J7:J10 with the formula in cell J6 to list the full names of the remaining contacts.
10. List the number of years in business and the state where the company is located using worksheet references as follows:
    1. In cell K6, enter a formula using the LEFT function to insert the first 2 characters on the left of cell E3 of the New Clients worksheet.
    2. Fill the range K7:K10 with the formula in cell K6.
    3. In cell L6, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell F3 of the New Clients worksheet.
    4. Fill the range L7:L10 with the formula in cell L6.
11. Darius asks you to calculate the total service agreement sales for both offices.
    Go to the Summary worksheet. In cell A4, create a formula using the SUM function and 3-D references to total cell G15 on the Tampa worksheet and cell G15 on the Orlando worksheet.
12. Next, Darius wants to separate the customer names from their invoice numbers and format the names with the last name first.
    Modify the data on the Summary worksheet as follows:
    1. Enter the text Foley, Dean in cell D5.
    2. Select the range D5:D22, and then use the Flash Fill command to automatically enter names into the remaining cells in the range. (Hint: You must use the Flash Fill command to receive credit for this step.)
    3. Enter the invoice number 2101 in cell B5.
    4. Select the range B5:B22, and then use the Flash Fill command to automatically enter invoice numbers into the remaining cells in the range. (Hint: You must use the Flash Fill command to receive credit for this step.)
    5. Delete column C, which is no longer necessary.
13. Go to the Projections worksheet, which includes the income and expenses for the current year. Darius also wants to include projections for next year. First, he asks you to correct the error on the worksheet.
    Trace the error in cell F17, which divides the net profit in cell F16 by the total income in cell F5. The formula is correct, but cell F5 needs a value to prevent the error. Correct the error as follows:
    1. In cell F17, add the IFERROR function to the formula.
    2. If dividing the net profit (cell F16) by the total income (cell F5) results in an error, display "Missing total" as an error message.
    3. Copy the formula in cell F17 to the range B17:E17.
    4. In cell F5, enter a formula using the SUM function to total the Q1–Q4 income amounts (range B5:E5).
14. Darius thinks the Tampa and Orlando offices have a good chance of increasing income in the next year. In the range B21:E23, he wants to make three projections based on different assumptions.
    Project the quarterly income for Darius as follows:
    1. First, determine the income needed in Quarters 2 and 3 to achieve an income of $7,500 in Quarter 4.
       Project the income in the four quarters by filling the series for the first projection (range B21:E21) with a linear trend.
    2. Next, determine the income in Q2–Q4 if it increases by 2 percent each quarter, starting with an income of $6,300.
       Project the income in the four quarters by filling the series for the second projection (range B22:E22) based on a growth trend using 1.02 as the step value.
    3. Finally, extrapolate the income based on the income in Q4 of the current year and a growth rate of 3 percent.
       In cell B23, enter a formula without using a function to reference the total income in Q4 of the current year (cell E5).
       Project the income in the four quarters by filling the series for the third projection (range B23:E23) based on a growth trend using 1.03 as the step value.
15. Darius has prepared the range H1:M17 for detailed projections to complete later. For now, he asks you to enter the expense categories, which he will use in other workbooks.
    Build a custom list of categories as follows:
    1. Create a custom list by importing the range A4:A17.
    2. Enter the text Income in cell H4, and then use the new AutoList to fill cells H5:H17.
16. SmartEnergy Services is considering expanding the business to add a third Florida office. Darius asks you to help with the analysis of loan options to fund the expansion.
    Go to the Expansion worksheet, and then create defined names based on the range A5:B10, using the values in the left column only.
17. Calculate the monthly payment for Scenario 1 as follows:
    1. In cell B9, enter a formula using the PMT function to determine the monthly payment.
    2. In the formula, use the monthly interest rate (cell B7) as the rate, the term in months (cell B8) as the nper, and the loan amount (cell B5) as the pv.
18. In Scenario 2, Darius wants to make a monthly payment of $13,800 at an annual interest rate of 4.15 percent. He wants to know how much he can borrow on those terms.
    Calculate the loan amount for Darius as follows:
    1. In cell C5, enter a formula using the PV function to determine the loan amount.
    2. In the formula, use the monthly interest rate (cell C7) as the rate, the term in months (cell C8) as the nper, and the monthly payment (cell C9) as the pmt.
19. In Scenario 3, Darius wants to know the total amount spent for a loan of $750,000 at an annual interest rate of 3.95 percent and a monthly payment of $15,000.
    Calculate the future value of the loan for Darius as follows:
    1. In cell D10, enter a formula using the FV function to determine the future value.
    2. In the formula, use the monthly interest rate (cell D7) as the rate, the term in months (cell D8) as the nper, and the monthly payment (cell D9) as the pmt.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.