Module 5 - End of Module Project 1 #part2
Description
Topics covered in this video: 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. 9. 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 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 C6. 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:D11, the range D13:D15, and the range D17:D19. In the range D17:D19, 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.
Support 💖 Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar
#sam #spreadsheet #excel ---
Disclaimer: This video is intended for educational purposes only. The purpose of this video is to help students understand how to solve problems in the Cengage business spreadsheet course. However, please note that any attempt to copy or replicate the solutions shown in this video may be considered a violation of academic integrity policies at your college or university. We strongly encourage you to use this video as a guide and to complete the assignments on your own, without resorting to cheating or plagiarism. We are not responsible for any consequences that may result from the use of this video in violation of academic integrity policies.
Transcript
1 00:00:00,000 --> 00:00:03,899 so continuing from this step is it is
2 00:00:02,159 --> 00:00:06,240 saying low any prefers that are content
3 00:00:03,899 --> 00:00:08,400 and other contact leveling through the
4 00:00:06,240 --> 00:00:13,400 link in S8 rather than one that contact
5 00:00:08,400 --> 00:00:18,119 to his email address in cell c21
6 00:00:13,400 --> 00:00:19,920 so this is D21 this c21 uh it is saying
7 00:00:18,119 --> 00:00:22,800 remove the hyperlink leaving the
8 00:00:19,920 --> 00:00:24,600 underlying text in the cell so just to
9 00:00:22,800 --> 00:00:26,880 right click and then here is remove
10 00:00:24,600 --> 00:00:30,240 hyperlink option just click here and we
11 00:00:26,880 --> 00:00:32,099 are done and in Step 9 it is saying when
12 00:00:30,240 --> 00:00:36,239 you want to combine the profit and loss
13 00:00:32,099 --> 00:00:40,440 for loss data for 2022 is the incl C6
14 00:00:36,239 --> 00:00:42,780 inter formula so in cell E60 E6
15 00:00:40,440 --> 00:00:45,300 a internal formula using the sum
16 00:00:42,780 --> 00:00:47,760 function so we start with equal to sine
17 00:00:45,300 --> 00:00:51,059 and then sum and then open parenthesis
18 00:00:47,760 --> 00:00:53,579 close parenthesis uh using some function
19 00:00:51,059 --> 00:00:56,760 okay and 3D reference and grouped
20 00:00:53,579 --> 00:00:58,020 worksheet then totals the value from C6
21 00:00:56,760 --> 00:01:00,379 in
22 00:00:58,020 --> 00:01:04,920 uh
23 00:01:00,379 --> 00:01:07,380 colon and Below okay worksheets so it is
24 00:01:04,920 --> 00:01:10,320 saying using 3D reference
25 00:01:07,380 --> 00:01:13,380 okay so let's enter
26 00:01:10,320 --> 00:01:16,140 let's copy this a little bit easier so
27 00:01:13,380 --> 00:01:19,200 let's copy this and find the find the
28 00:01:16,140 --> 00:01:22,860 sum of these worksheet but we want to
29 00:01:19,200 --> 00:01:26,640 find the sum of cell E6 so we should do
30 00:01:22,860 --> 00:01:29,340 xka exclamation mark and then uh oh
31 00:01:26,640 --> 00:01:31,200 sorry C6 of the cell C6 so it is saying
32 00:01:29,340 --> 00:01:33,180 enter the formula using some function so
33 00:01:31,200 --> 00:01:35,700 we have some function and in 3D
34 00:01:33,180 --> 00:01:37,740 reference so this is 3D reference and
35 00:01:35,700 --> 00:01:41,100 group worksheet so this is group
36 00:01:37,740 --> 00:01:42,240 worksheet so we used uh colon E6 it
37 00:01:41,100 --> 00:01:45,240 means that
38 00:01:42,240 --> 00:01:48,119 now from the colon E6 of this worksheet
39 00:01:45,240 --> 00:01:49,500 and this works it calculate the sum and
40 00:01:48,119 --> 00:01:52,259 then hit
41 00:01:49,500 --> 00:01:55,140 just hit enter okay
42 00:01:52,259 --> 00:01:57,180 so this is our required answer
43 00:01:55,140 --> 00:02:01,259 and it is saying copy the formula from
44 00:01:57,180 --> 00:02:03,060 cell E6 to E7 so just go to the kernel
45 00:02:01,259 --> 00:02:06,840 and plus sign will appear and drag it
46 00:02:03,060 --> 00:02:09,179 towards E7 and after this let's do Ctrl
47 00:02:06,840 --> 00:02:14,459 C and it is saying copy the formula from
48 00:02:09,179 --> 00:02:17,640 to range E9 through 11 so E9 right click
49 00:02:14,459 --> 00:02:21,000 and then do paste sorry paste formula so
50 00:02:17,640 --> 00:02:23,879 paste formula and then hit Escape key to
51 00:02:21,000 --> 00:02:25,920 remove this around retorted line and
52 00:02:23,879 --> 00:02:28,800 then go to Corner plus sign will appear
53 00:02:25,920 --> 00:02:31,319 and then drag it up to elab and it is
54 00:02:28,800 --> 00:02:34,319 saying now pre 11 and then range e 13
55 00:02:31,319 --> 00:02:38,520 through E15 so same process
56 00:02:34,319 --> 00:02:42,120 copy it and then paste just the formula
57 00:02:38,520 --> 00:02:48,000 and then escape and then drag it
58 00:02:42,120 --> 00:02:51,060 and then again from E17 to 19. so same
59 00:02:48,000 --> 00:02:53,760 copy it and then paste this the formula
60 00:02:51,060 --> 00:02:56,640 and then escape and then go to that one
61 00:02:53,760 --> 00:02:58,200 and then drag it drag it up to E90 all
62 00:02:56,640 --> 00:03:01,379 right
63 00:02:58,200 --> 00:03:04,560 so let's quickly check check the answer
64 00:03:01,379 --> 00:03:08,640 so yeah that is our required answer now
65 00:03:04,560 --> 00:03:12,720 in the step 10 go to the Charlo t
66 00:03:08,640 --> 00:03:14,640 uh uh worksheet so let's go to here I
67 00:03:12,720 --> 00:03:18,060 may be mispronouncing it but it doesn't
68 00:03:14,640 --> 00:03:21,060 matter and Loni needs to calculate the
69 00:03:18,060 --> 00:03:24,420 percent of sale for that assign the
70 00:03:21,060 --> 00:03:27,300 defined name Revenue to sell E6 so to
71 00:03:24,420 --> 00:03:29,879 sell E6 we should assigned it so right
72 00:03:27,300 --> 00:03:32,940 click and here is the Define name and
73 00:03:29,879 --> 00:03:35,700 the revenue is uh it is internet default
74 00:03:32,940 --> 00:03:39,739 so just hit okay
75 00:03:35,700 --> 00:03:42,180 in cell D7 so in cell D7 click D7
76 00:03:39,739 --> 00:03:45,000 internal formula without using a
77 00:03:42,180 --> 00:03:46,799 function that divides the so we need to
78 00:03:45,000 --> 00:03:50,099 enter formula without using a function
79 00:03:46,799 --> 00:03:54,360 and then in the formula that divides the
80 00:03:50,099 --> 00:03:56,400 cost of cell value in cell C7
81 00:03:54,360 --> 00:03:59,099 and buy the revenue amount
82 00:03:56,400 --> 00:04:01,319 so we just need to start with equals to
83 00:03:59,099 --> 00:04:04,019 sign and then enter Revenue we defined
84 00:04:01,319 --> 00:04:07,379 it previously and maybe a new revenue
85 00:04:04,019 --> 00:04:11,519 and then divide it oh sorry which will
86 00:04:07,379 --> 00:04:13,019 divide cell C7 by Revenue social C7 by
87 00:04:11,519 --> 00:04:16,139 Revenue
88 00:04:13,019 --> 00:04:18,720 and then hit enter uh use the Define
89 00:04:16,139 --> 00:04:22,079 name so we'll do that and then
90 00:04:18,720 --> 00:04:25,020 copy the formula from D7 to the range D9
91 00:04:22,079 --> 00:04:28,199 3D level so D7
92 00:04:25,020 --> 00:04:30,120 so copy it and then TNN so why are you
93 00:04:28,199 --> 00:04:32,759 all why are we only pasting the
94 00:04:30,120 --> 00:04:36,000 formatting it's because if we just Ctrl
95 00:04:32,759 --> 00:04:38,580 B and then this this case it didn't
96 00:04:36,000 --> 00:04:40,440 remove the spelling word in some case if
97 00:04:38,580 --> 00:04:42,900 it does to control V and it will remove
98 00:04:40,440 --> 00:04:45,419 this this is styling of the cell in
99 00:04:42,900 --> 00:04:49,380 there is Border in the cell and in some
100 00:04:45,419 --> 00:04:52,560 case that border might be removed
101 00:04:49,380 --> 00:04:55,560 so it's 24 percent
102 00:04:52,560 --> 00:04:59,460 and it is saying to copy from D9 through
103 00:04:55,560 --> 00:05:04,560 d11 so hit escape to remove this so from
104 00:04:59,460 --> 00:05:06,600 D9 through d11 and 12 7.
105 00:05:04,560 --> 00:05:11,360 uh
106 00:05:06,600 --> 00:05:13,680 there is D13 through D15 so same here
107 00:05:11,360 --> 00:05:17,100 we shall copy it
108 00:05:13,680 --> 00:05:19,580 and then paste it and then extend it up
109 00:05:17,100 --> 00:05:24,419 to D15
110 00:05:19,580 --> 00:05:26,100 uh and a d17 through d19 so here I think
111 00:05:24,419 --> 00:05:29,639 we should we should do the formatting
112 00:05:26,100 --> 00:05:33,180 only yeah that's what I am saying so
113 00:05:29,639 --> 00:05:37,139 let's do paste only formula
114 00:05:33,180 --> 00:05:39,120 and then drag it up to d19
115 00:05:37,139 --> 00:05:41,280 okay
116 00:05:39,120 --> 00:05:43,020 now last step learn you want to apply
117 00:05:41,280 --> 00:05:45,720 consistent formatting to the worksheet
118 00:05:43,020 --> 00:05:48,960 he collected from three level an office
119 00:05:45,720 --> 00:05:52,440 so group these this and this worksheet
120 00:05:48,960 --> 00:05:55,020 so download it then bar so click here
121 00:05:52,440 --> 00:05:57,780 and then hold Ctrl or command key and
122 00:05:55,020 --> 00:06:00,600 then click here and then click here
123 00:05:57,780 --> 00:06:05,000 and then apply the heading 1 style to
124 00:06:00,600 --> 00:06:08,039 cell B2 so click on B2
125 00:06:05,000 --> 00:06:09,479 to use the same formatting ungroup the
126 00:06:08,039 --> 00:06:12,780 worksheet and then verify so it is
127 00:06:09,479 --> 00:06:16,139 saying heading one cell style to B2
128 00:06:12,780 --> 00:06:17,580 so heading one cell Styles and then here
129 00:06:16,139 --> 00:06:20,460 is heading one
130 00:06:17,580 --> 00:06:23,580 and then click here and then right click
131 00:06:20,460 --> 00:06:27,319 on group sheets so let's check so yeah
132 00:06:23,580 --> 00:06:27,319 it is applied and it is applied
133 00:06:27,360 --> 00:06:30,979 so that's it thank you
