question archive 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:19.99 Bought3

Illustrated Excel 2019 | Module 11: SAM Project 1a

SmartEnergy Services

ANALYZE DATA AND CORRECT FORMULAS

- GETTING STARTED

- Open the file
**IL_EX19_11a_**, available for download from the SAM website.*FirstLastName*_1.xlsx - Save the file as
**IL_EX19_11a_**by changing the “1” to a “2”.*FirstLastName*_2.xlsx- 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.

- If you do not see the
- 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_**still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.*FirstLastName*_2.xlsx- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**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 B20, enter a formula using the
- 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.

- Open the 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 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:- 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. - Fill the range I7:I10 with the formula in cell I6 to show the company names.

- In cell I6 on the
- 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:- 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. - Fill the range J7:J10 with the formula in cell J6 to list the full names of the remaining contacts.

- In cell J6, enter a formula using the
- List the number of years in business and the state where the company is located using worksheet references as follows:
- 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. - Fill the range K7:K10 with the formula in cell K6.
- 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. - Fill the range L7:L10 with the formula in cell L6.

- In cell K6, enter a formula using the
- 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* - 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:- Enter the text
**Foley, Dean**in cell D5. - 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.) - Enter the invoice number
**2101**in cell B5. - 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.) - Delete column C, which is no longer necessary.

- Enter the text
- 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:- In cell F17, add the
**IFERROR**function to the formula. - If dividing the net profit (cell
**F16**) by the total income (cell**F5**) results in an error, display**"Missing total"**as an error message. - Copy the formula in cell F17 to the range B17:E17.
- In cell F5, enter a formula using the
**SUM**function to total the Q1–Q4 income amounts (range**B5:E5**).

- In cell F17, add the
- 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:- 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. - 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. - 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.

- First, determine the income needed in Quarters 2 and 3 to achieve an income of $7,500 in Quarter 4.
- 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:- Create a custom list by importing the range
**A4:A17**. - Enter the text
**Income**in cell H4, and then use the new AutoList to fill cells H5:H17.

- Create a custom list by importing the range
- 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. - Calculate the monthly payment for Scenario 1 as follows:
- In cell B9, enter a formula using the
**PMT**function to determine the monthly payment. - 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.

- In cell B9, enter a formula using the
- 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:- In cell C5, enter a formula using the
**PV**function to determine the loan amount. - 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.

- In cell C5, enter a formula using the
- 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:- In cell D10, enter a formula using the
**FV**function to determine the future value. - 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.

- In cell D10, enter a formula using the

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

IL_EX19_11a_JenniferKim_1.xlsx

Purchased 3 times