Illustrated Excel 2019 | Module 11: SAM Project 1b Top Hits

ANALYZE DATA AND CORRECT FORMULAS

GETTING STARTED

1. Open the file IL_EX19_11b_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_11b_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. 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.

3. 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_11b_Redesign.xlsx

4. With the file IL_EX19_11b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

a. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Carla Amaya recently started a small business in Takoma Park, Maryland, specializing in search engine optimization (SEO) and social media marketing for other businesses. In an Excel workbook, Carla is tracking the projects she manages and their invoice amounts. She asks for your help in analyzing the project data and correcting errors. Go to the SEO Projects worksheet. Correct the errors in the worksheet as follows:

a. In cell H7, 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 start date (the range named SEO_Start) is less than the date in cell C4. If it is, multiply the billable amount (cell G7) by the discount rate (cell H4) and return the result. If it is not, return the billable amount (cell G7) in cell H7.

b. Copy the formula in cell H7 into the range H8:H16 to fix the errors in that range.

c. In cell H17, use Error Checking to find the formula error, and then correct the formula.

d. Correct the formula error in cell G17.

2. After entering the project code data in the range E7:E16, Carla applied data validation to the range to make sure she and others entered the correct codes. Check for data-entry errors and correct them as follows:

a. Circle invalid data in the worksheet.

b. Change the circled value to 230 to use the correct project code.

3. In the range J12:K17, Carla has created an area for analyzing the projects for specific types of services. She wants to analyze the sales of redesign projects because they have the highest billable amounts. Create formulas to analyze this data as follows:

a. In cell K14, enter a formula using the COUNTIF function to count the number of redesign projects. Use the list of project codes (range E7:E16) as the range and the code for redesign services (250) as the criteria in your formula.

b. In cell K15, enter a formula using the AVERAGEIF function to find the average billable amount for redesign projects. Use the list of project codes as the range, the code for redesign services as the criteria, and the discounted amounts (range H7:H16) as the range to average in your formula.

c. In cell K16, use the SUMIF function to find the total billable amount of redesign projects. Use the list of project codes as the range, the code for redesign services as the criteria, and the discounted amounts as the range to sum in your formula.

4. In cell K17, Carla wants to display the profit margin for redesign projects, which is contained in another worksheet. Create a formula using an external reference as follows to include the profit margin:

a. Open the workbook Support_EX19_11b_Redesign.xlsx.

b. In cell K17 of the SEO Projects worksheet, insert a formula that references cell C7 in the Support_EX19_11b_Redesign.xlsx workbook.

c. Close the Support_EX19_11b_Redesign.xlsx workbook.

5. Carla has defined a name for cell K17, but wants to change it to one more similar to the text in cell J17. Edit the defined name for cell K17 to use Profit_Margin as the name. [Mac Hint: Delete the old defined name and create a new defined name.]

6. Go to the Social Media Projects worksheet. In the range F7:F15, Carla needs to enter a formula to calculate the end dates for the projects, which have a duration of 90 days. She wants to use a defined name in the formula as she did on the SEO Projects worksheet. Calculate the expiration dates as follows:

a. Examine the formula in cell H7, which uses a defined name to calculate the sale price.

b. Assign the same defined name (Social_Start) to the range D7:D15, which resolves the errors in column H.

c. In cell F7, enter a formula without using a function to determine the project end date by adding 90 to the defined name Social_Start.

d. Fill the range F8:F15 with the formula in cell F7.

7. The workbook contains a defined name Carla no longer needs. Delete the defined name inv_num (but not the data) from the workbook.

8. In the range J6:M11, Carla wants to list data about new social media clients who have not signed a contract yet. On a separate worksheet named New Clients, she imported the data, but it did not appear in the correct format. For example, the client 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 Social Media Projects worksheet to incorporate the imported client name data in the range J7:J11 using worksheet references as follows:

a. In cell J7 on the Social Media Projects worksheet, enter a formula using the PROPER function to capitalize the first letter in each word in the Client text in cell B3 of the New Clients worksheet.

b. Fill the range J8:J11 with the formula in cell J7 to show the client names.

9. The imported contact name data separates the first and last names, but Carla 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:

a. In cell K7, 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.

b. Fill the range K8:K11 with the formula in cell K7 to list the full names of the remaining contacts.

10. List the number of years in business and the state where the client is located using worksheet references as follows:

a. In cell L7, enter a formula using the LEFT function to insert the first 2 characters on the left of cell E3 of the New Clients worksheet.

b. Fill the range L8:L11 with the formula in cell L7.

c. In cell M7, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell F3 of the New Clients worksheet.

d. Fill the range M8:M11 with the formula in cell M7.

11. Carla asks you to calculate the total project amounts for both types of projects. Go to the Summary worksheet. In cell B5, create a formula using the SUM function and 3-D references to total cell H17 on the SEO Projects worksheet and cell H16 on the Social Media Projects worksheet.

12. Next, Carla wants to separate the contact names from their invoice numbers and format the names with the last name first. Modify the data on the Summary worksheet as follows:

a. Enter the text Brazell, Kathryn in cell E6.

b. Select the range E6:E24, 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.)

c. Enter the invoice number 1165 in cell C6.

d. Select the range C6:C24, 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.)

e. Delete column D, which is no longer necessary.

13. Go to the Profit Projections worksheet, which includes the income and expenses for the current year. Carla also wants to include projections for next year. First, she asks you to correct the error on the worksheet. Trace the error in cell G18, which divides the net profit in cell G17 by the total income in cell G6. The formula is correct, but cell G6 needs a value to prevent the error. Correct the error as follows:

a. In cell G18, add the IFERROR function to the formula.

b. If dividing the net profit (cell G17) by the total income (cell G6) results in an error, display "Missing total" as an error message.

c. Copy the formula in cell G18 to the range C18:F18.

d. In cell G6, enter a formula using the SUM function to total the Q1–Q4 income amounts (range C6:F6).

14. Carla thinks her company has a good chance of increasing income in the next year. In the range C22:F24, she wants to make three projections based on different assumptions. Project the quarterly income for Carla as follows:

a. First, determine the income needed in Quarters 2 and 3 to achieve an income of $18,000 in Quarter 4. Project the income in the four quarters by filling the series for the first projection (range C22:F22) with a linear trend.

b. Next, determine the income in Q2–Q4 if it increases by 2.5 percent each quarter, starting with an income of $17,500. Project the income in the four quarters by filling the series for the second projection (range C23:F23) based on a growth trend using 1.025 as the step value.

c. Finally, extrapolate the income based on the income in Q4 of the current year and a growth rate of 3 percent. In cell C24, enter a formula without using a function to reference the total income in Q4 of the current year (cell F6). Project the income in the four quarters by filling the series for the third projection (range C24:F24) based on a growth trend using 1.03 as the step value.

15. Carla has prepared the range I2:N18 for detailed projections to complete later. For now, she asks you to enter the expense categories, which she will use in other workbooks. Build a custom list of categories as follows:

a. Create a custom list by importing the range B5:B18.

b. Enter the text Income in cell I5, and then use the new AutoList to fill cells I6:I18.

16. Carla is considering whether to purchase a web design business to increase the services she provides to clients. She asks you to help with the analysis of loan options to fund the purchase. Go to the Growth Scenarios worksheet, and then create defined names based on the range B6:C11, using the values in the left column only.

17. Calculate the monthly payment for Scenario 1 as follows:

a. In cell C10, enter a formula using the PMT function to determine the monthly payment.

b. In the formula, use the monthly interest rate (cell C8) as the rate, the term in months (cell C9) as the nper, and the loan amount (cell C6) as the pv.

18. In Scenario 2, Carla wants to make a monthly payment of $17,000 at an annual interest rate of 4.45 percent. She wants to know how much she can borrow on those terms. Calculate the loan amount for Carla as follows:

a. In cell D6, enter a formula using the PV function to determine the loan amount.

b. In the formula, use the monthly interest rate (cell D8) as the rate, the term in months (cell D9) as the nper, and the monthly payment (cell D10) as the pmt.

19. In Scenario 3, Carla wants to know the total amount spent for a loan of $895,000 at an annual interest rate of 4.1 percent and a monthly payment of $16,000. Calculate the future value of the loan for Carla as follows:

a. In cell E11, enter a formula using the FV function to determine the future value.

b. In the formula, use the monthly interest rate (cell E8) as the rate, the term in months (cell E9) as the nper, and the monthly payment (cell E10) 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.

Final Figure 1: SEO Projects Worksheet

Final Figure 2: Social Media Projects Worksheet

Final Figure 3: Summary Worksheet

Final Figure 4: Profit Projections Worksheet

Final Figure 5: Growth Scenarios Worksheet