formuas for all steps requiring formulas

9. 7. In cell 018, enter a formula using the SUM function to total the expense values using the defined range names PayrollExp and OpExp. 8. 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 ising 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 E9:E11, the range E13:E15, and the range E17:E19. In the range E17:E19, 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 09: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. File Home Insert Page Layout Formulas Data Review View Acrobat Calibri - 11 AA Wrap Test General Peste BIU- A Merge & Center $ . %. Clipboard Font . Conditional Formats Cell Formatting Table Style Style Alignment Number 310 X LEWELLEN Group Links swollen website Contact Lewellen Category list Profit and loss statement 2021 SA D E F 1 Profit and Loss Statement 2 For the year 2022 3 4 Stated in 000 5 Category Total 2021 Total Budget Total 2022 % change from Budget 6 Revenue $579,075 $ 583,888 -100.0% 7 Cost of sales 162,890 165,135 - 100.0% 8 Payroll 9 Salaries 143,000 143,500 -100.0% 10 Benefits 69,780 72,526 -100.0% 11 Taxes 38,025 38,635 -100.0% 12 Operating Expenses 13 Sales and marketing 50,046 50,250 -100.0% 14 General and administrative 64,128 65,655 -100.0% 15 Other operating 41.754 41,750 -100.0% 16 Summary 17 Gross profit 416,185 418,753 -100.0% 18 Total expenses 406,733 412,316 -100.0% 19 Net profit $ 9,452 $ 6,437 -100.0% 20 21 Prepared by Lonnie Holtsman 22 23 24 25 26 27 28 29 30 31 32 33 34 15 Dashboard Charlott Denver Milwaukee Categories 4 Ready Home Insert Page Layout en tawford 1 - Excel Formulas Data Review View X Acrobat Tell me what you want to do Calibri - 11 Pa A A B.A. General BIU- CA- EWrap Test Merge & Center $ - % Board Font % Conditional Format Formatting Table Shtes Alignment Number A D E G H C Profit and Loss Statement For the year 2022 LEWELLEN Group % Sales Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Total 2022 $196,900 55,045 Manager Location Charlotte office Ron Keller 225 E. Center St. Charlotte, NC 28214 (704) 555-9801 Phone 46,667 24,175 13,030 17,950 22.035 Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 5 6 14.450 7 8 141.855 138,307 $ 3,548 9 20 21 22 23 25 26 27 28 29 30 31 32 33 34 35 Milwaukee Categories Denver Dashboard Charlotte Ready File Home Insert Page Layout Formes NP_EX(19_EOMS-1_ayden Crawford. 1 - Excel Review View Acrobie Te me what you want Data Calibri - 31 Paste BIU General - AA 2. Wsplet A. Merge & Center - Alignment Choboard $.$. Font Conditional Formats Formatting Table 11 Number f E G H LEWELLEN Group Manager Location Denver Office Pilar Plata 1897 W. Clark Ave. Denver, CO 80201 720) 555-1424 Phone 4A B C D 1 Profit and Loss Statement 2 For the year 2022 3 4 Stated in 00s 5 Category Total 2022 % Sales 6 Revenue $ 197,857 7 Cost of sales 54,605 28% Payroll o Salaries 47,028 24% 10 Benefits 24,775 13% 11 Taxes 13,050 7% 12 Operating Expenses 13 Sales and marketing 18,455 9% 14 General and administrative 22,267 11% 15 Other operating 13,284 16 Summary 17 Gross profit 143,252 72% 18 Total expenses 138,859 70% 19 Net profit $4,393 2% 20 21 22 23 24 25 27 28 29 30 31 32 33 34 35 Dashboard Charlotte Denver Milwaukee Categories Ready File Home insert Page Layout Formulas NP_510_EMS-1Hayden Crawford 1. Escal Revie View Acrobat Data Genes Calibri - 111-AA . Wap Tot BTW. A Sa Merge Center Font Alt Clipboard 1 Conditional Format Call Formatting Table Styles Shits A B D H Profit and Loss Statement For the year 2022 1 2 3 4 5. LEWELLEN Group Sales 6 Total 2022 $196,972 54,003 7 8 Manager Location 27% Milwaukee Office Jum Nguyen 344 E Wisconsin Ave. kee, WI 53205 (414) 555-2166 24% Phone 46,750 24,146 13,577 12% 7% 9 10 11 12 13 14 75 16 Stated in 000 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% 17,182 21,990 14,506 11% 7% 17 142,969 138,151 $ 4,818 73% 20% 2% 18 19 20 21 22 25 26 27 28 29 30 31 32 33 35 Milwaukee Dashboard Denver Categories Charlotte Ready 3 Share 55 SportEOMS-1,21 File Home net Page Layout Form Aci X Calbe LA P. BTW. - A Meerder Opboard Font Ravi Candida Formatting CG 5707 1 D 3 4 Group 5 7 9 12 Stated in 000 Cate on Total 2021 Total Budget Change from Budres Revenue $579,075 S 80,250 0.2% Cost of sales 162,190 163.132 -0.1% Payroll Salaries 143,000 143,000 0.0% Benefits 69,780 69,500 0.4% Taxes 38.025 37.235 2.1% Operating Expenses Sales and marketing 50,046 50,250 -0.4% General and administrative 64,128 65,245 -17% Other operating 41,754 42,057 -0.7% Summary Gross profit 416,15 417,118 -0.7 Total expenses 106,733 407.27 -0.1% 9,831 -3. 594525 Net profit 23 13 14 15 16 17 18 19 20 21 22 23 14 2021 Summary Asage Count 11 um Ready 124 25 26 27 28 29 30 31 32 33 34 Mwak Categories Dashboard Charlotte Ready 9. 7. In cell 018, enter a formula using the SUM function to total the expense values using the defined range names PayrollExp and OpExp. 8. 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 ising 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 E9:E11, the range E13:E15, and the range E17:E19. In the range E17:E19, 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 09: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. File Home Insert Page Layout Formulas Data Review View Acrobat Calibri - 11 AA Wrap Test General Peste BIU- A Merge & Center $ . %. Clipboard Font . Conditional Formats Cell Formatting Table Style Style Alignment Number 310 X LEWELLEN Group Links swollen website Contact Lewellen Category list Profit and loss statement 2021 SA D E F 1 Profit and Loss Statement 2 For the year 2022 3 4 Stated in 000 5 Category Total 2021 Total Budget Total 2022 % change from Budget 6 Revenue $579,075 $ 583,888 -100.0% 7 Cost of sales 162,890 165,135 - 100.0% 8 Payroll 9 Salaries 143,000 143,500 -100.0% 10 Benefits 69,780 72,526 -100.0% 11 Taxes 38,025 38,635 -100.0% 12 Operating Expenses 13 Sales and marketing 50,046 50,250 -100.0% 14 General and administrative 64,128 65,655 -100.0% 15 Other operating 41.754 41,750 -100.0% 16 Summary 17 Gross profit 416,185 418,753 -100.0% 18 Total expenses 406,733 412,316 -100.0% 19 Net profit $ 9,452 $ 6,437 -100.0% 20 21 Prepared by Lonnie Holtsman 22 23 24 25 26 27 28 29 30 31 32 33 34 15 Dashboard Charlott Denver Milwaukee Categories 4 Ready Home Insert Page Layout en tawford 1 - Excel Formulas Data Review View X Acrobat Tell me what you want to do Calibri - 11 Pa A A B.A. General BIU- CA- EWrap Test Merge & Center $ - % Board Font % Conditional Format Formatting Table Shtes Alignment Number A D E G H C Profit and Loss Statement For the year 2022 LEWELLEN Group % Sales Stated in 000s Category Revenue Cost of sales Payroll Salaries Benefits Total 2022 $196,900 55,045 Manager Location Charlotte office Ron Keller 225 E. Center St. Charlotte, NC 28214 (704) 555-9801 Phone 46,667 24,175 13,030 17,950 22.035 Taxes Operating Expenses Sales and marketing General and administrative Other operating Summary Gross profit Total expenses Net profit 5 6 14.450 7 8 141.855 138,307 $ 3,548 9 20 21 22 23 25 26 27 28 29 30 31 32 33 34 35 Milwaukee Categories Denver Dashboard Charlotte Ready File Home Insert Page Layout Formes NP_EX(19_EOMS-1_ayden Crawford. 1 - Excel Review View Acrobie Te me what you want Data Calibri - 31 Paste BIU General - AA 2. Wsplet A. Merge & Center - Alignment Choboard $.$. Font Conditional Formats Formatting Table 11 Number f E G H LEWELLEN Group Manager Location Denver Office Pilar Plata 1897 W. Clark Ave. Denver, CO 80201 720) 555-1424 Phone 4A B C D 1 Profit and Loss Statement 2 For the year 2022 3 4 Stated in 00s 5 Category Total 2022 % Sales 6 Revenue $ 197,857 7 Cost of sales 54,605 28% Payroll o Salaries 47,028 24% 10 Benefits 24,775 13% 11 Taxes 13,050 7% 12 Operating Expenses 13 Sales and marketing 18,455 9% 14 General and administrative 22,267 11% 15 Other operating 13,284 16 Summary 17 Gross profit 143,252 72% 18 Total expenses 138,859 70% 19 Net profit $4,393 2% 20 21 22 23 24 25 27 28 29 30 31 32 33 34 35 Dashboard Charlotte Denver Milwaukee Categories Ready File Home insert Page Layout Formulas NP_510_EMS-1Hayden Crawford 1. Escal Revie View Acrobat Data Genes Calibri - 111-AA . Wap Tot BTW. A Sa Merge Center Font Alt Clipboard 1 Conditional Format Call Formatting Table Styles Shits A B D H Profit and Loss Statement For the year 2022 1 2 3 4 5. LEWELLEN Group Sales 6 Total 2022 $196,972 54,003 7 8 Manager Location 27% Milwaukee Office Jum Nguyen 344 E Wisconsin Ave. kee, WI 53205 (414) 555-2166 24% Phone 46,750 24,146 13,577 12% 7% 9 10 11 12 13 14 75 16 Stated in 000 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% 17,182 21,990 14,506 11% 7% 17 142,969 138,151 $ 4,818 73% 20% 2% 18 19 20 21 22 25 26 27 28 29 30 31 32 33 35 Milwaukee Dashboard Denver Categories Charlotte Ready 3 Share 55 SportEOMS-1,21 File Home net Page Layout Form Aci X Calbe LA P. BTW. - A Meerder Opboard Font Ravi Candida Formatting CG 5707 1 D 3 4 Group 5 7 9 12 Stated in 000 Cate on Total 2021 Total Budget Change from Budres Revenue $579,075 S 80,250 0.2% Cost of sales 162,190 163.132 -0.1% Payroll Salaries 143,000 143,000 0.0% Benefits 69,780 69,500 0.4% Taxes 38.025 37.235 2.1% Operating Expenses Sales and marketing 50,046 50,250 -0.4% General and administrative 64,128 65,245 -17% Other operating 41,754 42,057 -0.7% Summary Gross profit 416,15 417,118 -0.7 Total expenses 106,733 407.27 -0.1% 9,831 -3. 594525 Net profit 23 13 14 15 16 17 18 19 20 21 22 23 14 2021 Summary Asage Count 11 um Ready 124 25 26 27 28 29 30 31 32 33 34 Mwak Categories Dashboard Charlotte Ready