Module 8: End of Module Project 2
Description
Please Subscribe, Please? 1. Fernanda Oliveira is a financial analyst for Expo Events, a full-service trade show and exhibition company headquartered in Orlando, Florida. Fernanda created a workbook containing details about events scheduled for March this year. She asks you to help analyze the data to determine how the company can increase profits. Switch to the Builders worksheet. Fernanda wants to calculate the revenue, expenses, and profit based on the number of people attending the Builders Trade Show. She has already set up a structure for a one-variable data table. In the range E5:H10, insert a one-variable data table, using the number of attendees (cell C4) as the Column input cell. 2. In cell E14, apply a custom format to display the text Attendees/Fee in place of the cell value. 3. Switch to the GymWear worksheet. Create a Scatter with Straight Lines chart based on the range E4:G14 in the data table titled GymWear - Break-Even Analysis. Resize and move the chart so that it covers the range E15:H27. Remove the chart title. Add Sales and Expenses as the vertical axis title and Attendees as the horizontal axis title. 4. Fernanda wants to change the bounds of the chart to better determine the break-even point. Change the Minimum Bounds of the vertical axis to 120,000 and let the Maximum Bounds change automatically to 210,000. Change the Number format of the vertical axis to Currency with 0 decimal places and $ as the symbol. Change the Minimum Bounds of the horizontal axis to 1,500 and let the Maximum Bounds change automatically to 2,500. 5. ..In the Scenario Manager, add a scenario named using the data shown in bold in Table 1. The changing cells are the nonadjacent cells C4, C10, and C13. Table 1: GymWear Scenario Values
Scenario Name Add Exhibits Attendees (C4) 2200 Variable_cost_per_attendee (C10) 53 Total_fixed_cost (C13) 52,400
- One of the values in the Extra Day scenario is incorrect. Edit the Extra Day scenario to use 55,000 as the Total_fixed_cost value. Show the Extra Day scenario, and then close the Scenario Manager.
- Switch to the Logistics worksheet. Edit the chart series names by using cell F5 as the series name for Series 1. For Series 2, use cell G5 as the series name. For Series 3, use cell H5 as the series name. For Series 4, use cell I5 as the series name. For Series 5, use cell J5 as the series name.
- Switch to the Service Providers worksheet. Run Solver and set the objective as minimizing the total costs (cell F10). Use the hours provided for each company (range C4:E4) as the changing variable cells. Adjust the hours provided by each company using the following constraints: · F4 is equal to 192, the total hours of event services provided. · F10 is less than or equal to 48,000, the maximum amount Fernanda can pay to the other event services companies. · C4:E4 is less than or equal to 68, the maximum hours provided by a single event services company. · C4:E4 should be an Integer. Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model to the range B14:B21.
- Fernanda wants to document the answer Solver found, including the constraints and a list of the values Solver changed to solve the problem. Solve the model again, this time choosing to produce an Answer report. Use Services Answer Report as the name of the worksheet containing the Answer report.
- Switch to the March worksheet, which compares the data for the three events the company is handling in March. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Current, Increase Fee, and Contractors scenarios. Use the range C16:E16 as the result cells.
- Switch back to the March worksheet. Use the Scenario Manager to create a Scenario PivotTable report for result cells C16:E16. Change the Number format of the value fields (Profit_Attendee_Builders, Profit_Attendee_GymWear, and Profit_Attendee_Logistics) to Currency with 2 decimal places and $ as the symbol.
- Add a Clustered Column PivotChart to the Scenario PivotTable worksheet, based on the Scenario PivotTable report. Resize and reposition the chart so that it covers the range A8:C21.
#excel #sam #cengage #solutions
Disclaimer: Our videos are intended solely for educational purposes, with the goal of assisting students in understanding how to solve problems in various courses. We want to emphasize that any attempt to copy or replicate the solutions shown in these videos may be regarded as a breach of academic integrity policies at your college or university. We strongly recommend that you use these videos as a guide and as a tool to support your learning, but we urge you to complete your assignments independently and refrain from cheating or plagiarism. Please be aware that we are not liable for any consequences resulting from the misuse of these videos in violation of academic integrity policies.
Transcript
1 00:00:02,399 --> 00:00:07,379 all right so this is module 8 in the
2 00:00:04,680 --> 00:00:10,139 module project 2 and here in this step
3 00:00:07,379 --> 00:00:13,200 one it is saying Fernanda Oliver is a
4 00:00:10,139 --> 00:00:15,120 financial analyst for the experience and
5 00:00:13,200 --> 00:00:17,400 then let me skip this and it is saying
6 00:00:15,120 --> 00:00:20,279 switch to the Builder's worksheet so on
7 00:00:17,400 --> 00:00:21,840 the button click on Builders tab and
8 00:00:20,279 --> 00:00:23,640 then it is saying finder wants to
9 00:00:21,840 --> 00:00:25,140 calculate the revenue expenses and
10 00:00:23,640 --> 00:00:28,800 profit based on the number of people
11 00:00:25,140 --> 00:00:31,560 attending the builders trade so okay so
12 00:00:28,800 --> 00:00:34,320 Builder state so she has already created
13 00:00:31,560 --> 00:00:37,620 she has already set up a structure for a
14 00:00:34,320 --> 00:00:39,899 one variable data table okay in range S5
15 00:00:37,620 --> 00:00:42,660 through S10 insert one variable data
16 00:00:39,899 --> 00:00:44,820 table so in range
17 00:00:42,660 --> 00:00:48,360 E5
18 00:00:44,820 --> 00:00:51,780 through S10 one validator using a number
19 00:00:48,360 --> 00:00:53,780 of attendees C4 has a colonial so in one
20 00:00:51,780 --> 00:00:57,899 variable we just need to enter one value
21 00:00:53,780 --> 00:01:00,059 so go to data Tab and then under
22 00:00:57,899 --> 00:01:02,579 forecast group click on what if analysis
23 00:01:00,059 --> 00:01:04,440 and then click on data table and then we
24 00:01:02,579 --> 00:01:07,320 just need to enter one value in the
25 00:01:04,440 --> 00:01:10,760 column input cell and that will be C4
26 00:01:07,320 --> 00:01:10,760 and then hit OK
27 00:01:19,580 --> 00:01:24,420 this number I'm going to display that
28 00:01:22,380 --> 00:01:26,820 text and for that go to
29 00:01:24,420 --> 00:01:28,799 for Mid cell do right click and then go
30 00:01:26,820 --> 00:01:31,020 to format cell and then on the custom
31 00:01:28,799 --> 00:01:33,960 tab under category group
32 00:01:31,020 --> 00:01:37,079 scroll down and select the attendees and
33 00:01:33,960 --> 00:01:42,180 then edit this just add on slash and
34 00:01:37,079 --> 00:01:44,640 then fields and then hit OK all right
35 00:01:42,180 --> 00:01:46,439 now step three it is saying switch to
36 00:01:44,640 --> 00:01:48,840 the gym World worksheet so on the bottom
37 00:01:46,439 --> 00:01:51,540 click on GMod Tab and then create a
38 00:01:48,840 --> 00:01:56,159 scatter with straight line chart based
39 00:01:51,540 --> 00:01:57,299 on the Range E4 through z14 in the data
40 00:01:56,159 --> 00:02:01,140 table okay
41 00:01:57,299 --> 00:02:04,979 so for E4
42 00:02:01,140 --> 00:02:07,200 E4 through G14
43 00:02:04,979 --> 00:02:09,539 all right it is saying scatter with
44 00:02:07,200 --> 00:02:12,180 straight lines are so for that go to
45 00:02:09,539 --> 00:02:14,879 insert and then on the chart Google
46 00:02:12,180 --> 00:02:16,860 group here is the scanner
47 00:02:14,879 --> 00:02:19,860 oops
48 00:02:16,860 --> 00:02:22,200 yes they scattered and then go to the
49 00:02:19,860 --> 00:02:25,260 lines with straight line
50 00:02:22,200 --> 00:02:28,080 and then click here
51 00:02:25,260 --> 00:02:31,739 all right now
52 00:02:28,080 --> 00:02:35,760 so in this and then it is saying move to
53 00:02:31,739 --> 00:02:37,080 the E15 through S70 okay so here is the
54 00:02:35,760 --> 00:02:40,739 E15
55 00:02:37,080 --> 00:02:43,340 and then it is saying s27
56 00:02:40,739 --> 00:02:46,680 okay so this is it
57 00:02:43,340 --> 00:02:50,099 and then his current similar stick yeah
58 00:02:46,680 --> 00:02:52,800 s27 so you remove the chart title okay
59 00:02:50,099 --> 00:02:55,040 and as the vertical axis so click on the
60 00:02:52,800 --> 00:02:57,900 title right click and then you delete
61 00:02:55,040 --> 00:02:59,700 and it is saying add sales and expresses
62 00:02:57,900 --> 00:03:01,800 at the vertical axis title Advantage as
63 00:02:59,700 --> 00:03:04,980 the horizontal axis title so for that
64 00:03:01,800 --> 00:03:06,900 click on this plus plus sign and then
65 00:03:04,980 --> 00:03:09,959 check mark this axis title so it will
66 00:03:06,900 --> 00:03:14,599 add horizontal vertical and horizontal
67 00:03:09,959 --> 00:03:14,599 okay so for the vertical it was saying
68 00:03:15,239 --> 00:03:20,640 sales and
69 00:03:17,640 --> 00:03:23,220 expenses
70 00:03:20,640 --> 00:03:26,099 and then for
71 00:03:23,220 --> 00:03:29,720 horizontal it is same
72 00:03:26,099 --> 00:03:29,720 add in this right
73 00:03:30,300 --> 00:03:34,019 now step four reducing for another one
74 00:03:32,519 --> 00:03:35,580 to change the bound double turn to
75 00:03:34,019 --> 00:03:37,920 better determine the break even Point
76 00:03:35,580 --> 00:03:42,360 change the minimum bounce of the
77 00:03:37,920 --> 00:03:44,819 vertical axis to 120 000 so double click
78 00:03:42,360 --> 00:03:46,260 on it and this section will appear or if
79 00:03:44,819 --> 00:03:48,659 it doesn't if that does not appear on
80 00:03:46,260 --> 00:03:50,700 double click you can go to plus sign and
81 00:03:48,659 --> 00:03:52,560 then go to any of this arrow and then
82 00:03:50,700 --> 00:03:54,780 click on more option and then again
83 00:03:52,560 --> 00:03:57,120 these option will help you after that
84 00:03:54,780 --> 00:04:00,420 click on this drop down and then
85 00:03:57,120 --> 00:04:02,099 it is saying a vertical axis so minimum
86 00:04:00,420 --> 00:04:03,659 amount of vertical axis that's why click
87 00:04:02,099 --> 00:04:06,180 on particle axis
88 00:04:03,659 --> 00:04:08,700 and then again click on this icon and
89 00:04:06,180 --> 00:04:11,760 then click on this drop down axis option
90 00:04:08,700 --> 00:04:13,340 drop down and then on minimum bounce it
91 00:04:11,760 --> 00:04:14,939 is saying
92 00:04:13,340 --> 00:04:16,979 120
93 00:04:14,939 --> 00:04:18,840 000 and then hit OK
94 00:04:16,979 --> 00:04:20,459 and the reason due to the maximum bounce
95 00:04:18,840 --> 00:04:22,979 in the automatically to
96 00:04:20,459 --> 00:04:24,660 this okay so this is done and then
97 00:04:22,979 --> 00:04:27,419 change the number format of the vertical
98 00:04:24,660 --> 00:04:28,560 axis to currency with zero decimal
99 00:04:27,419 --> 00:04:31,380 places
100 00:04:28,560 --> 00:04:34,560 all right so it is saying a vertical
101 00:04:31,380 --> 00:04:35,820 axis so we are on the same axis now go
102 00:04:34,560 --> 00:04:37,979 to numbers
103 00:04:35,820 --> 00:04:41,100 and then
104 00:04:37,979 --> 00:04:42,720 is the currency so it is saying
105 00:04:41,100 --> 00:04:45,419 currency
106 00:04:42,720 --> 00:04:47,340 with zero decimal places and dollar
107 00:04:45,419 --> 00:04:49,440 assemble okay
108 00:04:47,340 --> 00:04:53,580 and then it is saying change the minimum
109 00:04:49,440 --> 00:04:56,040 bound to the horizontal axis to 1500.
110 00:04:53,580 --> 00:04:58,080 all right now select on this drop down
111 00:04:56,040 --> 00:04:59,160 and then we need to go to horizontal
112 00:04:58,080 --> 00:05:01,919 axis
113 00:04:59,160 --> 00:05:03,419 and then it is saying change the minimum
114 00:05:01,919 --> 00:05:05,220 bound of
115 00:05:03,419 --> 00:05:08,460 okay
116 00:05:05,220 --> 00:05:09,120 so delete this and then 15 on there and
117 00:05:08,460 --> 00:05:11,639 then
118 00:05:09,120 --> 00:05:13,979 and then change the maximum bound of the
119 00:05:11,639 --> 00:05:16,620 horizontal axis and then maximum bound
120 00:05:13,979 --> 00:05:17,759 change automatically 2500 okay so this
121 00:05:16,620 --> 00:05:21,960 is changed
122 00:05:17,759 --> 00:05:24,600 now you can close this
123 00:05:21,960 --> 00:05:28,680 okay so another one way to increase
124 00:05:24,600 --> 00:05:31,440 profit is to run the Gmod trade so on so
125 00:05:28,680 --> 00:05:34,259 so for an extra day another way is to
126 00:05:31,440 --> 00:05:36,660 add exhibits so Foreigner has already
127 00:05:34,259 --> 00:05:39,120 created a scenario named extra day and
128 00:05:36,660 --> 00:05:41,759 who wants to create another for adding
129 00:05:39,120 --> 00:05:44,100 exhibit in the scenario manager add a
130 00:05:41,759 --> 00:05:46,340 scenario named the data shown in the
131 00:05:44,100 --> 00:05:46,340 Bold
132 00:05:54,600 --> 00:06:01,500 okay so for that go to data Tab and then
133 00:05:59,039 --> 00:06:05,639 under forecast group here is vertical
134 00:06:01,500 --> 00:06:08,100 analysis and then it is saying
135 00:06:05,639 --> 00:06:10,680 okay scenario so for that go to scenario
136 00:06:08,100 --> 00:06:14,100 manager and then currently we just have
137 00:06:10,680 --> 00:06:16,560 extra day and we want to add another so
138 00:06:14,100 --> 00:06:18,660 the scenario name will be add exhibits
139 00:06:16,560 --> 00:06:21,600 which is given in this table just copy
140 00:06:18,660 --> 00:06:25,199 on it and then click on ADD
141 00:06:21,600 --> 00:06:28,440 itional your name will be and Exhibits
142 00:06:25,199 --> 00:06:31,259 and then changing cells will be C4 C10
143 00:06:28,440 --> 00:06:33,060 C13 so we don't need to change it and
144 00:06:31,259 --> 00:06:35,720 then after that hit OK
145 00:06:33,060 --> 00:06:39,360 so I'd rather attend these will be
146 00:06:35,720 --> 00:06:42,660 2200 and then variable cost for
147 00:06:39,360 --> 00:06:46,620 identities will be 53
148 00:06:42,660 --> 00:06:48,199 and then total fixed cost will be 52.
149 00:06:46,620 --> 00:06:51,539 52
150 00:06:48,199 --> 00:06:53,819 400. now hit OK
151 00:06:51,539 --> 00:06:56,819 all right so one of the values in the
152 00:06:53,819 --> 00:06:59,340 extra day scenario is input so here is
153 00:06:56,819 --> 00:07:02,400 extra day uh edit the extra day scenario
154 00:06:59,340 --> 00:07:04,020 to use 5500 so click on this and then
155 00:07:02,400 --> 00:07:09,060 click on edit
156 00:07:04,020 --> 00:07:12,120 so 55 1000 so again click ok
157 00:07:09,060 --> 00:07:15,199 55 000 as the total fixed okay so
158 00:07:12,120 --> 00:07:18,259 currently it's 50 grams now change to
159 00:07:15,199 --> 00:07:21,479 55 grants okay
160 00:07:18,259 --> 00:07:24,960 so the extra day scenario and then
161 00:07:21,479 --> 00:07:27,660 okay after that okay and it is saying so
162 00:07:24,960 --> 00:07:30,720 the extra day so for that click here and
163 00:07:27,660 --> 00:07:33,419 then click on so
164 00:07:30,720 --> 00:07:36,060 and now hit close
165 00:07:33,419 --> 00:07:38,099 all right noticing switch to the
166 00:07:36,060 --> 00:07:39,240 logistic worksheet so on the bottom
167 00:07:38,099 --> 00:07:42,180 click here
168 00:07:39,240 --> 00:07:44,400 and then it is saying uh 500 wants to
169 00:07:42,180 --> 00:07:46,919 clarify what each line means in the
170 00:07:44,400 --> 00:07:49,620 scatter with scatter line start in the
171 00:07:46,919 --> 00:07:51,599 range E15 through j28
172 00:07:49,620 --> 00:07:55,380 so here is the third
173 00:07:51,599 --> 00:07:58,440 and then using edit this third series
174 00:07:55,380 --> 00:08:00,900 Name by using cell J5 as the series name
175 00:07:58,440 --> 00:08:03,120 for series one okay so to edit the
176 00:08:00,900 --> 00:08:05,819 series name just click on the tab click
177 00:08:03,120 --> 00:08:07,680 on the chart right click and then click
178 00:08:05,819 --> 00:08:10,560 on select data
179 00:08:07,680 --> 00:08:12,180 okay so here is the series one now click
180 00:08:10,560 --> 00:08:16,020 on edit
181 00:08:12,180 --> 00:08:17,099 and then first series name it wants
182 00:08:16,020 --> 00:08:19,199 um
183 00:08:17,099 --> 00:08:22,080 okay it wants yeah five
184 00:08:19,199 --> 00:08:26,639 so click on F5
185 00:08:22,080 --> 00:08:29,280 oops this is F6 so it should be F5
186 00:08:26,639 --> 00:08:30,840 and then click ok
187 00:08:29,280 --> 00:08:35,279 all right
188 00:08:30,840 --> 00:08:37,800 so it did not sell it as add for F5
189 00:08:35,279 --> 00:08:41,180 so let me cancel this one
190 00:08:37,800 --> 00:08:41,180 and then f
191 00:08:42,120 --> 00:08:48,200 oops
192 00:08:44,700 --> 00:08:48,200 something is wrong here
193 00:08:49,500 --> 00:08:53,399 you canceled
194 00:08:50,880 --> 00:08:56,300 okay so again right click and then
195 00:08:53,399 --> 00:08:56,300 select data
196 00:08:56,399 --> 00:09:03,060 and then series one and then edit
197 00:09:00,600 --> 00:09:05,339 and then series name
198 00:09:03,060 --> 00:09:09,420 so for that
199 00:09:05,339 --> 00:09:11,580 click on F5 over okay so it worked when
200 00:09:09,420 --> 00:09:14,220 I maximize and then do right click and
201 00:09:11,580 --> 00:09:15,959 the same step where I maximize and I
202 00:09:14,220 --> 00:09:18,720 think it had more space and then it
203 00:09:15,959 --> 00:09:22,920 worked so go to series 1 and then same
204 00:09:18,720 --> 00:09:24,899 step edit and then go to F5 so now it is
205 00:09:22,920 --> 00:09:27,300 selectable so I don't know what happened
206 00:09:24,899 --> 00:09:29,760 before so I just maximized give more
207 00:09:27,300 --> 00:09:31,440 Express and then it would okay so now
208 00:09:29,760 --> 00:09:33,600 hit okay
209 00:09:31,440 --> 00:09:35,700 and then
210 00:09:33,600 --> 00:09:40,440 and then again hit OK
211 00:09:35,700 --> 00:09:44,519 so let's see so for Series 2 it's G5 and
212 00:09:40,440 --> 00:09:47,160 then S5 and then i5
213 00:09:44,519 --> 00:09:50,220 and then J5 okay
214 00:09:47,160 --> 00:09:52,380 so let's edit so again right click and
215 00:09:50,220 --> 00:09:55,380 then select data
216 00:09:52,380 --> 00:09:58,500 and then click on Series 2 and then edit
217 00:09:55,380 --> 00:10:02,880 so this time it will be G5
218 00:09:58,500 --> 00:10:04,500 and then hit OK so Series 3 Edit so it
219 00:10:02,880 --> 00:10:06,240 will be against five
220 00:10:04,500 --> 00:10:08,459 and then hit OK
221 00:10:06,240 --> 00:10:13,019 so for series 4
222 00:10:08,459 --> 00:10:14,700 again edit and then it will be
223 00:10:13,019 --> 00:10:16,500 I5
224 00:10:14,700 --> 00:10:20,880 and then hit OK
225 00:10:16,500 --> 00:10:22,320 Series 5 indeed it will be say five then
226 00:10:20,880 --> 00:10:26,100 hit OK
227 00:10:22,320 --> 00:10:28,620 and then at last it is okay all right
228 00:10:26,100 --> 00:10:30,360 so step seven is down now step 8 it is
229 00:10:28,620 --> 00:10:33,959 saying switch to the service providers
230 00:10:30,360 --> 00:10:36,839 worksheet oops so
231 00:10:33,959 --> 00:10:40,080 let's click here on the bottom and then
232 00:10:36,839 --> 00:10:41,700 for Fernando wants to determine whether
233 00:10:40,080 --> 00:10:44,640 partnering with other event Service
234 00:10:41,700 --> 00:10:47,720 Company would reduce overall experiences
235 00:10:44,640 --> 00:10:49,940 for a trade so so don't solver and
236 00:10:47,720 --> 00:10:53,160 objective as
237 00:10:49,940 --> 00:10:55,860 minimizing the total cost after okay so
238 00:10:53,160 --> 00:10:59,519 for solver
239 00:10:55,860 --> 00:11:01,620 so for solver go to data Tab and here
240 00:10:59,519 --> 00:11:03,480 you need to have this option so if you
241 00:11:01,620 --> 00:11:06,360 don't have this option so just go to
242 00:11:03,480 --> 00:11:07,740 search and then search add-ins and then
243 00:11:06,360 --> 00:11:10,260 click here
244 00:11:07,740 --> 00:11:11,940 and then make sure you check mark this
245 00:11:10,260 --> 00:11:13,260 solver and then click OK and this will
246 00:11:11,940 --> 00:11:15,120 appear here
247 00:11:13,260 --> 00:11:18,839 okay
248 00:11:15,120 --> 00:11:20,880 so it is saying run the solver okay so
249 00:11:18,839 --> 00:11:22,320 just go to this tab and then click on
250 00:11:20,880 --> 00:11:24,540 solver
251 00:11:22,320 --> 00:11:27,899 and then it is saying
252 00:11:24,540 --> 00:11:30,300 so objective objectives will be acting
253 00:11:27,899 --> 00:11:33,300 and then use the hours provided for each
254 00:11:30,300 --> 00:11:35,880 company and C4 through E4 at the
255 00:11:33,300 --> 00:11:38,519 changing variable so it is so it is
256 00:11:35,880 --> 00:11:42,180 saying minimizing so make sure you check
257 00:11:38,519 --> 00:11:45,360 this and then changing will be C4
258 00:11:42,180 --> 00:11:47,820 through E4
259 00:11:45,360 --> 00:11:53,220 so we're going to add this constant now
260 00:11:47,820 --> 00:11:57,200 F4 is equals to 192. now click on ADD
261 00:11:53,220 --> 00:12:00,600 and then it is saying yeah for
262 00:11:57,200 --> 00:12:04,920 equals to so it equals to and then
263 00:12:00,600 --> 00:12:07,440 constant will be 192. and then hit OK
264 00:12:04,920 --> 00:12:10,380 so this constraint is added now
265 00:12:07,440 --> 00:12:13,980 now again click on ADD and it is saying
266 00:12:10,380 --> 00:12:16,260 F10 social reference so we have 10 and
267 00:12:13,980 --> 00:12:18,660 then less than or equals to
268 00:12:16,260 --> 00:12:20,720 48
269 00:12:18,660 --> 00:12:24,000 000. okay
270 00:12:20,720 --> 00:12:25,980 so again click on ADD and it is saying
271 00:12:24,000 --> 00:12:27,079 C4
272 00:12:25,980 --> 00:12:30,480 through
273 00:12:27,079 --> 00:12:34,040 E4 is less than or equals to okay
274 00:12:30,480 --> 00:12:36,000 again less than or equals to
275 00:12:34,040 --> 00:12:38,279 68.
276 00:12:36,000 --> 00:12:40,860 so it will be 68
277 00:12:38,279 --> 00:12:43,320 and then here
278 00:12:40,860 --> 00:12:47,120 and it is saying C4 and E4 should be
279 00:12:43,320 --> 00:12:49,860 integer so okay again C 4 through E4
280 00:12:47,120 --> 00:12:51,779 click on this drop down and then select
281 00:12:49,860 --> 00:12:55,620 int and it will auto fill integer in
282 00:12:51,779 --> 00:12:56,700 constraint and then this is the last so
283 00:12:55,620 --> 00:12:58,920 hit OK
284 00:12:56,700 --> 00:13:00,600 now it is saying run solver keep the
285 00:12:58,920 --> 00:13:06,060 solution and then return to the solver
286 00:13:00,600 --> 00:13:08,700 parameter dialog box okay so solve
287 00:13:06,060 --> 00:13:11,040 okay so keep the solution and it is
288 00:13:08,700 --> 00:13:13,500 saying return to the solver solver
289 00:13:11,040 --> 00:13:16,579 parameter dial so make sure you check
290 00:13:13,500 --> 00:13:19,680 mark this and then hit OK
291 00:13:16,579 --> 00:13:23,339 now it is saying save the model to the
292 00:13:19,680 --> 00:13:26,040 range b14 through B21 okay so for that
293 00:13:23,339 --> 00:13:29,240 click on this load slash save
294 00:13:26,040 --> 00:13:31,620 and then make sure the range is v14
295 00:13:29,240 --> 00:13:33,959 through B21
296 00:13:31,620 --> 00:13:36,660 and then click on Save
297 00:13:33,959 --> 00:13:39,000 after that close this
298 00:13:36,660 --> 00:13:42,800 okay
299 00:13:39,000 --> 00:13:42,800 now it is saying foreign
300 00:13:55,680 --> 00:14:01,860 so again click on the solver
301 00:13:58,860 --> 00:14:04,320 and then run the server
302 00:14:01,860 --> 00:14:06,360 and releasing this time to produce the
303 00:14:04,320 --> 00:14:07,620 answer report so for the reports click
304 00:14:06,360 --> 00:14:10,560 on answer
305 00:14:07,620 --> 00:14:12,899 and then use Services answer report as
306 00:14:10,560 --> 00:14:16,560 the name and the name of the worksheet
307 00:14:12,899 --> 00:14:19,680 containing the answer report okay so and
308 00:14:16,560 --> 00:14:23,940 now hit OK
309 00:14:19,680 --> 00:14:25,740 all right now click close And it created
310 00:14:23,940 --> 00:14:27,959 answer report
311 00:14:25,740 --> 00:14:30,420 so now it is also saving the name of the
312 00:14:27,959 --> 00:14:32,880 file here make sure you change this to
313 00:14:30,420 --> 00:14:36,060 underscore 2 before doing this
314 00:14:32,880 --> 00:14:38,040 all right now it is saying use this as
315 00:14:36,060 --> 00:14:42,480 the name of the worksheet
316 00:14:38,040 --> 00:14:45,180 so right click and then rename
317 00:14:42,480 --> 00:14:46,740 and then here
318 00:14:45,180 --> 00:14:49,560 okay
319 00:14:46,740 --> 00:14:51,480 now stepping the same switch to the Mars
320 00:14:49,560 --> 00:14:54,660 worksheet okay
321 00:14:51,480 --> 00:14:56,399 so let's see Mars worksheet so it is now
322 00:14:54,660 --> 00:14:58,980 it is saying which compares the data for
323 00:14:56,399 --> 00:15:00,839 the three events of the three events the
324 00:14:58,980 --> 00:15:02,459 company is handling marks for
325 00:15:00,839 --> 00:15:04,440 undergraded three scenario on this
326 00:15:02,459 --> 00:15:05,339 worksheet to compare the current data
327 00:15:04,440 --> 00:15:07,199 with
328 00:15:05,339 --> 00:15:09,480 with one that will increase the station
329 00:15:07,199 --> 00:15:11,100 fee okay so it is saying use the
330 00:15:09,480 --> 00:15:14,100 scenario manager to create a scenario
331 00:15:11,100 --> 00:15:16,260 summary report all right so again go to
332 00:15:14,100 --> 00:15:18,600 data Tab and then under forecast group
333 00:15:16,260 --> 00:15:21,660 there is 45 analysis and then it is
334 00:15:18,600 --> 00:15:24,720 saying scenario manager okay
335 00:15:21,660 --> 00:15:27,000 so it is saying you have to create a
336 00:15:24,720 --> 00:15:29,600 scenario summary report online so for
337 00:15:27,000 --> 00:15:32,519 that click on the summary and then
338 00:15:29,600 --> 00:15:34,920 scenario summary report all right that
339 00:15:32,519 --> 00:15:38,339 summarizes okay interesting use range
340 00:15:34,920 --> 00:15:42,740 c16 through e16 as the result cell okay
341 00:15:38,339 --> 00:15:46,560 so results serial sold b c 16 through
342 00:15:42,740 --> 00:15:48,360 e16 and then hit OK
343 00:15:46,560 --> 00:15:52,139 it will take some time
344 00:15:48,360 --> 00:15:54,839 and then it will create this okay
345 00:15:52,139 --> 00:15:56,699 now it is back to the Mars worksheet you
346 00:15:54,839 --> 00:15:59,399 scenario manager to create a scenario
347 00:15:56,699 --> 00:16:02,519 people table okay so let's go back to
348 00:15:59,399 --> 00:16:04,680 the maths and then using a review
349 00:16:02,519 --> 00:16:07,860 manager to create a scenario pivot table
350 00:16:04,680 --> 00:16:10,680 report for the result so okay again go
351 00:16:07,860 --> 00:16:13,800 to data Tab and then forecast group but
352 00:16:10,680 --> 00:16:16,320 if analysis scenario manager and then
353 00:16:13,800 --> 00:16:19,260 again summary but this time select
354 00:16:16,320 --> 00:16:20,399 scenario pivot table report
355 00:16:19,260 --> 00:16:23,639 okay
356 00:16:20,399 --> 00:16:28,459 and then reports are results are same
357 00:16:23,639 --> 00:16:31,380 c16 through a16 now heat okay
358 00:16:28,459 --> 00:16:35,220 change the number format of the of the
359 00:16:31,380 --> 00:16:38,100 fields so these
360 00:16:35,220 --> 00:16:41,279 so profit and regular
361 00:16:38,100 --> 00:16:44,040 to currency with two decimal places and
362 00:16:41,279 --> 00:16:47,540 tolerance symbol so all right
363 00:16:44,040 --> 00:16:47,540 so for this field
364 00:16:49,259 --> 00:16:52,759 just drops
365 00:16:53,220 --> 00:16:58,259 so select all these number
366 00:16:56,459 --> 00:17:01,620 and then right click
367 00:16:58,259 --> 00:17:03,180 and then go to Home tab and then here's
368 00:17:01,620 --> 00:17:05,939 a number group and then click on this
369 00:17:03,180 --> 00:17:06,959 expand and then make sure you select the
370 00:17:05,939 --> 00:17:09,000 currency
371 00:17:06,959 --> 00:17:11,699 and then it is saying with two decimal
372 00:17:09,000 --> 00:17:13,380 places and the dollar as the symbol and
373 00:17:11,699 --> 00:17:15,540 everything is default okay
374 00:17:13,380 --> 00:17:18,000 so the state okay
375 00:17:15,540 --> 00:17:20,040 online next thing add a cluster column
376 00:17:18,000 --> 00:17:22,140 two percent to the scenario pivot okay
377 00:17:20,040 --> 00:17:26,339 so this works it based on the scenario
378 00:17:22,140 --> 00:17:29,280 people report so okay
379 00:17:26,339 --> 00:17:32,040 So based on these reports
380 00:17:29,280 --> 00:17:34,860 so select all these sync clustered
381 00:17:32,040 --> 00:17:36,780 column keywords are okay so for the uh
382 00:17:34,860 --> 00:17:40,020 you can just select these you don't need
383 00:17:36,780 --> 00:17:41,700 to select all range so go to insert and
384 00:17:40,020 --> 00:17:43,500 then here's the pivot set click on view
385 00:17:41,700 --> 00:17:47,280 version it is say you are saying
386 00:17:43,500 --> 00:17:50,039 clustered column okay and then hit OK
387 00:17:47,280 --> 00:17:53,940 now like now Edition resize A8 through
388 00:17:50,039 --> 00:17:56,700 c21 so drag it
389 00:17:53,940 --> 00:18:00,020 A8
390 00:17:56,700 --> 00:18:03,299 and then it is in c21
391 00:18:00,020 --> 00:18:05,400 so C
392 00:18:03,299 --> 00:18:09,320 21.
393 00:18:05,400 --> 00:18:09,320 all right so let's check our result
394 00:18:10,500 --> 00:18:15,179 so from the zoomware
395 00:18:13,380 --> 00:18:16,440 oops
396 00:18:15,179 --> 00:18:19,100 Plus
397 00:18:16,440 --> 00:18:21,600 okay said in this last fees
398 00:18:19,100 --> 00:18:24,480 and then this
399 00:18:21,600 --> 00:18:27,200 and then okay so this is correct now
400 00:18:24,480 --> 00:18:31,200 zoomware worksheet and then
401 00:18:27,200 --> 00:18:31,200 okay certainties
402 00:18:33,780 --> 00:18:38,039 okay total sales
403 00:18:36,419 --> 00:18:40,740 25
404 00:18:38,039 --> 00:18:43,500 okay so it is just showing up to 25
405 00:18:40,740 --> 00:18:47,100 all right so this is also correct
406 00:18:43,500 --> 00:18:49,679 now logistic worksheet
407 00:18:47,100 --> 00:18:52,140 okay so here 85
408 00:18:49,679 --> 00:18:55,320 105
409 00:18:52,140 --> 00:18:58,799 and then okay so this is also correct
410 00:18:55,320 --> 00:19:01,500 now answer report worksheets okay
411 00:18:58,799 --> 00:19:03,660 say here see here make sure you edit
412 00:19:01,500 --> 00:19:06,780 your file name before doing this
413 00:19:03,660 --> 00:19:11,000 okay so this is also correct
414 00:19:06,780 --> 00:19:11,000 now service provider
415 00:19:11,100 --> 00:19:15,660 here
416 00:19:12,600 --> 00:19:18,840 so okay this is also correct
417 00:19:15,660 --> 00:19:21,900 and then scenario summary
418 00:19:18,840 --> 00:19:26,700 okay so this is also correct
419 00:19:21,900 --> 00:19:29,580 and then scenario paper table worksheet
420 00:19:26,700 --> 00:19:30,960 okay so this is also correct and then
421 00:19:29,580 --> 00:19:33,000 lastly
422 00:19:30,960 --> 00:19:36,480 March
423 00:19:33,000 --> 00:19:39,440 all right this is also correct okay so
424 00:19:36,480 --> 00:19:39,440 see you in the next video
