- 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 C16, and close the Support_EX19_EOM5-1_2021.xlsx workbook.
- Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use the name PayrollExp to define the range D9:D11. Use the name OpExp to define the range D13:D15.
D@= NP_EX19_EOM5-1_RochiBorbon_1 Q Search Sheet Home Page Layout Formulas Data Review View 9+ Share Insert Cut Calibri (Body) - 11 A- A+ Wrap Text General WD AutoSum Fill 49 H H Copy " Paste BIU A Merge & Center $ % %) +.0 .00 .00 >0 Insert Conditional Format Cell Formatting as Table Styles Delete Format Format Clear Sort & Filter Check for Updates x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. J4 4 X fx G H 1 K L M N O P Q R S T U V LEWELLEN Group Links Lewellen website Contact Lewellen Category list Profit and loss statement 2021 A B D E F F 1 Profit and Loss Statement 2 For the year 2022 31 4 4 Stated in 000s 5 Category Total 2021 Total Budget Total 2022 % Change from Budget 6 Revenue $ 583,888 -100.0% 7 Cost of sales 165,135 -100.0% 8 Payroll 9 Salaries 143,500 -100.0% 10 Benefits 72,526 -100.0% 11 Taxes 38,635 -100.0% 12 Operating Expenses 13 Sales and marketing 50,250 -100.0% 14 General and administrative 65,655 -100.0% 15 Other operating 41,750 -100.0% 16 Summary 17 Gross profit 418,753 -100.0% 18 Total expenses #DIV/0! 19 Net profit $ 418,753 -100.0% 20 21 Prepared by Lonnie Holtsman 22 23 24 25 26 27 28 29 301 31 32 33 34 35 36 37 38 Documentation Dashboard Charlotte Denver Milwaukee Categories + Select destination and press ENTER or choose Paste a + 100% D@= Support_EX19_EOM5-1_2021 Q Search Sheet Home Page Layout Formulas Data Review View 9+ Share Insert Cut Calibri (Body) - 11 A- A+ 5 Wrap Text Custom WD AutoSum Fill 49 H H Copy " Paste BIU A = Merge & Center $ %) +.0 .00 .00 0 Insert Conditional Format Cell Formatting as Table Styles Delete Format Format Clear Sort & Filter Check for Updates H 1 J K L N o P Q R S T U V Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. C6 4 x v fx 579075 A B D E F G 1 Profit and Loss Statement 2 For the year 2021 LEWELLEN 3 Group 4 Stated in 00s 5 Category Total 2021 Total Budget % Change from Budget 6 Revenue $ 579,075 $ 580,250 -0.2% 7 Cost of sales 162,890 163,132 -0.1% 8 Payroll 9 Salaries 143,000 143,000 0.0% 10 Benefits 69,780 69,500 0.4% 11 Taxes 38,025 37,235 2.1% 12 Operating Expenses 13 Sales and marketing 50,046 50,250 -0.4% 14 General and administrative 64,128 65,245 -1.7% 15 Other operating 41,754 42,057 -0.7% 16 Summary 17 Gross profit 416,185 417,118 -0.2% 18 Total expenses 406,733 407,287 -0.1% 19 Net profit $ 9,452 | $ 9.831 -3.9% 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 2021 Summary + Select destination and press ENTER or choose Paste Average: $180,097 Count: 11 Sum: $1,981,068 2 a + 100% D@= NP_EX19_EOM5-1_RochiBorbon_1 Q Search Sheet Home Page Layout Formulas Data Review View 9+ Share Insert Cut Calibri (Body) - 11 A- A+ Wrap Text General WD AutoSum Fill 49 H H Copy " Paste BIU A Merge & Center $ % %) +.0 .00 .00 >0 Insert Conditional Format Cell Formatting as Table Styles Delete Format Format Clear Sort & Filter Check for Updates x Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. J4 4 X fx G H 1 K L M N O P Q R S T U V LEWELLEN Group Links Lewellen website Contact Lewellen Category list Profit and loss statement 2021 A B D E F F 1 Profit and Loss Statement 2 For the year 2022 31 4 4 Stated in 000s 5 Category Total 2021 Total Budget Total 2022 % Change from Budget 6 Revenue $ 583,888 -100.0% 7 Cost of sales 165,135 -100.0% 8 Payroll 9 Salaries 143,500 -100.0% 10 Benefits 72,526 -100.0% 11 Taxes 38,635 -100.0% 12 Operating Expenses 13 Sales and marketing 50,250 -100.0% 14 General and administrative 65,655 -100.0% 15 Other operating 41,750 -100.0% 16 Summary 17 Gross profit 418,753 -100.0% 18 Total expenses #DIV/0! 19 Net profit $ 418,753 -100.0% 20 21 Prepared by Lonnie Holtsman 22 23 24 25 26 27 28 29 301 31 32 33 34 35 36 37 38 Documentation Dashboard Charlotte Denver Milwaukee Categories + Select destination and press ENTER or choose Paste a + 100% D@= Support_EX19_EOM5-1_2021 Q Search Sheet Home Page Layout Formulas Data Review View 9+ Share Insert Cut Calibri (Body) - 11 A- A+ 5 Wrap Text Custom WD AutoSum Fill 49 H H Copy " Paste BIU A = Merge & Center $ %) +.0 .00 .00 0 Insert Conditional Format Cell Formatting as Table Styles Delete Format Format Clear Sort & Filter Check for Updates H 1 J K L N o P Q R S T U V Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. C6 4 x v fx 579075 A B D E F G 1 Profit and Loss Statement 2 For the year 2021 LEWELLEN 3 Group 4 Stated in 00s 5 Category Total 2021 Total Budget % Change from Budget 6 Revenue $ 579,075 $ 580,250 -0.2% 7 Cost of sales 162,890 163,132 -0.1% 8 Payroll 9 Salaries 143,000 143,000 0.0% 10 Benefits 69,780 69,500 0.4% 11 Taxes 38,025 37,235 2.1% 12 Operating Expenses 13 Sales and marketing 50,046 50,250 -0.4% 14 General and administrative 64,128 65,245 -1.7% 15 Other operating 41,754 42,057 -0.7% 16 Summary 17 Gross profit 416,185 417,118 -0.2% 18 Total expenses 406,733 407,287 -0.1% 19 Net profit $ 9,452 | $ 9.831 -3.9% 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 2021 Summary + Select destination and press ENTER or choose Paste Average: $180,097 Count: 11 Sum: $1,981,068 2 a + 100%
