2. 3. PROJECT STEPS 1. Lonnie Holtzman is a financial analyst for the Lewellen Group, a management consulting firm headquartered in Providence, Rhode Island. The firm also has offices in Charlotte, North Carolina; Milwaukee, Wisconsin; and Denver, Colorado. Lonnie is using an Excel workbook with multiple worksheets to create a profit and loss statement for the firm. He asks for your help in completing the statement. Go to the Dashboard worksheet. Lonnie first wants to complete the list of links in the range H7:H10. In cell H7, create a hyperlink to the www.lewellen.example.net website. In cell H8, create a hyperlink to the info@lewellen.example.net email address. Use Contact a Lewellen consultant as the ScreenTip text. In cell H9, create a hyperlink to cell A1 in the Categories worksheet. In cell H10, create a hyperlink to the workbook Support_EX19_EOM5-1_2021.xlsx, which contains Lewellen profit and loss data from 2021. Lonnie wants to compare 2021 profit and loss totals to those for 2022 and needs to add the 2021 data to the Dashboard worksheet. Open the file Support_EX19_EOM5- 1_2021.xlsx. Copy the values in the range C6:C19. In cell C6 of the Dashboard worksheet in the original workbook, use the Paste Link command to create external references to the values in the Support_EX19_EOM5-1_2021.xlsx workbook. Delete the unnecessary values in cells C8, C12, and c6, and close the Support_EX19_EOM5-1_2021.xlsx workbook. 6. Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use the name PayrollExp to define the range 09:011. Use the name OpExp to define the range D13:D15. 4. 5. CENGAGE 7. 8. 9. In cell D18, enter a formula using the SUM function to total the expense values using the defined range names Payroll xp and OpExp. Lonnie prefers that accountants and others contact Lewellen through the link in cell H8 rather than one that connects to his email address. In cell C21, remove the hyperlink, leaving the unlinked text "Lonnie Holtzman" in the cell. Lonnie wants to combine the profit and loss data for 2022 from each of the three Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C6 in the Charlotte: Milwaukee worksheets. Copy the formula from cell E6 to cell E7, the range 09:11, the range E13:E15, and the range E17:519. In the range E17:619, copy the formula and number formatting only. 10. Go to the Charlotte worksheet. Lonnie needs to calculate the percent of sales for each expense item. Assign the defined name Revenue to cell 06. 11. In cell D7, enter a formula without using a function that divides the cost of sales value in cell C7 by the revenue amount, using the defined name Revenue for the Charlotte worksheet. Copy the formula from cell D7 to the range D9:011, the range D13:015, and the range D17:019. In the range D17:019, copy the formula and number formatting only. 12. Lonnie wants to apply consistent formatting to the worksheets he collected from the three Lewellen offices. Group the Charlotte, Denver, and Milwaukee worksheets, and then apply the Heading 1 cell style to cell B2 to use the same formatting as in the Dashboard worksheet. Ungroup the worksheets, and then verify that the formatting is applied to all three worksheets. 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. NP_EX19_EOM5-11 Io Page Layout Home Insert Formulas Data Review View X Calibri (Body) 11 A- A+ | I General en = Conditional Forr Format as Table Cell Styles Paste BI U U $ 4.0 .00 .00 1.0 J1 4 x fx B D E F G H 1 Profit and Loss Statement For the year 2022 2 LEWELLEN Group 3 4 5 6 Total 2021 Total Budget $ 583,888 165,135 Total 2022 % Change from Budget -100.0% -100.0% 7 8 Links Lewellen website Contact Lewellen Category list Profit and loss statement 2021 9 143,500 72,526 38,635 -100.0% -100.0% 10 -100.0% 11 12 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 13 -100.0% 14 50,250 65,655 41,750 -100.0% -100.0% 15 16 17 418,753 18 -100.0% #DIV/0! -100.0% $ 418,753 Prepared by Lonnie Holtsman 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Documentation Dashboard Charlotte Denver Milwaukee Categories Home Insert Page Layout Formulas Data Review View = X . Calibri (Body) 11 = A- A- General 3 Paste BIU $ % ) 4.0 .00 .00 2.0 = X fx A D E F G . NA Profit and Loss Statement For the year 2022 2 3 4 LEWELLEN Group 5 % Sales 6 Total 2022 $ 196,900 55,045 Manager Location 7 Charlotte Office Ron Keller 225 E. Center St. Charlotte, NC 28214 (704) 555-9801 8 9 Phone 46,667 24,175 13,030 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 17,950 22,035 14,450 141,855 138,307 3,548 $ 19 20 21 22 23 24 25 Documentation Dashboard Charlotte Denver Milwaukee Categories + Ready Home Insert Page Layout Formulas Data Review View 11 A- A General Calibri (Body) BIU IL 1 Paste $ 7 4.0 .00 % ) 11 fx A B D E F G H 1 Profit and Loss Statement For the year 2022 2 3 4 LEWELLEN Group 5 % Sales 6 Total 2022 $ 197,857 54,605 Manager Location 7 28% Denver Office Pilar Plata 1897 W. Clark Ave. Denver, CO 80201 (720) 555-1424 00 9 24% Phone 47,028 24,775 10 13% 13,050 7% 11 12 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 9% 13 14 18,455 22,267 13,284 11% 7% 15 16 17 72% 18 143,252 138,859 4,393 70% $ 2% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Documentation Dashboard Charlotte Denver Milwaukee Categories + Ready Home Insert Page Layout Formulas Data Review View 11 A- A General Calibri (Body) B I u IL 1 Paste $ 7 % ) 0 .00 .00 0 11 fx A B D E F G . 1 Profit and Loss Statement For the year 2022 LEWELLEN Group 2 3 4 5 % Sales 6 Total 2022 $ 196,972 54,003 Manager Location 7 27% Milwaukee Office Jum Nguyen 344 E. Wisconsin Ave. Milwaukee, WI 53205 (414) 555-2166 00 9 24% Phone 10 46,750 24,146 13,577 12% 7% 11 12 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 9% 13 14 17,182 21,990 11% 14,506 7% 15 16 17 73% 142,969 138,151 18 70% $ 4,818 2% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Documentation Dashboard Charlotte Denver Milwaukee Categories + Home Insert Page Layout Formulas Data Review View Calibri (Body) 11 A- A- General = = Paste $ % ) 4.0 .00 ab G1 Copy x fx A B D 1 Category 2 Revenue Cost of sales 3 4 Salaries Benefits 5 6 Taxes Description Income from consulting services, including service agreements Total of all costs used to produce consulting services, including overhead and commissions Amount paid to employees as salary before taxes and withholdings Employee compensation in addition to salary, including Social Security and insurance Payroll taxes paid on salaries Amount spent on sales and marketing efforts, including advertising and promotional materials Operation expenses such as rent and utilities Other operating expenses such as equipment rental and contracted labor Sales and marketing General and administrative Other operating 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Documentation Dashboard Charlotte Denver Milwaukee Categories + 2. 3. PROJECT STEPS 1. Lonnie Holtzman is a financial analyst for the Lewellen Group, a management consulting firm headquartered in Providence, Rhode Island. The firm also has offices in Charlotte, North Carolina; Milwaukee, Wisconsin; and Denver, Colorado. Lonnie is using an Excel workbook with multiple worksheets to create a profit and loss statement for the firm. He asks for your help in completing the statement. Go to the Dashboard worksheet. Lonnie first wants to complete the list of links in the range H7:H10. In cell H7, create a hyperlink to the www.lewellen.example.net website. In cell H8, create a hyperlink to the info@lewellen.example.net email address. Use Contact a Lewellen consultant as the ScreenTip text. In cell H9, create a hyperlink to cell A1 in the Categories worksheet. In cell H10, create a hyperlink to the workbook Support_EX19_EOM5-1_2021.xlsx, which contains Lewellen profit and loss data from 2021. Lonnie wants to compare 2021 profit and loss totals to those for 2022 and needs to add the 2021 data to the Dashboard worksheet. Open the file Support_EX19_EOM5- 1_2021.xlsx. Copy the values in the range C6:C19. In cell C6 of the Dashboard worksheet in the original workbook, use the Paste Link command to create external references to the values in the Support_EX19_EOM5-1_2021.xlsx workbook. Delete the unnecessary values in cells C8, C12, and c6, and close the Support_EX19_EOM5-1_2021.xlsx workbook. 6. Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use the name PayrollExp to define the range 09:011. Use the name OpExp to define the range D13:D15. 4. 5. CENGAGE 7. 8. 9. In cell D18, enter a formula using the SUM function to total the expense values using the defined range names Payroll xp and OpExp. Lonnie prefers that accountants and others contact Lewellen through the link in cell H8 rather than one that connects to his email address. In cell C21, remove the hyperlink, leaving the unlinked text "Lonnie Holtzman" in the cell. Lonnie wants to combine the profit and loss data for 2022 from each of the three Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C6 in the Charlotte: Milwaukee worksheets. Copy the formula from cell E6 to cell E7, the range 09:11, the range E13:E15, and the range E17:519. In the range E17:619, copy the formula and number formatting only. 10. Go to the Charlotte worksheet. Lonnie needs to calculate the percent of sales for each expense item. Assign the defined name Revenue to cell 06. 11. In cell D7, enter a formula without using a function that divides the cost of sales value in cell C7 by the revenue amount, using the defined name Revenue for the Charlotte worksheet. Copy the formula from cell D7 to the range D9:011, the range D13:015, and the range D17:019. In the range D17:019, copy the formula and number formatting only. 12. Lonnie wants to apply consistent formatting to the worksheets he collected from the three Lewellen offices. Group the Charlotte, Denver, and Milwaukee worksheets, and then apply the Heading 1 cell style to cell B2 to use the same formatting as in the Dashboard worksheet. Ungroup the worksheets, and then verify that the formatting is applied to all three worksheets. 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. NP_EX19_EOM5-11 Io Page Layout Home Insert Formulas Data Review View X Calibri (Body) 11 A- A+ | I General en = Conditional Forr Format as Table Cell Styles Paste BI U U $ 4.0 .00 .00 1.0 J1 4 x fx B D E F G H 1 Profit and Loss Statement For the year 2022 2 LEWELLEN Group 3 4 5 6 Total 2021 Total Budget $ 583,888 165,135 Total 2022 % Change from Budget -100.0% -100.0% 7 8 Links Lewellen website Contact Lewellen Category list Profit and loss statement 2021 9 143,500 72,526 38,635 -100.0% -100.0% 10 -100.0% 11 12 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 13 -100.0% 14 50,250 65,655 41,750 -100.0% -100.0% 15 16 17 418,753 18 -100.0% #DIV/0! -100.0% $ 418,753 Prepared by Lonnie Holtsman 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Documentation Dashboard Charlotte Denver Milwaukee Categories Home Insert Page Layout Formulas Data Review View = X . Calibri (Body) 11 = A- A- General 3 Paste BIU $ % ) 4.0 .00 .00 2.0 = X fx A D E F G . NA Profit and Loss Statement For the year 2022 2 3 4 LEWELLEN Group 5 % Sales 6 Total 2022 $ 196,900 55,045 Manager Location 7 Charlotte Office Ron Keller 225 E. Center St. Charlotte, NC 28214 (704) 555-9801 8 9 Phone 46,667 24,175 13,030 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 17,950 22,035 14,450 141,855 138,307 3,548 $ 19 20 21 22 23 24 25 Documentation Dashboard Charlotte Denver Milwaukee Categories + Ready Home Insert Page Layout Formulas Data Review View 11 A- A General Calibri (Body) BIU IL 1 Paste $ 7 4.0 .00 % ) 11 fx A B D E F G H 1 Profit and Loss Statement For the year 2022 2 3 4 LEWELLEN Group 5 % Sales 6 Total 2022 $ 197,857 54,605 Manager Location 7 28% Denver Office Pilar Plata 1897 W. Clark Ave. Denver, CO 80201 (720) 555-1424 00 9 24% Phone 47,028 24,775 10 13% 13,050 7% 11 12 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 9% 13 14 18,455 22,267 13,284 11% 7% 15 16 17 72% 18 143,252 138,859 4,393 70% $ 2% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Documentation Dashboard Charlotte Denver Milwaukee Categories + Ready Home Insert Page Layout Formulas Data Review View 11 A- A General Calibri (Body) B I u IL 1 Paste $ 7 % ) 0 .00 .00 0 11 fx A B D E F G . 1 Profit and Loss Statement For the year 2022 LEWELLEN Group 2 3 4 5 % Sales 6 Total 2022 $ 196,972 54,003 Manager Location 7 27% Milwaukee Office Jum Nguyen 344 E. Wisconsin Ave. Milwaukee, WI 53205 (414) 555-2166 00 9 24% Phone 10 46,750 24,146 13,577 12% 7% 11 12 Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 9% 13 14 17,182 21,990 11% 14,506 7% 15 16 17 73% 142,969 138,151 18 70% $ 4,818 2% 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Documentation Dashboard Charlotte Denver Milwaukee Categories + Home Insert Page Layout Formulas Data Review View Calibri (Body) 11 A- A- General = = Paste $ % ) 4.0 .00 ab G1 Copy x fx A B D 1 Category 2 Revenue Cost of sales 3 4 Salaries Benefits 5 6 Taxes Description Income from consulting services, including service agreements Total of all costs used to produce consulting services, including overhead and commissions Amount paid to employees as salary before taxes and withholdings Employee compensation in addition to salary, including Social Security and insurance Payroll taxes paid on salaries Amount spent on sales and marketing efforts, including advertising and promotional materials Operation expenses such as rent and utilities Other operating expenses such as equipment rental and contracted labor Sales and marketing General and administrative Other operating 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Documentation Dashboard Charlotte Denver Milwaukee Categories +