Module 5 - End of Module Project 1 #part1
Description
Please Subscribe, Please? Topics covered:
- 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 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.
- In cell D18, enter a formula using the SUM function to total the expense values using the defined range names PayrollExp and OpExp.
Support: Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar
For more questions, visit: https://taandar.com/subjects/business-spreadsheet
#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:01,979 --> 00:00:07,319 all right so this is module 5 project
2 00:00:04,440 --> 00:00:10,500 one so it is saying learning holds many
3 00:00:07,319 --> 00:00:12,420 different channel list two so go to the
4 00:00:10,500 --> 00:00:15,059 dashboard worksheet so we're on the same
5 00:00:12,420 --> 00:00:16,859 worksheet dashboard and lonely first one
6 00:00:15,059 --> 00:00:20,340 to complete the list of Link in the
7 00:00:16,859 --> 00:00:24,240 range S7 through S10 so here is the S7
8 00:00:20,340 --> 00:00:27,060 and through S10 okay and in S7 create a
9 00:00:24,240 --> 00:00:28,099 hyperlink to the website so let's copy
10 00:00:27,060 --> 00:00:30,480 this
11 00:00:28,099 --> 00:00:33,420 right click and hit cop you have to
12 00:00:30,480 --> 00:00:36,660 control C or command C and then click as
13 00:00:33,420 --> 00:00:39,120 7 as 7 and then to right click and here
14 00:00:36,660 --> 00:00:41,940 is the link and then in the first tab it
15 00:00:39,120 --> 00:00:43,860 is saying existing file or web page so
16 00:00:41,940 --> 00:00:46,620 click on this so it will be as default
17 00:00:43,860 --> 00:00:49,200 and then paste paste our Link in the
18 00:00:46,620 --> 00:00:52,020 address so hit Ctrl V command V or to
19 00:00:49,200 --> 00:00:55,620 right click any paste and then click ok
20 00:00:52,020 --> 00:00:57,719 so this is done and in as it create a
21 00:00:55,620 --> 00:01:00,260 hyperlink to this email address so let's
22 00:00:57,719 --> 00:01:03,480 copy this
23 00:01:00,260 --> 00:01:04,920 and then use contact of this as a screen
24 00:01:03,480 --> 00:01:08,280 tip text all
25 00:01:04,920 --> 00:01:09,960 so same process click on link
26 00:01:08,280 --> 00:01:12,479 and then this time it is email address
27 00:01:09,960 --> 00:01:14,280 so click on email address and then in
28 00:01:12,479 --> 00:01:16,320 email address
29 00:01:14,280 --> 00:01:18,119 paste it like this
30 00:01:16,320 --> 00:01:20,220 so while you paste it it will
31 00:01:18,119 --> 00:01:22,740 automatically add this prefix so it is
32 00:01:20,220 --> 00:01:26,100 okay don't try to remove it I think it
33 00:01:22,740 --> 00:01:29,460 is by mistake so it is saying and use
34 00:01:26,100 --> 00:01:30,960 this as a screen type text so just copy
35 00:01:29,460 --> 00:01:33,299 this
36 00:01:30,960 --> 00:01:35,880 and then click click here here is the
37 00:01:33,299 --> 00:01:37,079 screen tip so click here and then paste
38 00:01:35,880 --> 00:01:40,320 here
39 00:01:37,079 --> 00:01:42,360 so just paste here
40 00:01:40,320 --> 00:01:45,479 and you can just
41 00:01:42,360 --> 00:01:48,540 right click and then hit paste and then
42 00:01:45,479 --> 00:01:51,540 okay and then hit OK so this is also
43 00:01:48,540 --> 00:01:54,299 done in ads9 create a hyperlink to cell
44 00:01:51,540 --> 00:01:55,979 A1 so this is tricky in the categories
45 00:01:54,299 --> 00:01:58,140 worksheet so we have get over the
46 00:01:55,979 --> 00:02:01,280 worksheet in term here so this category
47 00:01:58,140 --> 00:02:01,280 and we need to
48 00:02:01,640 --> 00:02:09,239 link to go to the cell A1 so here is the
49 00:02:06,180 --> 00:02:11,700 place in this document and then
50 00:02:09,239 --> 00:02:14,580 it is saying where
51 00:02:11,700 --> 00:02:17,040 in A1 in category is okay so click on
52 00:02:14,580 --> 00:02:19,500 categories worksheet and it is there a
53 00:02:17,040 --> 00:02:20,940 one by triple so no need to edit and
54 00:02:19,500 --> 00:02:23,900 then it is not saying anything about
55 00:02:20,940 --> 00:02:27,959 screen type so hit OK
56 00:02:23,900 --> 00:02:30,000 and step four in cell uh as then create
57 00:02:27,959 --> 00:02:31,319 a hyperlink to the workbook so you
58 00:02:30,000 --> 00:02:33,660 should have downloaded this while
59 00:02:31,319 --> 00:02:37,739 downloading these files uh which
60 00:02:33,660 --> 00:02:42,660 contains uh 11 profit level and profit
61 00:02:37,739 --> 00:02:46,519 whatever so in cell as train as stain it
62 00:02:42,660 --> 00:02:49,260 right right click and click on link
63 00:02:46,519 --> 00:02:51,540 and then create a hyperlink to this
64 00:02:49,260 --> 00:02:53,940 workbook so click on existing file or
65 00:02:51,540 --> 00:02:56,760 workplace and then look at click click
66 00:02:53,940 --> 00:02:59,940 here and then locate that file so
67 00:02:56,760 --> 00:03:01,440 support yeah this is the file and then
68 00:02:59,940 --> 00:03:05,580 double click and then it will appear
69 00:03:01,440 --> 00:03:08,400 here in the address and then hit OK
70 00:03:05,580 --> 00:03:10,680 which content so yeah this is done so
71 00:03:08,400 --> 00:03:13,920 let's go to step five so we'll only want
72 00:03:10,680 --> 00:03:17,060 to complete uh 2021 rupee to lost a
73 00:03:13,920 --> 00:03:20,459 total add so open these
74 00:03:17,060 --> 00:03:22,260 and copy the value in the range so let's
75 00:03:20,459 --> 00:03:23,340 open this we can easily open this by
76 00:03:22,260 --> 00:03:25,319 clicking here
77 00:03:23,340 --> 00:03:27,840 click here and then that's will be open
78 00:03:25,319 --> 00:03:30,480 if you have downloaded if you haven't
79 00:03:27,840 --> 00:03:33,620 downloaded it once again go to that same
80 00:03:30,480 --> 00:03:37,260 as the web page and then download this
81 00:03:33,620 --> 00:03:40,019 uh copy the value in the range this in
82 00:03:37,260 --> 00:03:43,700 the cell C6 of the transport oxide so
83 00:03:40,019 --> 00:03:49,019 let's copy C6 through c19 so this is C6
84 00:03:43,700 --> 00:03:52,280 through c19 so right click and copy what
85 00:03:49,019 --> 00:03:54,299 is copy okay let's go
86 00:03:52,280 --> 00:03:57,420 minimize it
87 00:03:54,299 --> 00:04:00,360 and then it is saying uh industrial C6
88 00:03:57,420 --> 00:04:03,959 of gas board worksheet so dashboard
89 00:04:00,360 --> 00:04:08,280 worksheet in the cell
90 00:04:03,959 --> 00:04:11,040 C6 so here is the C6
91 00:04:08,280 --> 00:04:14,459 I use the paste link command to create a
92 00:04:11,040 --> 00:04:17,100 external uh reference to the value these
93 00:04:14,459 --> 00:04:18,900 delete the unnecessary valuable so we
94 00:04:17,100 --> 00:04:21,299 should we shall not paste it regular way
95 00:04:18,900 --> 00:04:23,759 we should paste it uh in a link link
96 00:04:21,299 --> 00:04:26,160 format so here are the options so this
97 00:04:23,759 --> 00:04:28,740 is the paste link so you need to click
98 00:04:26,160 --> 00:04:31,080 here and it will paste like this
99 00:04:28,740 --> 00:04:33,240 so just click on this cell and then to
100 00:04:31,080 --> 00:04:36,600 right click and then click here so and
101 00:04:33,240 --> 00:04:38,820 it will do the job uh and then it is
102 00:04:36,600 --> 00:04:42,960 saying delete the unnecessary value in
103 00:04:38,820 --> 00:04:45,780 cell C8 C12 and c16 so delete these
104 00:04:42,960 --> 00:04:49,500 delete these and then delete
105 00:04:45,780 --> 00:04:51,419 this so okay step 5 is done you only
106 00:04:49,500 --> 00:04:54,000 need a quick way to refer to the budget
107 00:04:51,419 --> 00:04:58,020 payroll and operating expenses use the
108 00:04:54,000 --> 00:05:00,780 name payroll EXP
109 00:04:58,020 --> 00:05:06,000 so let's copy this text to define the
110 00:05:00,780 --> 00:05:09,000 range D9 through d11 so D9 D9 through
111 00:05:06,000 --> 00:05:11,220 d11 again right click and then here is
112 00:05:09,000 --> 00:05:13,040 the Define name click here and then in
113 00:05:11,220 --> 00:05:15,780 the name
114 00:05:13,040 --> 00:05:18,180 exp I'll just click copy and paste it
115 00:05:15,780 --> 00:05:19,979 and it needs to refer to D9 through D
116 00:05:18,180 --> 00:05:21,180 level of dashboard
117 00:05:19,979 --> 00:05:24,479 okay
118 00:05:21,180 --> 00:05:27,060 so hit OK and this is done and then also
119 00:05:24,479 --> 00:05:28,639 use the name opexp to define the range
120 00:05:27,060 --> 00:05:34,020 G13 through
121 00:05:28,639 --> 00:05:37,440 D15 so the 13 oops this is little thing
122 00:05:34,020 --> 00:05:41,100 through the 15 and then click right
123 00:05:37,440 --> 00:05:44,539 click Define name that is saying o p e x
124 00:05:41,100 --> 00:05:49,259 p and then hit OK
125 00:05:44,539 --> 00:05:51,300 and now in Step 7 in cell d18 entered a
126 00:05:49,259 --> 00:05:53,300 formula using a sum function to Total
127 00:05:51,300 --> 00:05:57,840 the expense value using the defined name
128 00:05:53,300 --> 00:06:00,419 purely XP and op XP so in d18
129 00:05:57,840 --> 00:06:02,400 use some function so enter any function
130 00:06:00,419 --> 00:06:05,220 but today any mathematical calculation
131 00:06:02,400 --> 00:06:08,460 which will start from equals to sine and
132 00:06:05,220 --> 00:06:10,740 then enter sum the casing doesn't matter
133 00:06:08,460 --> 00:06:13,800 you can enter any small or lower or
134 00:06:10,740 --> 00:06:15,539 alternate anything so and then do open
135 00:06:13,800 --> 00:06:19,639 and close parenthesis and inside this
136 00:06:15,539 --> 00:06:22,940 parenthesis uh enter payroll
137 00:06:19,639 --> 00:06:26,280 payroll because we defined the the name
138 00:06:22,940 --> 00:06:32,340 we Define this range by the name pero
139 00:06:26,280 --> 00:06:34,680 and then some from payroll to o p e x p
140 00:06:32,340 --> 00:06:36,600 and so this will calculate this total
141 00:06:34,680 --> 00:06:39,780 sum and then this total sum and it will
142 00:06:36,600 --> 00:06:41,520 give the final result in d18 and then
143 00:06:39,780 --> 00:06:44,580 hit enter
144 00:06:41,520 --> 00:06:46,680 so yeah this is our required answer
145 00:06:44,580 --> 00:06:49,400 so we'll continue from other step in
146 00:06:46,680 --> 00:06:49,400 toggle video
