Module 3 - SAM Project 1a #part1
Description
Please Subscribe, Please?
Part 2: https://www.youtube.com/watch?v=gSsgpBb_HVM
Topics covered in the video:
- Danilo Lewellen is an entrepreneur planning to manufacture and market an innovative handheld controller for virtual reality gaming. Danilo is building a business plan as he recruits investors and plans logistics for the launch of his new company, Scrub Jay Peripherals, and wants to use Excel to summarize data and create projections.
Switch to the Sales Projections worksheet. Use the values in the range B6:C6 to extend the incremented percentages to the range D6:H6. 2. Use the values in the range A7:A8 to extend the list of years to the range A9:A11. 3. Use AutoFill to fill the range A9:H11 with the formatting from the range A7:H8. 4. In cell B8, create a formula without using a function that multiplies the value in cell B7 by the value in cell B6 and then adds the value in B7. In this formula, use a mixed reference for cell B6 by including an absolute reference to row 6. Copy the formula from cell B8 to the range B9:B11 and then copy the formulas from the range B8:B11 to the range C8:H11. 5. Switch to the Cost Estimates worksheet. In cell A9, create a formula using the AVERAGE function that calculates the average of the values in the range A5:A7, then copy your formula to cell D9. 6. In cell A10, create a formula using the MAX function that identifies the maximum value in the range A5:A7 and then copy your formula to cell D10. 7. In cell A11, create a formula using the MIN function that identifies the minimum value in the range A5:A7 and then copy your formula to cell D11. 8. In cell B13, create a formula using the VLOOKUP function that looks up the value from cell A11 in the range A5:B7, returns the value in column 2, and specifies an exact match. Copy the formula to cell E13.
#cengage #solutions #sam ---
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:05,520 so this is module 3 project 1A so in
2 00:00:02,820 --> 00:00:07,560 this Step 1 it is saying switch to the
3 00:00:05,520 --> 00:00:10,679 sales projects and worksheet use the
4 00:00:07,560 --> 00:00:12,840 value in the range B6 through C6 to
5 00:00:10,679 --> 00:00:17,039 extend the incremental percentage to
6 00:00:12,840 --> 00:00:19,380 range D6 through S6 so from
7 00:00:17,039 --> 00:00:21,660 B6 through C6
8 00:00:19,380 --> 00:00:24,480 going to use this so go to the corner
9 00:00:21,660 --> 00:00:28,500 and this plus sign will appear now hold
10 00:00:24,480 --> 00:00:29,460 it and drag through D6 through F6 so up
11 00:00:28,500 --> 00:00:32,279 to here
12 00:00:29,460 --> 00:00:35,520 so this this this plus sign will appear
13 00:00:32,279 --> 00:00:38,219 hold it and drag it until S6
14 00:00:35,520 --> 00:00:41,579 so it will auto fill after the releasing
15 00:00:38,219 --> 00:00:45,480 use the value in the range A7 through A8
16 00:00:41,579 --> 00:00:47,460 so is seven through eight and to extend
17 00:00:45,480 --> 00:00:52,739 the list of the year in the range A9
18 00:00:47,460 --> 00:00:55,440 through a11 so A9 through 11.
19 00:00:52,739 --> 00:00:57,120 same method just just go to the corner
20 00:00:55,440 --> 00:01:01,320 this type of plus sign will appear and
21 00:00:57,120 --> 00:01:01,320 hold it and drag it until 11.
22 00:01:01,620 --> 00:01:07,320 so after that use autofill to fill the
23 00:01:04,500 --> 00:01:09,900 range a 9 through S11 with the
24 00:01:07,320 --> 00:01:11,040 formatting from the range A7 through
25 00:01:09,900 --> 00:01:13,200 acid
26 00:01:11,040 --> 00:01:15,960 so A7
27 00:01:13,200 --> 00:01:18,360 now hold shift and then click here
28 00:01:15,960 --> 00:01:20,820 and it will select all and it is saying
29 00:01:18,360 --> 00:01:24,720 use this to fill the range A9 through
30 00:01:20,820 --> 00:01:26,040 h11 so A9 3x11 same process just go to
31 00:01:24,720 --> 00:01:29,880 the government plus sign will appear
32 00:01:26,040 --> 00:01:32,939 hold it and drag it until S11
33 00:01:29,880 --> 00:01:34,680 and then release it after that in cell
34 00:01:32,939 --> 00:01:37,079 B8
35 00:01:34,680 --> 00:01:39,060 initial we'll create a formula using a
36 00:01:37,079 --> 00:01:42,960 function that multiplies the value in
37 00:01:39,060 --> 00:01:44,880 cell B7 by the value in cell B6
38 00:01:42,960 --> 00:01:48,500 so
39 00:01:44,880 --> 00:01:52,220 equals to sign after that V7
40 00:01:48,500 --> 00:01:52,220 by B6
41 00:01:52,680 --> 00:01:57,619 and then adds the value in B7
42 00:01:58,079 --> 00:02:02,700 okay
43 00:01:59,700 --> 00:02:04,159 V7 in this formula use the mixed
44 00:02:02,700 --> 00:02:07,380 reference for
45 00:02:04,159 --> 00:02:09,000 B6 by including an absolute reference to
46 00:02:07,380 --> 00:02:12,599 row six
47 00:02:09,000 --> 00:02:15,420 so for B6 going to do the absolute
48 00:02:12,599 --> 00:02:17,400 reference for row 6 so just for row 6
49 00:02:15,420 --> 00:02:18,660 into 100 dollars and it will stay
50 00:02:17,400 --> 00:02:20,420 constant
51 00:02:18,660 --> 00:02:23,340 and then
52 00:02:20,420 --> 00:02:24,720 row six okay so this is done and hit
53 00:02:23,340 --> 00:02:27,300 enter
54 00:02:24,720 --> 00:02:29,760 after that in this formula so this is
55 00:02:27,300 --> 00:02:32,580 done complete the formula from copy the
56 00:02:29,760 --> 00:02:36,480 formula from p8 to the range B9 through
57 00:02:32,580 --> 00:02:39,599 b11 so click on V8 again plus PSI plus
58 00:02:36,480 --> 00:02:43,019 sign in B 9 through v11
59 00:02:39,599 --> 00:02:44,940 after that it is saying and then copy
60 00:02:43,019 --> 00:02:49,920 the formula from the range B8 through
61 00:02:44,940 --> 00:02:52,379 b11 to the range C8 through h11 so
62 00:02:49,920 --> 00:02:53,959 go to the kernel plus signal up here and
63 00:02:52,379 --> 00:02:57,180 then drag it until
64 00:02:53,959 --> 00:02:58,980 c83 has 11 so until here
65 00:02:57,180 --> 00:03:01,620 now it will auto fill out the value
66 00:02:58,980 --> 00:03:03,840 switch to the cost estimates worksheet
67 00:03:01,620 --> 00:03:07,800 so let's do it
68 00:03:03,840 --> 00:03:10,019 and then in cell A9 so A9
69 00:03:07,800 --> 00:03:11,700 create a formula using average function
70 00:03:10,019 --> 00:03:14,940 that calculates
71 00:03:11,700 --> 00:03:16,680 so every time we enter formula or any
72 00:03:14,940 --> 00:03:20,099 mathematical calculation we shall start
73 00:03:16,680 --> 00:03:23,940 with equals to sign and then enter
74 00:03:20,099 --> 00:03:26,340 average so this is case insensitive so
75 00:03:23,940 --> 00:03:28,080 it doesn't matter if we type in capital
76 00:03:26,340 --> 00:03:30,840 case or lowercase
77 00:03:28,080 --> 00:03:33,959 or anything and then that calculates the
78 00:03:30,840 --> 00:03:37,920 average value in the range A5 through A7
79 00:03:33,959 --> 00:03:41,580 so just enter the range A5 through A7
80 00:03:37,920 --> 00:03:44,760 and then hit enter and then copy your
81 00:03:41,580 --> 00:03:49,680 formula to cell D9
82 00:03:44,760 --> 00:03:51,720 so just copy it Ctrl C and then Ctrl V
83 00:03:49,680 --> 00:03:55,560 and then hit Escape
84 00:03:51,720 --> 00:03:58,980 after that in cell 18 create a formula
85 00:03:55,560 --> 00:04:01,739 using the max function so cell a 10
86 00:03:58,980 --> 00:04:04,739 again go to here equals to sign
87 00:04:01,739 --> 00:04:07,920 Max function and it accepts
88 00:04:04,739 --> 00:04:11,840 uh which number so that identify maximum
89 00:04:07,920 --> 00:04:16,799 value in the range a 5 through A7
90 00:04:11,840 --> 00:04:18,720 formula to d11 so just copy it and then
91 00:04:16,799 --> 00:04:22,019 paste it
92 00:04:18,720 --> 00:04:24,960 after that in cell a11 create a formula
93 00:04:22,019 --> 00:04:26,100 using a mean function so again win
94 00:04:24,960 --> 00:04:30,300 function
95 00:04:26,100 --> 00:04:32,100 that identify A5 through A7
96 00:04:30,300 --> 00:04:36,440 same thing
97 00:04:32,100 --> 00:04:36,440 so copies and then paste it
98 00:04:36,900 --> 00:04:42,180 so after that it is saying in cell B13
99 00:04:40,020 --> 00:04:43,199 create a formula using the vlookup
100 00:04:42,180 --> 00:04:47,940 function
101 00:04:43,199 --> 00:04:50,940 okay so peer lookup function that looks
102 00:04:47,940 --> 00:04:55,860 up the value from cell a11 so lookup
103 00:04:50,940 --> 00:04:59,280 value is 11 and then a 5 through B7 and
104 00:04:55,860 --> 00:05:02,100 then in column 2 that is specify and
105 00:04:59,280 --> 00:05:05,280 specifies an exact match
106 00:05:02,100 --> 00:05:06,300 is intervals and then close the
107 00:05:05,280 --> 00:05:08,220 parenthesis
108 00:05:06,300 --> 00:05:11,639 and then hit enter
109 00:05:08,220 --> 00:05:15,440 and then copy the formula to cell e13 so
110 00:05:11,639 --> 00:05:15,440 just copy it and then paste it here
