1 00:00:01,820 --> 00:00:05,583 - [Instructor] This is Excel Module 8: SAM Project 1b. 2 00:00:07,010 --> 00:00:09,990 We will be working for Ferguson Fitness, 3 00:00:09,990 --> 00:00:11,550 and there's one thing we need to do 4 00:00:11,550 --> 00:00:13,770 before beginning the project. 5 00:00:13,770 --> 00:00:15,793 We need to have a Solver Add-In, 6 00:00:17,490 --> 00:00:20,100 and so we'll go ahead and activate that in Excel. 7 00:00:20,100 --> 00:00:23,250 It's something that's not automatically within Excel, 8 00:00:23,250 --> 00:00:26,170 so let's go ahead and get that ready to go. 9 00:00:26,170 --> 00:00:29,990 Click on File and then Options. 10 00:00:29,990 --> 00:00:33,223 And in this first column here, select Add-ins. 11 00:00:34,620 --> 00:00:37,550 And in the second column at the bottom, 12 00:00:37,550 --> 00:00:40,063 next to Excel Add-ins click on Go. 13 00:00:40,980 --> 00:00:44,530 And you can see some different tools available to add in. 14 00:00:44,530 --> 00:00:49,370 Just activate the check box by Solver Add-in and click OK. 15 00:00:49,370 --> 00:00:54,370 Then if you check on your Data tab to the far right, 16 00:00:54,580 --> 00:00:57,243 you should have a Solver tool ready to go. 17 00:00:59,590 --> 00:01:03,110 All right, let's do a quick save to get the appropriate 18 00:01:03,110 --> 00:01:06,347 file name going, File, Save As. 19 00:01:06,347 --> 00:01:09,430 The only thing you should have to change on this file 20 00:01:09,430 --> 00:01:13,570 is change the ending 1 to a 2 on the file name. 21 00:01:13,570 --> 00:01:16,513 And I always make sure it's on my desktop as well. 22 00:01:22,840 --> 00:01:24,640 And then I'll save. 23 00:01:24,640 --> 00:01:27,080 All right, so let's go ahead and see what they would like us 24 00:01:27,080 --> 00:01:28,603 to do in this project. 25 00:01:29,810 --> 00:01:32,937 Step task one says that, "Bruce and Doug Ferguson 26 00:01:32,937 --> 00:01:36,467 "started Ferguson Fitness providing comprehensive, private, 27 00:01:36,467 --> 00:01:37,927 "and corporate fitness programs 28 00:01:37,927 --> 00:01:40,937 "in Philadelphia, Pennsylvania. 29 00:01:40,937 --> 00:01:45,097 "Bruce made a workbook detailing the services they offer. 30 00:01:45,097 --> 00:01:47,557 "He asks you to help analyze the data 31 00:01:47,557 --> 00:01:50,760 "to determine how they can increase profits." 32 00:01:50,760 --> 00:01:53,470 So we will switch to the Personal Training worksheet 33 00:01:53,470 --> 00:01:55,700 and create a one-variable data table 34 00:01:55,700 --> 00:01:59,080 to calculate the sales, expenses, and profit 35 00:01:59,080 --> 00:02:04,080 based on the hours of personal training provided as follows. 36 00:02:04,140 --> 00:02:06,690 So in cell E5, we will enter a formula 37 00:02:06,690 --> 00:02:10,730 without using a function that references cell C4 38 00:02:10,730 --> 00:02:13,943 which is the number of hours of personal training provided. 39 00:02:15,870 --> 00:02:19,070 So on the Personal Training worksheet, 40 00:02:19,070 --> 00:02:24,030 if I click in cell E5, I will reference cell C4 41 00:02:24,030 --> 00:02:28,140 by typing in an equals and clicking on cell C4, 42 00:02:28,140 --> 00:02:30,000 which once again is the 43 00:02:32,880 --> 00:02:35,710 personal training hours provided. 44 00:02:35,710 --> 00:02:38,010 Rather than pressing Enter, if I press Tab 45 00:02:40,480 --> 00:02:43,973 it should put me in place for the next reference. 46 00:02:46,410 --> 00:02:50,540 We will type, in cell F5 we will reference cell C19, 47 00:02:50,540 --> 00:02:54,620 so type in an equals under the Total Sales heading. 48 00:02:54,620 --> 00:02:57,990 C19 is going to reference the Total Sales number 49 00:02:57,990 --> 00:03:01,173 in cell C19, and press the Tab. 50 00:03:02,700 --> 00:03:06,920 We want to do the same for Total Expenses in cell C21. 51 00:03:06,920 --> 00:03:09,543 So we'll type an equals, or rather C20. 52 00:03:11,070 --> 00:03:14,230 So make sure you get C20 in that reference. 53 00:03:14,230 --> 00:03:17,970 Press the Tab and then reference the Gross Profit. 54 00:03:17,970 --> 00:03:19,860 In cell H5, type in an equals 55 00:03:19,860 --> 00:03:22,873 and reference the Gross Profit in cell C21. 56 00:03:24,030 --> 00:03:25,740 I can go ahead and press Enter then, 57 00:03:25,740 --> 00:03:29,163 and we're ready to create our one variable data table. 58 00:03:30,780 --> 00:03:33,880 So what I'll need to do is select the information 59 00:03:33,880 --> 00:03:37,183 that the instructions are asking me to select. 60 00:03:39,980 --> 00:03:43,540 They want like E5 to H10 gives us the proper selection 61 00:03:43,540 --> 00:03:46,710 for what we'll need to do to make this data table work, 62 00:03:46,710 --> 00:03:49,650 and then we'll be referencing cell C4 63 00:03:49,650 --> 00:03:51,373 as the Column input cell. 64 00:03:52,580 --> 00:03:54,400 So with that range selected, 65 00:03:54,400 --> 00:03:56,980 I can go ahead and click on Data, 66 00:03:56,980 --> 00:04:01,980 and in the Forecast group, What-If Analysis Data Table. 67 00:04:03,080 --> 00:04:07,280 Now because we only have a one-variable data table going 68 00:04:07,280 --> 00:04:09,720 and it's about Hours Contracted, 69 00:04:09,720 --> 00:04:12,760 we will click in the Column input cell 70 00:04:12,760 --> 00:04:17,760 and reference cell C4 for Hours Sold, 71 00:04:17,830 --> 00:04:21,230 and click OK, and this will appropriately 72 00:04:21,230 --> 00:04:22,763 fill the data table. 73 00:04:25,040 --> 00:04:26,690 All right, let's do a save. 74 00:04:26,690 --> 00:04:28,143 I'm doing Control + S. 75 00:04:29,590 --> 00:04:31,113 Let's go on to the next step. 76 00:04:33,460 --> 00:04:36,570 In number two, we want to create a two-variable data table 77 00:04:36,570 --> 00:04:39,810 to calculate the gross profit based on the hours 78 00:04:39,810 --> 00:04:44,590 of fitness training provided and the hourly rate charged. 79 00:04:44,590 --> 00:04:46,920 So they're giving us a range to select 80 00:04:46,920 --> 00:04:49,620 for this two-variable data table. 81 00:04:49,620 --> 00:04:54,620 We'll be using the hourly rate for the Row input cell, 82 00:04:55,170 --> 00:04:57,930 and the hours of fitness training provided 83 00:04:57,930 --> 00:05:00,210 for the Column input cell. 84 00:05:00,210 --> 00:05:03,523 And then we'll do some formatting on that data table. 85 00:05:05,210 --> 00:05:08,210 So if we select E4, I'm gonna scroll 86 00:05:08,210 --> 00:05:09,760 over a little bit for a minute. 87 00:05:12,470 --> 00:05:15,453 Need to be able to select clear over to column L. 88 00:05:16,860 --> 00:05:19,150 So if I select, sorry, E14 89 00:05:21,170 --> 00:05:25,370 over to L and down to Row 19, 90 00:05:25,370 --> 00:05:27,743 I've got the correct selection made. 91 00:05:29,240 --> 00:05:33,470 I will go to the Data tab Forecast group, 92 00:05:33,470 --> 00:05:36,230 What-If Analysis Data Table, 93 00:05:36,230 --> 00:05:38,640 and our instructions did mention that it wanted 94 00:05:38,640 --> 00:05:43,640 to have the Row input cell be C5. 95 00:05:44,520 --> 00:05:46,773 So I want you to see what that's going to be, 96 00:05:47,810 --> 00:05:50,293 C5 is Price Per Hour. 97 00:05:51,940 --> 00:05:53,830 Right, there's your prices per hour 98 00:05:56,170 --> 00:05:58,760 from left to right in Row 14. 99 00:05:58,760 --> 00:06:02,683 And then the Column input cell is going to be cell C4, 100 00:06:04,140 --> 00:06:07,200 which is representing the hours that were sold 101 00:06:07,200 --> 00:06:08,820 in this Column here. 102 00:06:08,820 --> 00:06:12,150 Once you click OK, your data table will fill in 103 00:06:12,150 --> 00:06:13,983 with the appropriate values. 104 00:06:18,650 --> 00:06:20,470 Now one thing I wanted to point out here 105 00:06:20,470 --> 00:06:24,730 is what's going on, particularly in cell E14. 106 00:06:24,730 --> 00:06:27,940 This is what's making your data table work. 107 00:06:27,940 --> 00:06:32,330 We have E14 right here is referencing cell C21, 108 00:06:32,330 --> 00:06:34,190 which is all about the gross profit. 109 00:06:34,190 --> 00:06:37,340 And you'll notice that the title in this data table 110 00:06:37,340 --> 00:06:39,800 is all about gross profit analysis. 111 00:06:39,800 --> 00:06:43,330 And it is what is driving this data that filled in 112 00:06:43,330 --> 00:06:45,293 when we needed it to do so. 113 00:06:47,030 --> 00:06:51,000 So this particular number isn't that helpful 114 00:06:51,000 --> 00:06:52,940 to what we're trying to do. 115 00:06:52,940 --> 00:06:56,150 So we're going to apply a custom format 116 00:06:58,160 --> 00:07:03,160 to cell E14 to display the text Hours/Rate 117 00:07:03,560 --> 00:07:05,423 in place of that cell value. 118 00:07:06,700 --> 00:07:10,730 So in order to do this, make sure E14 is active, 119 00:07:10,730 --> 00:07:13,900 right-click, choose to format that cell, 120 00:07:13,900 --> 00:07:16,420 and in the options it's already considered 121 00:07:16,420 --> 00:07:18,770 a Custom category. 122 00:07:18,770 --> 00:07:20,990 But if you'll go over to the Type area 123 00:07:20,990 --> 00:07:24,970 and scroll to the top, you'll find a General category. 124 00:07:24,970 --> 00:07:28,590 And then once you have that General type selected, 125 00:07:28,590 --> 00:07:31,420 you can select the word General just above that 126 00:07:31,420 --> 00:07:36,420 and in quotes, type Hours/Rate, 127 00:07:38,200 --> 00:07:42,620 close quote, and then click OK. 128 00:07:42,620 --> 00:07:45,460 It gives us a more meaningful Column heading, 129 00:07:45,460 --> 00:07:49,520 but it also leaves that reference up in the formula bar 130 00:07:49,520 --> 00:07:53,133 to the Gross Profit, found in cell C21. 131 00:07:55,670 --> 00:07:58,120 All right, so now on step three, 132 00:07:58,120 --> 00:08:01,020 we're going to switch to the Consulting worksheet 133 00:08:01,020 --> 00:08:03,630 and create a Scatter with Straight Lines chart 134 00:08:03,630 --> 00:08:07,470 based on the range E4 to G14 in the data table 135 00:08:07,470 --> 00:08:10,913 titled Consulting Break-Even Analysis. 136 00:08:15,650 --> 00:08:20,493 So for Consulting, we will select E4 to G14, 137 00:08:22,600 --> 00:08:25,750 which does not include the Gross Profit Column. 138 00:08:25,750 --> 00:08:28,030 Kind of notice that that's what they want there, 139 00:08:28,030 --> 00:08:30,850 and then we'll just go to Insert. 140 00:08:30,850 --> 00:08:34,370 And in the Charts group, locate the Scatter Charts 141 00:08:34,370 --> 00:08:36,630 and once again you'll looking 142 00:08:36,630 --> 00:08:38,783 for Scatter with Straight Lines. 143 00:08:49,170 --> 00:08:52,503 All right, so it's that last option in the Scatter Category. 144 00:08:53,970 --> 00:08:57,090 All right, and once you have that chart ready to go, 145 00:08:57,090 --> 00:09:00,580 we will modify the chart as follows. 146 00:09:00,580 --> 00:09:03,120 We first want to resize and reposition the chart 147 00:09:03,120 --> 00:09:05,510 so that it covers E15 148 00:09:08,800 --> 00:09:10,683 to H30. 149 00:09:17,270 --> 00:09:19,470 If I'm not sure I've reached 30, 150 00:09:19,470 --> 00:09:21,830 I usually select that row just to make sure 151 00:09:21,830 --> 00:09:26,830 that I include row 30 in my selection. 152 00:09:29,460 --> 00:09:32,680 And then once you've got your chart resized 153 00:09:32,680 --> 00:09:36,510 and repositioned, we want to remove the chart title. 154 00:09:36,510 --> 00:09:40,030 So I'm gonna select the chart title and press Delete. 155 00:09:40,030 --> 00:09:41,680 There are different ways to do that. 156 00:09:41,680 --> 00:09:44,470 You can use your chart element tool over here 157 00:09:44,470 --> 00:09:46,240 if you want to, or just select it, 158 00:09:46,240 --> 00:09:49,960 select its placeholder and press Delete. 159 00:09:49,960 --> 00:09:53,690 Would also like us to add Sales and Expenses 160 00:09:53,690 --> 00:09:56,930 as the vertical axis title, 161 00:09:56,930 --> 00:10:00,103 and Hours as the horizontal axis title. 162 00:10:03,690 --> 00:10:05,480 So I'll use the Add Chart element, 163 00:10:05,480 --> 00:10:10,010 and I will activate the axis titles for the vertical one. 164 00:10:10,010 --> 00:10:12,620 I can triple click within its placeholder 165 00:10:12,620 --> 00:10:14,823 and add Sales and Expenses. 166 00:10:18,190 --> 00:10:20,920 And double-click or triple-click the axis title 167 00:10:21,820 --> 00:10:25,693 to select what's in there by default and type Hours, 168 00:10:28,340 --> 00:10:29,173 and click away. 169 00:10:36,018 --> 00:10:37,550 All right, step five wants us to change 170 00:10:37,550 --> 00:10:41,500 the Bounds Axis Options as follows. 171 00:10:41,500 --> 00:10:44,100 We'll be changing the Minimum Bounds of the vertical axis 172 00:10:44,100 --> 00:10:47,780 to -30,000 and let the Maximum Bounds automatically 173 00:10:47,780 --> 00:10:50,460 change to 130,000. 174 00:10:50,460 --> 00:10:53,370 We will also change the number format to, 175 00:10:53,370 --> 00:10:57,070 on the vertical axis, to Currency with 0 decimal places 176 00:10:57,070 --> 00:10:59,093 and a $ as the symbol. 177 00:10:59,930 --> 00:11:03,310 And we will change the Minimum Bounds of the horizontal axis 178 00:11:03,310 --> 00:11:07,840 to 700, and the Maximum Bounds to 1500. 179 00:11:07,840 --> 00:11:11,520 So what we're doing here is we're looking at our lines 180 00:11:11,520 --> 00:11:14,960 and we're noticing that they're not taking up a lot 181 00:11:14,960 --> 00:11:17,270 of the grid area on the chart, 182 00:11:17,270 --> 00:11:20,220 so we're making adjustments so that it looks better 183 00:11:20,220 --> 00:11:23,293 and our lines take more of the space available. 184 00:11:25,030 --> 00:11:27,900 So to change the Minimum Bounds of the vertical axis, 185 00:11:27,900 --> 00:11:29,950 let's go ahead and select the vertical axis 186 00:11:29,950 --> 00:11:31,250 and right-click it. 187 00:11:31,250 --> 00:11:34,840 Choose to format that axis, and then over in our Format Axis 188 00:11:34,840 --> 00:11:37,380 task pane, we can go to the Bounds section 189 00:11:37,380 --> 00:11:40,363 and under Minimum type in -30,000. 190 00:11:42,220 --> 00:11:44,890 And if I tab away from it, the Maximum Bounds 191 00:11:44,890 --> 00:11:46,783 will automatically adjust. 192 00:11:48,720 --> 00:11:51,680 Then let's go ahead and format the vertical axis 193 00:11:51,680 --> 00:11:54,140 to Currency, so I'm gonna scroll down 194 00:11:54,140 --> 00:11:58,750 in the Format Axis task pane and expand Number 195 00:12:00,860 --> 00:12:04,000 where I can set the Category to Currency 196 00:12:04,000 --> 00:12:09,000 and I can also make sure it's at zero decimal places. 197 00:12:09,420 --> 00:12:11,090 And you can see that that's what will happen 198 00:12:11,090 --> 00:12:13,180 over here on the vertical axis. 199 00:12:13,180 --> 00:12:17,360 Now let's adjust our Minimum Bounds of the horizontal axis, 200 00:12:17,360 --> 00:12:21,200 which is kind of moved up into the chart itself 201 00:12:21,200 --> 00:12:23,620 because of the negative values. 202 00:12:23,620 --> 00:12:26,170 Let's go ahead and right-click 203 00:12:26,170 --> 00:12:29,633 the horizontal axis value, format the axis. 204 00:12:30,600 --> 00:12:32,973 And for the Minimum Bounds it's 700. 205 00:12:35,420 --> 00:12:38,983 And for the Maximum Bounds we're going to set it at 1500. 206 00:12:41,223 --> 00:12:43,040 And as I press Enter there, you can see that the lines 207 00:12:43,040 --> 00:12:46,113 now take up much more of the space available. 208 00:12:51,320 --> 00:12:54,930 Now we will be creating two scenarios as follows 209 00:12:54,930 --> 00:12:58,250 to compare the costs of hiring fitness instructors 210 00:12:58,250 --> 00:13:01,940 with those for hiring certified personal trainers 211 00:13:01,940 --> 00:13:04,900 while increasing the number of hours provided. 212 00:13:04,900 --> 00:13:07,760 So in the Scenario Manager, we will add those two 213 00:13:07,760 --> 00:13:10,950 new scenarios using the data shown in bold 214 00:13:10,950 --> 00:13:14,350 in the Table below, in Table 1 below. 215 00:13:14,350 --> 00:13:16,490 The changing cells for both scenarios 216 00:13:16,490 --> 00:13:20,760 are the nonadjacent cells of C4, C11, and C14. 217 00:13:20,760 --> 00:13:23,550 And then we'll close the Scenario Manager 218 00:13:23,550 --> 00:13:26,593 without showing any of the scenarios. 219 00:13:30,080 --> 00:13:34,180 So on the Consulting sheet, I will deselect this chart 220 00:13:34,180 --> 00:13:36,170 and then I will go to the Data Tab, 221 00:13:36,170 --> 00:13:39,650 the Forecast group, What-If Analysis, 222 00:13:39,650 --> 00:13:42,380 and call up the Scenario Manager. 223 00:13:42,380 --> 00:13:45,090 And it said it wanted us to create two scenarios, 224 00:13:45,090 --> 00:13:46,930 so let's click Add. 225 00:13:46,930 --> 00:13:50,060 The first scenario name from our Table 1 226 00:13:50,060 --> 00:13:52,293 is called Instructors. 227 00:13:55,740 --> 00:13:59,040 And it's supposed to be by changing cells 228 00:13:59,040 --> 00:14:01,563 the specific cells are C4. 229 00:14:03,240 --> 00:14:07,140 So I select whatever is in my changing cell box 230 00:14:07,140 --> 00:14:10,550 and I can select C4, which is Hours Sold. 231 00:14:10,550 --> 00:14:14,320 I'll hold my Control key down and also select C11 232 00:14:16,030 --> 00:14:18,940 which is Variable Cost Per Hour, 233 00:14:18,940 --> 00:14:22,863 and C14, which is Total Fixed Cost. 234 00:14:24,120 --> 00:14:25,993 Right then I'm ready to click OK. 235 00:14:27,490 --> 00:14:32,490 And the Hours Sold for Consulting for instructors was 1500. 236 00:14:35,880 --> 00:14:38,340 The Variable Cost Per Hour for Consulting 237 00:14:38,340 --> 00:14:40,190 for instructors was 53. 238 00:14:40,190 --> 00:14:42,390 I'm tabbing to get to the next box 239 00:14:42,390 --> 00:14:44,920 or you can go ahead and click on the box. 240 00:14:44,920 --> 00:14:47,840 And then the Total Fixed Cost for Consulting 241 00:14:47,840 --> 00:14:51,200 for instructors was 34,500. 242 00:14:51,200 --> 00:14:52,620 I just put in the raw data 243 00:14:52,620 --> 00:14:55,300 and I don't worry about the commas. 244 00:14:55,300 --> 00:14:59,430 Click Add for the second scenario we want to set up, 245 00:14:59,430 --> 00:15:01,563 that scenario name is Trainers. 246 00:15:03,920 --> 00:15:06,770 The changing cells should not change, 247 00:15:06,770 --> 00:15:09,140 otherwise it's not a comparable scenario. 248 00:15:09,140 --> 00:15:12,483 So we'll leave the changing cells as-is, then click OK. 249 00:15:13,990 --> 00:15:18,990 Then the Hours Sold Consulting is 1800. 250 00:15:20,520 --> 00:15:24,150 The Variable Cost Per Hour for Consulting 251 00:15:24,150 --> 00:15:29,010 is 55 for trainers, and the Total Fixed Cost for Consulting 252 00:15:29,010 --> 00:15:33,403 for trainers is 35,500. 253 00:15:35,380 --> 00:15:38,400 All right, then I can go ahead and click OK. 254 00:15:38,400 --> 00:15:41,530 I can check my Instructors Scenario name 255 00:15:41,530 --> 00:15:43,460 and my Trainers Scenario name 256 00:15:43,460 --> 00:15:44,910 and if there are any problems there, 257 00:15:44,910 --> 00:15:48,120 I can also go in and edit the information there 258 00:15:48,120 --> 00:15:49,623 if I need to do that. 259 00:15:52,120 --> 00:15:54,220 And then I can close the Scenario Manager. 260 00:16:02,470 --> 00:16:07,050 Let's move on to task seven, which tells us to switch 261 00:16:07,050 --> 00:16:09,940 to the Corporate Programs worksheet, 262 00:16:09,940 --> 00:16:12,330 create a Scatter with Straight Lines chart 263 00:16:12,330 --> 00:16:16,220 based on range E6 to J14 in the data table 264 00:16:16,220 --> 00:16:19,773 titled Corporate Programs Net Income Analysis. 265 00:16:20,730 --> 00:16:22,040 So let's go ahead and do that, 266 00:16:22,040 --> 00:16:23,893 and then we will modify the chart. 267 00:16:26,380 --> 00:16:30,700 So we're selecting in the Corporate Programs sheet 268 00:16:30,700 --> 00:16:33,413 cells E6 to J14. 269 00:16:37,410 --> 00:16:39,973 I'm gonna have to scroll over so we can see that. 270 00:16:45,220 --> 00:16:46,990 And then I will choose to Insert, 271 00:16:46,990 --> 00:16:49,830 and in the Charts group I'll go the Scatter category 272 00:16:49,830 --> 00:16:52,053 and look for Scatter with Straight Lines. 273 00:16:55,990 --> 00:16:58,120 All right, so we have that chart. 274 00:16:58,120 --> 00:17:02,210 We do need to resize it and reposition it 275 00:17:02,210 --> 00:17:06,280 so that it resides in E15 to J30. 276 00:17:06,280 --> 00:17:08,460 We will remove the title from the chart. 277 00:17:08,460 --> 00:17:10,690 We will reposition the chart legend 278 00:17:10,690 --> 00:17:12,470 to the right of the chart. 279 00:17:12,470 --> 00:17:15,260 We'll add Net Income as a vertical axis title 280 00:17:15,260 --> 00:17:18,400 and Hours as the horizontal axis title. 281 00:17:18,400 --> 00:17:20,800 And then we'll change the colors of the chart 282 00:17:20,800 --> 00:17:23,200 to Monochromatic Palette 2, 283 00:17:23,200 --> 00:17:25,763 and it tells us specifically where that is. 284 00:17:27,230 --> 00:17:30,590 All right, so let's first move and reposition the chart 285 00:17:30,590 --> 00:17:32,760 so that it begins in cell E15 286 00:17:35,040 --> 00:17:36,897 and extends to J30. 287 00:17:50,610 --> 00:17:53,100 Then let's remove the chart title. 288 00:17:53,100 --> 00:17:56,990 I'm clicking on the placeholder and pressing delete. 289 00:17:56,990 --> 00:18:01,290 I can also now reposition the chart legend, 290 00:18:01,290 --> 00:18:03,603 which is along the bottom at this point. 291 00:18:04,490 --> 00:18:06,660 So I'm clicking the chart legend. 292 00:18:06,660 --> 00:18:09,400 I can right-click and choose to format the legend 293 00:18:09,400 --> 00:18:11,630 and get the Format Legend task pane 294 00:18:11,630 --> 00:18:14,903 and choose to put the chart legend on the right. 295 00:18:16,400 --> 00:18:18,810 I can add a vertical axis title 296 00:18:21,120 --> 00:18:23,930 by formatting the vertical axis. 297 00:18:23,930 --> 00:18:27,780 Oh, I just want a title so I make sure the chart 298 00:18:27,780 --> 00:18:31,720 is selected, let me size my task pane out a little bit 299 00:18:31,720 --> 00:18:35,370 so I can use my quick tools over here in Chart Elements. 300 00:18:35,370 --> 00:18:37,410 Keep in mind that you can come up to the ribbon too 301 00:18:37,410 --> 00:18:40,550 and Add Chart Elements whenever you would like 302 00:18:40,550 --> 00:18:42,920 as long as you're on the Chart Design tab. 303 00:18:42,920 --> 00:18:45,100 But I'll use my Add Chart Elements tool, 304 00:18:45,100 --> 00:18:49,240 I'll choose Axis Titles, and for the vertical axis 305 00:18:52,150 --> 00:18:53,683 it should be Net Income. 306 00:18:58,030 --> 00:19:02,293 And the horizontal should be Hours. 307 00:19:07,200 --> 00:19:08,920 And then with the chart still selected 308 00:19:08,920 --> 00:19:12,200 on the Chart Design tab, in the Chart Styles group 309 00:19:12,200 --> 00:19:13,830 I can change colors 310 00:19:15,330 --> 00:19:19,113 to Monochromatic Palette 2. 311 00:19:22,510 --> 00:19:25,750 All right, let's take a look at task nine 312 00:19:25,750 --> 00:19:28,940 that would like us to change the Bounds Axis Options 313 00:19:28,940 --> 00:19:30,450 for this new chart. 314 00:19:30,450 --> 00:19:34,020 The Minimum Balance will, the vertical axis will be -20,000 315 00:19:34,020 --> 00:19:37,830 and we will leave the Maximum Bounds at 40,000. 316 00:19:37,830 --> 00:19:40,970 We'll set the horizontal axis to cross 317 00:19:40,970 --> 00:19:44,680 at the axis value -20,000. 318 00:19:44,680 --> 00:19:47,570 We'll format the vertical axis for Currency 319 00:19:47,570 --> 00:19:50,800 with 0 decimal places and a $ as the symbol, 320 00:19:50,800 --> 00:19:52,700 and we will change the Minimum Bounds 321 00:19:52,700 --> 00:19:54,690 of the horizontal axis to 900 322 00:19:54,690 --> 00:19:57,173 and the Maximum Bounds to 1700. 323 00:19:59,890 --> 00:20:01,420 All right, so the first thing we wanna do 324 00:20:01,420 --> 00:20:05,633 is change the Minimum Bounds of the vertical axis. 325 00:20:06,680 --> 00:20:08,740 So I'll select the vertical axis. 326 00:20:08,740 --> 00:20:10,313 I will choose to format it. 327 00:20:11,540 --> 00:20:15,507 And the Minimum Bounds are supposed to be set at -20,000. 328 00:20:19,320 --> 00:20:22,243 We will leave the Maximum Bounds at 40,000. 329 00:20:28,670 --> 00:20:31,240 And we will set the horizontal axis 330 00:20:31,240 --> 00:20:34,110 to cross at the axis value of -20,000. 331 00:20:45,380 --> 00:20:47,033 All right, let's see, um, 332 00:20:54,320 --> 00:20:57,140 all right so if I choose under the horizontal axis crosses 333 00:20:57,140 --> 00:21:02,103 the axis value, I'm gonna set that at 20,000, -20,000. 334 00:21:06,700 --> 00:21:09,170 I want to change the number format 335 00:21:09,170 --> 00:21:12,433 of the vertical axis to currency. 336 00:21:18,110 --> 00:21:22,410 So I'll expand Number, set it to Currency, 337 00:21:22,410 --> 00:21:25,043 and we should have zero decimal places. 338 00:21:27,830 --> 00:21:32,220 And then we want to change the Minimum Bounds 339 00:21:32,220 --> 00:21:36,111 of the horizontal axis, so I've selected the horizontal axis 340 00:21:36,111 --> 00:21:37,711 and I'll right-click and Format. 341 00:21:39,600 --> 00:21:41,503 The Minimum Bounds should be 900, 342 00:21:45,150 --> 00:21:47,520 and the Maximum Bounds should be 1700. 343 00:21:57,210 --> 00:21:59,090 Task number 10 would like us to edit 344 00:21:59,090 --> 00:22:01,570 the chart series names as follows. 345 00:22:01,570 --> 00:22:04,390 For Series 1, we're going to change it 346 00:22:04,390 --> 00:22:07,110 to the series name in F5. 347 00:22:07,110 --> 00:22:09,910 For Series 2, we'll change the series name 348 00:22:09,910 --> 00:22:12,913 to the content in cell G5. (electronic chimes) 349 00:22:12,913 --> 00:22:14,143 - [Electronic Automated Voice] I'm on it, hello? 350 00:22:14,143 --> 00:22:16,770 (electronic chimes) Hmm. 351 00:22:16,770 --> 00:22:20,090 - [Instructor] And then for, we'll follow the same situation 352 00:22:20,090 --> 00:22:22,343 with Series 3, 4, and 5. 353 00:22:23,600 --> 00:22:26,960 So when we go back here, we can select the legend. 354 00:22:26,960 --> 00:22:28,330 It's basically what it is, 355 00:22:28,330 --> 00:22:30,140 and if you right-click the, (chuckles) 356 00:22:30,140 --> 00:22:34,430 the selection of the legend you can select the data. 357 00:22:34,430 --> 00:22:37,460 And that puts you into an easy place to make changes 358 00:22:37,460 --> 00:22:40,090 to the series names here. 359 00:22:40,090 --> 00:22:42,940 So I'm gonna select Series 1, choose to edit it, 360 00:22:42,940 --> 00:22:45,903 and I can simply go up and select F5. 361 00:22:46,850 --> 00:22:49,960 I'll click, um, I think that's all I need to do there. 362 00:22:49,960 --> 00:22:52,280 If I click OK you can see that that first 363 00:22:52,280 --> 00:22:54,870 Series 1 is now $80. 364 00:22:54,870 --> 00:22:58,640 So I'll select Series 2, I will edit that to be, 365 00:22:58,640 --> 00:23:00,233 to include G5, 366 00:23:03,690 --> 00:23:07,483 Edit Series 3 to be H5, 367 00:23:11,350 --> 00:23:15,603 Edit Series 4 to be I5, 368 00:23:18,460 --> 00:23:22,333 and Edit Series 5 to be J5, 369 00:23:24,310 --> 00:23:27,943 and click OK. (dialog box chimes) 370 00:23:30,620 --> 00:23:32,150 Okay, let me see, 371 00:23:32,150 --> 00:23:35,820 I might have an error. (dialog box chimes) 372 00:23:35,820 --> 00:23:38,670 Let's see which one it has not agreed with me on, 373 00:23:38,670 --> 00:23:40,170 looks like the very last one 374 00:23:42,296 --> 00:23:44,027 which should be a J5, and OK, 375 00:23:47,747 --> 00:23:50,830 and OK and you can see now that the series names 376 00:23:50,830 --> 00:23:53,023 have changed in this new chart. 377 00:23:57,190 --> 00:24:01,280 Task 11 tells us 378 00:24:01,280 --> 00:24:03,820 that Bruce wants to determine whether partnering 379 00:24:03,820 --> 00:24:06,660 with another fitness company would reduce the costs 380 00:24:06,660 --> 00:24:09,080 of the fitness training services. 381 00:24:09,080 --> 00:24:14,080 So we will be switching to the Training Providers worksheet 382 00:24:14,730 --> 00:24:18,923 and then run the Solver to solve this problem as follows. 383 00:24:22,170 --> 00:24:24,660 What we're going to do is set the objective 384 00:24:24,660 --> 00:24:28,940 as minimizing the value of F10 which is the Total Costs. 385 00:24:28,940 --> 00:24:31,080 We will use the range C4 to E4 386 00:24:31,080 --> 00:24:33,780 as the changing variable cells. 387 00:24:33,780 --> 00:24:35,950 Adjust the hours provided by each company 388 00:24:35,950 --> 00:24:38,420 using the following constraints. 389 00:24:38,420 --> 00:24:40,870 So our constraints are listed here 390 00:24:40,870 --> 00:24:42,870 and I will work through those 391 00:24:42,870 --> 00:24:46,000 and kind of explain those as we go along. 392 00:24:46,000 --> 00:24:48,370 So let's go back to our spreadsheet, 393 00:24:48,370 --> 00:24:50,880 specifically we want to go to 394 00:24:53,480 --> 00:24:55,163 the Training Providers worksheet, 395 00:24:56,840 --> 00:24:59,000 and we're going to use the Solver. 396 00:24:59,000 --> 00:25:01,670 So let's look here, we've got Training Providers here. 397 00:25:01,670 --> 00:25:03,660 We've got Fixed and Variable Costs, 398 00:25:03,660 --> 00:25:06,340 and then Total Costs here listed. 399 00:25:06,340 --> 00:25:09,770 So, and different types of products, 400 00:25:09,770 --> 00:25:13,970 the Express, the FitPros, the Universal, and the Total 401 00:25:13,970 --> 00:25:18,140 for those different products or providers. 402 00:25:18,140 --> 00:25:20,710 So let's go ahead and call up the Solver. 403 00:25:20,710 --> 00:25:22,300 Once again, that's on our Data tab. 404 00:25:22,300 --> 00:25:24,470 We added it at the beginning of the project 405 00:25:24,470 --> 00:25:26,230 if you didn't have it already. 406 00:25:26,230 --> 00:25:29,000 So on the Data tab, call up the Solver. 407 00:25:29,000 --> 00:25:32,690 Our instructions tell us that we want to set the objective 408 00:25:32,690 --> 00:25:37,170 as minimizing the value, so I'm gonna choose Minimizing 409 00:25:37,170 --> 00:25:40,320 the value of what's in cell F10. 410 00:25:40,320 --> 00:25:43,210 So if I can pull over and show you that F10 411 00:25:43,210 --> 00:25:45,410 is actually the Total Costs. 412 00:25:45,410 --> 00:25:48,370 We want to minimize the Total Costs 413 00:25:48,370 --> 00:25:52,280 for Ferguson Fitness by changing the following cells. 414 00:25:52,280 --> 00:25:55,950 So click in By Changing Variable Cells. 415 00:25:55,950 --> 00:26:00,840 The cells that are variable are in C4 to E4 416 00:26:02,040 --> 00:26:06,040 which are the Hours Provided, all right. 417 00:26:06,040 --> 00:26:10,330 And then just so the Solver can actually find solutions 418 00:26:10,330 --> 00:26:12,160 to what we need it to do, 419 00:26:12,160 --> 00:26:14,220 we need it to add some constraints. 420 00:26:14,220 --> 00:26:16,890 So the first thing we'll do is click on Add. 421 00:26:16,890 --> 00:26:19,890 The first constraint in our instructions say 422 00:26:19,890 --> 00:26:23,290 that F4 has to equal 600. 423 00:26:23,290 --> 00:26:28,290 So F4 is the total hours, 424 00:26:28,340 --> 00:26:30,800 you know, that this is what we have to work with 425 00:26:30,800 --> 00:26:33,740 so we want to make sure that that's one of the constraints 426 00:26:33,740 --> 00:26:37,573 that we have 600 hours to work with, click Add. 427 00:26:38,790 --> 00:26:43,790 It also mentions that F10 is less than or equal to 130,000 428 00:26:45,800 --> 00:26:49,420 and that's the maximum fees paid to another fitness company. 429 00:26:49,420 --> 00:26:53,250 So once again, we select F10 and we say that it has 430 00:26:53,250 --> 00:26:57,573 to be less than or equal to 130,000. 431 00:27:00,990 --> 00:27:03,050 Once again that's the maximum fees paid 432 00:27:03,050 --> 00:27:04,950 to another fitness company. 433 00:27:04,950 --> 00:27:07,120 We'll add that constraint. 434 00:27:07,120 --> 00:27:10,930 The next constraint says that C4 to E4, 435 00:27:10,930 --> 00:27:15,930 or our Hours Provided, has to be less than or equal to 220, 436 00:27:16,150 --> 00:27:20,450 220 which is the maximum 437 00:27:21,720 --> 00:27:24,890 hours provided by a single fitness company. 438 00:27:24,890 --> 00:27:27,040 So click Add, we have another constraint, 439 00:27:27,040 --> 00:27:28,530 and we have one more. 440 00:27:28,530 --> 00:27:32,820 We also have to make sure that C4 to E4 441 00:27:32,820 --> 00:27:36,720 is set as an integer, meaning we can't just have 442 00:27:36,720 --> 00:27:39,340 a partial hours recorded. 443 00:27:39,340 --> 00:27:42,150 We want to have whole numbers as the hours 444 00:27:42,150 --> 00:27:44,570 that would be showing in any one 445 00:27:44,570 --> 00:27:46,343 of those cells in that range. 446 00:27:47,800 --> 00:27:49,410 All right, so we can click OK now 447 00:27:49,410 --> 00:27:51,560 and we will see a list of our constraints 448 00:27:51,560 --> 00:27:55,023 just to make sure that we have set those up correctly. 449 00:27:55,970 --> 00:27:58,900 Let's see what the next thing is to do in the step. 450 00:27:58,900 --> 00:28:00,620 It wants us to run to the Solver 451 00:28:00,620 --> 00:28:03,780 and keep the solution that the solver gives us, 452 00:28:03,780 --> 00:28:08,780 and then return to the Solver Parameters Dialog box. 453 00:28:08,870 --> 00:28:12,147 We will save that model in this particular range, 454 00:28:12,147 --> 00:28:16,740 and then we'll go ahead and close that dialog box, okay? 455 00:28:16,740 --> 00:28:18,330 So let's do that then. 456 00:28:18,330 --> 00:28:21,943 So we want to run the Solver, we're going to click on Solve. 457 00:28:23,117 --> 00:28:24,320 (dialog box chimes) 458 00:28:24,320 --> 00:28:27,380 In the dialog box that is provided for us, 459 00:28:27,380 --> 00:28:29,930 we want to keep the Solver solution 460 00:28:29,930 --> 00:28:31,820 and we want to be able to return 461 00:28:31,820 --> 00:28:35,240 to the Solver Parameters Dialog box so that we can then 462 00:28:35,240 --> 00:28:37,770 load and save its results. 463 00:28:37,770 --> 00:28:40,600 So our instructions specifically say to save the model 464 00:28:40,600 --> 00:28:45,493 in range B14 to B21. 465 00:28:46,980 --> 00:28:49,043 So I'm going to choose Load/Save. 466 00:28:50,490 --> 00:28:54,623 I'm going to select B14 to be 21. 467 00:28:56,439 --> 00:28:59,670 You can actually just start in B14 and not select the range, 468 00:28:59,670 --> 00:29:02,730 but I like to see where the range will reside. 469 00:29:02,730 --> 00:29:05,553 And then be sure, be sure to click Save. 470 00:29:07,810 --> 00:29:10,190 And the results will be given to you 471 00:29:11,090 --> 00:29:13,460 in the spreadsheet itself. 472 00:29:13,460 --> 00:29:17,510 Your eText explains in detail what this model means 473 00:29:17,510 --> 00:29:19,900 and what each entry means, 474 00:29:19,900 --> 00:29:22,200 and I'll leave you time to go ahead 475 00:29:22,200 --> 00:29:24,633 and check your eText for an explanation. 476 00:29:27,920 --> 00:29:30,823 So let's go ahead and close the Solver. 477 00:29:34,840 --> 00:29:36,410 So back to the instructions. 478 00:29:36,410 --> 00:29:38,330 Task 12 would like us to switch 479 00:29:38,330 --> 00:29:40,300 to the All Services worksheet. 480 00:29:40,300 --> 00:29:42,170 We will use the Scenario Manager 481 00:29:42,170 --> 00:29:44,890 to create a Scenario Summary report 482 00:29:44,890 --> 00:29:47,123 that summarizes the effect of Current, 483 00:29:48,130 --> 00:29:52,270 Increase Hourly, and Contractors scenarios. 484 00:29:52,270 --> 00:29:57,270 Use the range C17 to E17 as the result cells. 485 00:29:59,170 --> 00:30:00,930 So on the All Services worksheet, 486 00:30:00,930 --> 00:30:02,580 let's go ahead and do that. 487 00:30:02,580 --> 00:30:06,370 If you recall from before, the Scenario Manager 488 00:30:06,370 --> 00:30:09,363 is in Data, What-If Analysis, 489 00:30:11,170 --> 00:30:13,110 Scenario Manager, okay. 490 00:30:13,110 --> 00:30:16,710 So remember we were able to in a previous task 491 00:30:16,710 --> 00:30:18,930 create some scenarios. 492 00:30:18,930 --> 00:30:21,170 This time we're gonna go directly to Summary 493 00:30:23,220 --> 00:30:27,680 so that we can provide a Scenario Summary 494 00:30:29,020 --> 00:30:33,770 that will use the range C17 to E17 as the result cell. 495 00:30:33,770 --> 00:30:35,853 So let's see what's in those cells. 496 00:30:37,667 --> 00:30:42,070 C17 to E17 is Profit Per Hour, all right? 497 00:30:42,070 --> 00:30:44,183 So we go ahead and click OK. 498 00:30:45,830 --> 00:30:48,970 A new sheet is created entitled Scenario Summary, 499 00:30:48,970 --> 00:30:53,110 and this information is now provided in columns 500 00:30:53,110 --> 00:30:54,983 next to one another. 501 00:30:57,770 --> 00:31:01,090 Just quickly, the Scenario Summary shows the current values, 502 00:31:01,090 --> 00:31:04,520 it shows the scenario if we stay with our current plan, 503 00:31:04,520 --> 00:31:07,760 it also shows the Scenario Summary 504 00:31:07,760 --> 00:31:10,050 if we increase our hourly, 505 00:31:10,050 --> 00:31:13,093 and also if there is a contractor change. 506 00:31:14,220 --> 00:31:17,070 All right, once again your eText can give you more details 507 00:31:17,070 --> 00:31:19,870 on the Scenario Summary, but this is what it looks like. 508 00:31:22,940 --> 00:31:25,920 So let's go ahead and switch back now 509 00:31:25,920 --> 00:31:27,470 to the All Services worksheet 510 00:31:27,470 --> 00:31:29,720 where we'll use the Scenario Manager 511 00:31:29,720 --> 00:31:32,510 to compare the profit per hour in each scenario. 512 00:31:32,510 --> 00:31:36,350 We will be doing a Scenario PivotTable report 513 00:31:36,350 --> 00:31:40,420 for the result cells in C17 through E17. 514 00:31:40,420 --> 00:31:43,280 We will remove the Filter from the PivotTable. 515 00:31:43,280 --> 00:31:45,410 There's a Filter field on that PivotTable, 516 00:31:45,410 --> 00:31:47,430 so we will remove that. 517 00:31:47,430 --> 00:31:49,970 And then we will change the number format 518 00:31:49,970 --> 00:31:52,733 of several of the PivotTable fields. 519 00:31:55,180 --> 00:31:57,990 And we will use Personal Training as the row label 520 00:31:57,990 --> 00:32:02,990 Consulting, in B3, and then Consulting is the value in C3, 521 00:32:03,080 --> 00:32:06,340 and Corporate Programs as the value in D3. 522 00:32:06,340 --> 00:32:09,210 We'll also in cell A1 use Profit Per Hour 523 00:32:09,210 --> 00:32:10,533 as a report title. 524 00:32:12,028 --> 00:32:15,150 We will format that report title as the Title cell style 525 00:32:15,150 --> 00:32:18,550 and then we will resize columns A through D to 17. 526 00:32:18,550 --> 00:32:20,600 So we're basically making this look like 527 00:32:20,600 --> 00:32:24,503 a much nicer Scenario PivotTable report. 528 00:32:29,240 --> 00:32:31,603 So back to the All Services worksheet. 529 00:32:33,190 --> 00:32:35,490 Let's go to Data, Forecast group, 530 00:32:35,490 --> 00:32:38,130 What-If Analysis, Scenario Manager. 531 00:32:38,130 --> 00:32:40,840 And if you click on Summary, you can find an option 532 00:32:40,840 --> 00:32:44,670 to activate the Scenario PivotTable report. 533 00:32:44,670 --> 00:32:46,740 Checking to make sure the result cells 534 00:32:46,740 --> 00:32:51,030 are still C17 to E17 which is our Profit Per Hour. 535 00:32:51,030 --> 00:32:52,970 Go ahead and click OK. 536 00:32:52,970 --> 00:32:55,610 And you have a new Scenario PivotTable sheet 537 00:32:55,610 --> 00:32:58,090 with a PivotTable available. 538 00:32:58,090 --> 00:33:00,580 The first thing it mentions that it wants us to do 539 00:33:00,580 --> 00:33:04,320 is while the PivotTable is activated, 540 00:33:04,320 --> 00:33:06,060 we want to remove this filter. 541 00:33:06,060 --> 00:33:08,310 Now I'm not getting my PivotTable task pane. 542 00:33:08,310 --> 00:33:11,220 So if this happens to you, you want to go up 543 00:33:11,220 --> 00:33:14,330 to the PivotTable Analyze tab 544 00:33:14,330 --> 00:33:16,480 and go ahead and activate the Field List. 545 00:33:16,480 --> 00:33:19,940 Sometimes it disappears when we are using another task pane 546 00:33:19,940 --> 00:33:23,470 or when we're in a more of a minimized view. 547 00:33:23,470 --> 00:33:25,320 So the Field List activates it 548 00:33:25,320 --> 00:33:28,300 on the PivotTable Analyze, all right? 549 00:33:28,300 --> 00:33:31,050 So notice that there is a filter applied, 550 00:33:31,050 --> 00:33:32,650 and you can see it also in Row 1 551 00:33:32,650 --> 00:33:35,030 and it just wants us to remove that filter. 552 00:33:35,030 --> 00:33:37,150 You can either use the dropdown arrow 553 00:33:37,150 --> 00:33:38,860 and choose to remove the field, 554 00:33:38,860 --> 00:33:41,660 or you can drag and drop it into the document window 555 00:33:41,660 --> 00:33:44,183 and the filter will be removed. 556 00:33:46,660 --> 00:33:49,410 All right, down here in the PivotTable task pane 557 00:33:49,410 --> 00:33:54,410 it wants us to format the values in the PivotTable 558 00:33:54,540 --> 00:33:56,730 to be currency with two decimal points 559 00:33:57,830 --> 00:33:59,830 and a dollar sign symbol. 560 00:33:59,830 --> 00:34:03,880 So I'm just gonna, with practice, use my dropdown arrows 561 00:34:03,880 --> 00:34:07,140 on these fields, choose the Value Field setting, 562 00:34:07,140 --> 00:34:09,300 and go into the Number Formatting 563 00:34:09,300 --> 00:34:13,370 and set it up for Currency, two decimals, 564 00:34:13,370 --> 00:34:16,010 the dollar sign by default is there. 565 00:34:16,010 --> 00:34:19,610 Do the same thing, so click OK, and do the same thing 566 00:34:19,610 --> 00:34:22,643 for the other values in the PivotTable. 567 00:34:25,190 --> 00:34:28,133 Once again, Number Format, Currency, 568 00:34:31,160 --> 00:34:33,593 and then make sure you get the last one as well. 569 00:34:44,010 --> 00:34:45,570 Go to cell B3. 570 00:34:45,570 --> 00:34:47,630 You can do this in the PivotTable task pane 571 00:34:47,630 --> 00:34:50,480 or you can simply click on cell B3 572 00:34:50,480 --> 00:34:53,800 and change that little bit of an awkward 573 00:34:53,800 --> 00:34:56,610 column heading there to profit, person, 574 00:34:56,610 --> 00:34:59,893 oh let me double-check the name of that, Personal Training. 575 00:35:03,320 --> 00:35:07,530 I'll tab to the next field in cell C3 576 00:35:07,530 --> 00:35:09,493 where they'd like it to say Consulting, 577 00:35:12,820 --> 00:35:16,363 and cell D3 should say Corporate Programs. 578 00:35:19,920 --> 00:35:21,980 Those are more appropriate column headings there 579 00:35:21,980 --> 00:35:22,930 for the PivotTable. 580 00:35:24,641 --> 00:35:29,117 In cell A1, let's go ahead and type in Profit Per Hour 581 00:35:33,880 --> 00:35:35,610 as the report title, 582 00:35:35,610 --> 00:35:38,910 and then search for the appropriate cell style. 583 00:35:38,910 --> 00:35:41,700 We would like it to have the title cell style. 584 00:35:41,700 --> 00:35:43,970 So I'm on the ribbon in the Styles group, 585 00:35:43,970 --> 00:35:47,593 Home tab, Styles group, dropdown list Title. 586 00:35:51,230 --> 00:35:55,500 We'd like to resize columns A through D to 17 characters, 587 00:35:55,500 --> 00:35:58,113 so I'm gonna select all four columns. 588 00:36:00,040 --> 00:36:04,533 I'm gonna right-click, choose Column Width, type in 17.00. 589 00:36:05,700 --> 00:36:08,090 That's exactly what the instructions ask for, 590 00:36:08,090 --> 00:36:09,653 so that's what I'm going to do. 591 00:36:11,720 --> 00:36:13,670 All right, we haven't saved for awhile 592 00:36:13,670 --> 00:36:16,300 so let's go ahead and deselect the PivotTable 593 00:36:16,300 --> 00:36:20,430 and do a quick Control + S to save our changes. 594 00:36:20,430 --> 00:36:22,340 You might want to save more often than I have 595 00:36:22,340 --> 00:36:25,230 just to make sure you make it through a task 596 00:36:25,230 --> 00:36:27,210 and you're ready to go on without any worries 597 00:36:27,210 --> 00:36:29,080 of your work being saved. 598 00:36:29,080 --> 00:36:32,730 One last step is to add a PivotChart 599 00:36:32,730 --> 00:36:36,330 to the Scenario PivotTable worksheet as follows. 600 00:36:36,330 --> 00:36:38,520 It will be a Clustered Column PivotChart 601 00:36:38,520 --> 00:36:40,600 based on that PivotTable. 602 00:36:40,600 --> 00:36:42,610 We'll resize and reposition the chart 603 00:36:42,610 --> 00:36:46,200 so that it covers the range A8 to D21 604 00:36:46,200 --> 00:36:48,453 and then we will hide the Field Buttons. 605 00:36:49,990 --> 00:36:51,350 So back to the PivotTable. 606 00:36:51,350 --> 00:36:53,180 Make sure it is active, 607 00:36:53,180 --> 00:36:57,410 and then we'll go ahead and create a PivotChart. 608 00:36:59,070 --> 00:37:04,070 On the PivotTable Analyze tab, if your PivotTable is active 609 00:37:04,840 --> 00:37:06,510 you've got a PivotChart button 610 00:37:06,510 --> 00:37:08,150 that you can go ahead and use. 611 00:37:08,150 --> 00:37:12,563 Clustered Column is actually the default, so click OK. 612 00:37:14,150 --> 00:37:17,563 All right, let's move that chart so it's in cell A8 to D21. 613 00:37:31,827 --> 00:37:36,350 And hide the Fields Button in the chart, okay? 614 00:37:36,350 --> 00:37:39,900 So we've got these, these can be distracting on a PivotChart 615 00:37:39,900 --> 00:37:44,900 so if you feel like they are annoying or distracting 616 00:37:45,580 --> 00:37:47,740 go up to your ribbon and kinda look around 617 00:37:47,740 --> 00:37:50,440 and see if you can find a tool to hide those. 618 00:37:50,440 --> 00:37:53,880 It's gotta be on one of your contextual tabs. 619 00:37:53,880 --> 00:37:56,090 It's on PivotChart Analyze, 620 00:37:56,090 --> 00:37:59,903 if I choose the Field Buttons arrow I can Hide All. 621 00:38:04,860 --> 00:38:07,890 Be sure to check your final figures on the following pages 622 00:38:07,890 --> 00:38:11,010 of the instructions and then do one final save 623 00:38:11,010 --> 00:38:13,530 on your changes, close the workbook, 624 00:38:13,530 --> 00:38:17,310 and upload your project. 625 00:38:17,310 --> 00:38:18,223 Thanks, everyone!