1 00:00:00,850 --> 00:00:04,863 - [Instructor] This is Excel, module nine, SAM Project 1a. 2 00:00:06,030 --> 00:00:09,053 We will be working with Mount Moreland Hospital, 3 00:00:10,030 --> 00:00:13,913 and learning how to perform financial calculations. 4 00:00:15,750 --> 00:00:19,280 Pranjali Kashyap is a financial analyst 5 00:00:19,280 --> 00:00:22,680 at Mount Moreland Hospital in Baltimore, Maryland. 6 00:00:22,680 --> 00:00:25,240 She is using Excel workbooks to analyze 7 00:00:25,240 --> 00:00:27,780 the financial data for a proposed program 8 00:00:27,780 --> 00:00:30,170 called Neighborhood Nurse. 9 00:00:30,170 --> 00:00:33,350 The program involves nurse and nurse practitioners 10 00:00:33,350 --> 00:00:36,480 providing healthcare services to Baltimore neighborhoods 11 00:00:36,480 --> 00:00:40,990 from a van outfitted with medical equipment and supplies. 12 00:00:40,990 --> 00:00:43,890 She asks for your help in correcting errors, 13 00:00:43,890 --> 00:00:47,920 and making financial calculations in the workbook. 14 00:00:47,920 --> 00:00:50,630 So, we will go to the Loan Payments worksheet. 15 00:00:50,630 --> 00:00:53,400 The hospital needs a loan to buy the medical van 16 00:00:53,400 --> 00:00:55,940 for the Neighborhood Nurse program. 17 00:00:55,940 --> 00:00:59,060 Before Pranjali can calculate the principal 18 00:00:59,060 --> 00:01:01,870 and interest payments on the loan, 19 00:01:01,870 --> 00:01:04,940 she asks you to correct the errors in the worksheet. 20 00:01:04,940 --> 00:01:08,360 So, we will be correcting the first error as follows. 21 00:01:08,360 --> 00:01:12,220 In cell H17 we'll use the Error Checking Command 22 00:01:12,220 --> 00:01:15,070 to identify the error in the cell, 23 00:01:15,070 --> 00:01:16,870 and then we will correct that error. 24 00:01:20,980 --> 00:01:24,710 So, if we look at the Loan Payments worksheet, 25 00:01:24,710 --> 00:01:29,710 specifically cell H17, we have a name error that is showing 26 00:01:31,360 --> 00:01:34,260 and we want to use the Error Checking command 27 00:01:34,260 --> 00:01:35,633 to help us correct that. 28 00:01:39,150 --> 00:01:42,040 So, if we go to the Formulas tab 29 00:01:42,040 --> 00:01:47,040 and then we look in the Formula Auditing group 30 00:01:47,060 --> 00:01:48,800 you'll see the Error Checking tool. 31 00:01:48,800 --> 00:01:50,460 Go ahead and click on that tool, 32 00:01:50,460 --> 00:01:52,450 and there are several ways to help us 33 00:01:52,450 --> 00:01:54,370 correct this name error. 34 00:01:54,370 --> 00:01:59,270 Help on this Error will launch you to the Microsoft site, 35 00:02:02,300 --> 00:02:05,240 and give you guidance on how you might correct 36 00:02:05,240 --> 00:02:07,650 a name error in a spreadsheet. 37 00:02:07,650 --> 00:02:10,550 So, you can read quite a bit about that 38 00:02:10,550 --> 00:02:12,820 and decide if that's the route you want to take 39 00:02:12,820 --> 00:02:14,500 to correct it. 40 00:02:14,500 --> 00:02:17,870 You can also try to Show the Calculations Steps 41 00:02:17,870 --> 00:02:21,230 for this particular cell, and it kind of stands out 42 00:02:21,230 --> 00:02:22,250 that there's a problem. 43 00:02:22,250 --> 00:02:24,390 We're trying to use the SOME function, 44 00:02:24,390 --> 00:02:28,150 but it's showing you, it's evaluating what's happening here 45 00:02:28,150 --> 00:02:30,110 and you can choose to Evaluate it, 46 00:02:30,110 --> 00:02:32,160 and it's letting you know that it is in fact 47 00:02:32,160 --> 00:02:33,463 a name problem. 48 00:02:34,300 --> 00:02:36,550 You can Ignore the error and move on, 49 00:02:36,550 --> 00:02:41,400 or you can simply check, choose to Edit in the Formula Bar. 50 00:02:41,400 --> 00:02:44,860 So, up in the Formula Bar, we can see that 51 00:02:44,860 --> 00:02:48,580 the error has to do with the way the SOME function has been 52 00:02:48,580 --> 00:02:51,880 spelled, as well as an at sign that shouldn't be there. 53 00:02:51,880 --> 00:02:55,783 So, we'll simply delete that, and type in the word SUM, 54 00:02:57,220 --> 00:02:58,403 and press Enter. 55 00:02:59,360 --> 00:03:01,870 Then we can go ahead and either Resume or close 56 00:03:01,870 --> 00:03:05,150 the Error Checking box, and you can see now that the error 57 00:03:05,150 --> 00:03:09,823 has been selected, with a zero balance at this point. 58 00:03:13,250 --> 00:03:16,150 Task number two wants us to correct the value error 59 00:03:16,150 --> 00:03:17,720 in the worksheet as follows. 60 00:03:17,720 --> 00:03:20,460 We'll be using the Trace Precedents arrows to find 61 00:03:20,460 --> 00:03:23,930 the source of the value error in cell C20. 62 00:03:23,930 --> 00:03:26,930 We'll correct that formula in cell C20, 63 00:03:26,930 --> 00:03:30,020 which should divide the remaining principal, 64 00:03:30,020 --> 00:03:35,020 the amount in cell C19, by the loan amount found in cell D5. 65 00:03:35,190 --> 00:03:38,360 Define the percentage of remaining principal. 66 00:03:38,360 --> 00:03:42,280 Then we'll fill the range D20 to G20 with the formula 67 00:03:42,280 --> 00:03:47,160 in cell C20 to correct the remaining value errors. 68 00:03:47,160 --> 00:03:50,190 And if our trace arrows are still active, 69 00:03:50,190 --> 00:03:51,513 we will remove those. 70 00:04:02,400 --> 00:04:04,700 So, if you click on cell C20 71 00:04:06,900 --> 00:04:10,070 and you come up to your Formulas tab, 72 00:04:10,070 --> 00:04:14,560 Formula Auditing group, and choose to Trace Precedents, 73 00:04:14,560 --> 00:04:18,820 you can see that the formula in cell C20 is dependent 74 00:04:18,820 --> 00:04:21,093 on the remaining percentage below that, 75 00:04:22,210 --> 00:04:26,300 and then it is divided by what is actually 76 00:04:26,300 --> 00:04:28,850 a column heading in cell D4. 77 00:04:28,850 --> 00:04:31,450 So, you know, right away that that must be a problem 78 00:04:31,450 --> 00:04:34,810 because you can't divide anything that is 79 00:04:34,810 --> 00:04:37,750 located in cell C4 at this point. 80 00:04:37,750 --> 00:04:40,330 So, I'm just gonna go up to my Formula Bar 81 00:04:40,330 --> 00:04:43,790 and our instructions said that it should be C19 82 00:04:43,790 --> 00:04:47,780 divided by cell D5, and D5 should be 83 00:04:47,780 --> 00:04:50,350 in an absolute cell reference. 84 00:04:50,350 --> 00:04:53,573 So, once we do that, the value error goes away, 85 00:04:53,573 --> 00:04:57,840 then we can actually auto-fill over to cell G5 86 00:04:57,840 --> 00:05:01,330 to correct that error in each one of those columns. 87 00:05:01,330 --> 00:05:04,510 Our trace precedents arrows are now gone. 88 00:05:04,510 --> 00:05:08,023 So, we don't need to worry about removing those. 89 00:05:09,780 --> 00:05:12,260 Let's go ahead and save our file at this point. 90 00:05:12,260 --> 00:05:14,373 Click on File, Save As. 91 00:05:17,290 --> 00:05:19,310 And then make sure that the name, 92 00:05:19,310 --> 00:05:22,200 I'm going to make sure I'm on my Desktop, 93 00:05:22,200 --> 00:05:26,840 and make sure the file name ends rather than with ones, 94 00:05:26,840 --> 00:05:29,373 make sure it ends in _2. 95 00:05:31,890 --> 00:05:34,623 And we're ready to try step three. 96 00:05:36,100 --> 00:05:39,220 Now, Pranjali is ready to calculate the annual principal 97 00:05:39,220 --> 00:05:42,170 and interest rates for the medical van. 98 00:05:42,170 --> 00:05:43,580 We will start by calculating 99 00:05:43,580 --> 00:05:47,320 the cumulative interest payments as follows. 100 00:05:47,320 --> 00:05:50,260 In cell C17, we'll enter a formula using 101 00:05:50,260 --> 00:05:53,400 the cumulative interest payment function 102 00:05:53,400 --> 00:05:57,150 to calculate the cumulative interest paid on the loan 103 00:05:57,150 --> 00:05:58,690 for year one, 104 00:05:58,690 --> 00:06:03,690 payment one in cell C15 through payment 12 in cell C16. 105 00:06:04,000 --> 00:06:08,420 We will use zero as the type argument in our formula 106 00:06:08,420 --> 00:06:13,100 because payments are made at the end of the period. 107 00:06:13,100 --> 00:06:16,670 We'll use absolute cell references for our rate, 108 00:06:16,670 --> 00:06:20,050 our number of periods, and our present value arguments, 109 00:06:20,050 --> 00:06:23,520 which are listed in the range D5 to D11, 110 00:06:23,520 --> 00:06:25,870 and we will use relative cell references 111 00:06:25,870 --> 00:06:28,770 for the start and end arguments. 112 00:06:28,770 --> 00:06:32,240 Once we have the CUMIPMT function completed, 113 00:06:32,240 --> 00:06:36,380 we will fill the range D17 to G17 with the formula 114 00:06:36,380 --> 00:06:40,660 in cell C17 to calculate the interest paid 115 00:06:40,660 --> 00:06:44,503 in years two through five and the total interest. 116 00:06:48,010 --> 00:06:50,490 All right, so, let's go to cell C17. 117 00:06:50,490 --> 00:06:53,430 We're going to use the cumulative interest payment. 118 00:06:53,430 --> 00:06:57,140 I'm going to use the Function Argument box for this one 119 00:06:57,140 --> 00:07:00,760 on the Formulas tab in the Financial dropdown list. 120 00:07:00,760 --> 00:07:05,293 Let's choose cumulative interest payment, or CUMIPMT. 121 00:07:10,060 --> 00:07:13,563 All right, the rate can be found in cell D7. 122 00:07:15,820 --> 00:07:18,910 You always want to choose the monthly interest rate 123 00:07:18,910 --> 00:07:20,900 and this needs an absolute cell reference. 124 00:07:20,900 --> 00:07:23,180 So, I've pressed the Function 4 key. 125 00:07:23,180 --> 00:07:28,160 The number of periods can be found in cell D9 126 00:07:28,160 --> 00:07:31,140 and that should contain an absolute cell reference. 127 00:07:31,140 --> 00:07:34,810 The present value is the loan amount in cell D5 128 00:07:34,810 --> 00:07:37,633 and that should also be an absolute cell reference. 129 00:07:53,670 --> 00:07:56,970 The start period for the first year can be found 130 00:07:56,970 --> 00:08:01,970 in cell C15, and the end period for the first year 131 00:08:01,970 --> 00:08:04,620 can be found in cell C16 132 00:08:04,620 --> 00:08:07,170 and these are relative cell references. 133 00:08:07,170 --> 00:08:09,470 The last thing you need to do is provide the timing 134 00:08:09,470 --> 00:08:10,303 for the payment. 135 00:08:10,303 --> 00:08:12,970 So, I'm going to scroll in the Function Argument box 136 00:08:12,970 --> 00:08:16,650 'til I can see the Type, and type a zero. 137 00:08:16,650 --> 00:08:20,200 Once again, that zero is actually letting us know 138 00:08:20,200 --> 00:08:22,800 that the payments are made at the end of the period. 139 00:08:23,690 --> 00:08:26,003 Then I can go ahead and click OK. 140 00:08:26,940 --> 00:08:30,190 And it shows the interest payment for year one. 141 00:08:30,190 --> 00:08:33,190 I can also then auto-fill over to year five 142 00:08:33,190 --> 00:08:36,323 to show interest payments for each of the five years. 143 00:08:38,090 --> 00:08:43,090 Let's Save by choosing Control + S 144 00:08:43,320 --> 00:08:46,713 and then go back to the instructions for step four. 145 00:08:48,090 --> 00:08:49,490 Step four wants us to calculate 146 00:08:49,490 --> 00:08:52,150 the cumulative principal payments as follows. 147 00:08:52,150 --> 00:08:55,710 In cell C18, we'll enter a formula using 148 00:08:55,710 --> 00:08:58,350 the cumulative principal function to calculate 149 00:08:58,350 --> 00:09:01,420 the cumulative principal paid for year one, 150 00:09:01,420 --> 00:09:06,280 payment one in cell C15 through payment 12 in cell C16. 151 00:09:06,280 --> 00:09:09,280 We will use zero as the type argument in our formula 152 00:09:09,280 --> 00:09:12,380 because payments are made at the end of the period. 153 00:09:12,380 --> 00:09:16,200 We will use absolute cell references where appropriate 154 00:09:16,200 --> 00:09:19,400 and relative cell references when we are providing 155 00:09:19,400 --> 00:09:23,510 start and end arguments, and then we'll fill the range 156 00:09:23,510 --> 00:09:28,010 D18 to G18 with the formula in cell C18 to calculate 157 00:09:28,010 --> 00:09:31,110 the principal paid in years two through five 158 00:09:31,110 --> 00:09:32,583 and the total principal. 159 00:09:36,550 --> 00:09:39,580 So, in cell C18 we will use 160 00:09:39,580 --> 00:09:43,060 the cumulative principal function. 161 00:09:43,060 --> 00:09:45,900 I'm going to do a one liner with this function 162 00:09:45,900 --> 00:09:48,150 for those of you who like to do that. 163 00:09:48,150 --> 00:09:52,220 So, type in equals, begin to type in CUMPRINC, 164 00:09:52,220 --> 00:09:54,290 or cumulative principal. 165 00:09:54,290 --> 00:09:57,600 Choose from the list the correct function. 166 00:09:57,600 --> 00:10:00,780 The rate once again is in cell D5. 167 00:10:00,780 --> 00:10:04,260 Press Function 4 to make it an absolute cell reference. 168 00:10:04,260 --> 00:10:07,190 Type a comma to take you to the number of periods 169 00:10:07,190 --> 00:10:09,810 of NPER filled. 170 00:10:09,810 --> 00:10:13,430 That can be found in cell D9. 171 00:10:13,430 --> 00:10:15,550 Make it an absolute cell reference. 172 00:10:15,550 --> 00:10:17,036 Type a comma. 173 00:10:17,036 --> 00:10:21,290 The present value is the amount of the loan. 174 00:10:21,290 --> 00:10:25,380 Looks like I've, for my number of periods, 175 00:10:25,380 --> 00:10:28,600 I think I've got that right, but I need to do 176 00:10:28,600 --> 00:10:31,610 my cumulative principal is actually 177 00:10:31,610 --> 00:10:34,560 the rate can be found in cell D7. 178 00:10:34,560 --> 00:10:37,120 So, go ahead and make that correction there, 179 00:10:37,120 --> 00:10:40,630 and then we can go ahead and provide the present value 180 00:10:40,630 --> 00:10:42,830 when prompted to do that. 181 00:10:42,830 --> 00:10:43,850 Let me start that one again. 182 00:10:43,850 --> 00:10:45,840 I wanna make sure that you get it right. 183 00:10:45,840 --> 00:10:50,573 So, equals CUM principal, cumulative principal. 184 00:10:51,428 --> 00:10:55,400 The rate is always the monthly rate. 185 00:10:55,400 --> 00:10:58,210 Make it an absolute cell reference. 186 00:10:58,210 --> 00:10:59,043 Type a comma. 187 00:10:59,043 --> 00:11:01,790 The number of periods can be found in cell D9. 188 00:11:01,790 --> 00:11:06,530 Type a absolute reference on that, type a comma. 189 00:11:06,530 --> 00:11:09,233 The present value can be found in cell D5. 190 00:11:10,600 --> 00:11:13,140 Make it an absolute reference, type a comma. 191 00:11:13,140 --> 00:11:18,060 The start period for year one is in cell C15, comma. 192 00:11:18,060 --> 00:11:22,023 The end period can be found in cell C16. 193 00:11:23,050 --> 00:11:26,040 Neither one of those have an absolute reference. 194 00:11:26,040 --> 00:11:28,620 They are relative cell references. 195 00:11:28,620 --> 00:11:31,410 Type a comma, and the last thing to do is provide 196 00:11:31,410 --> 00:11:33,770 the type of payment, and it is a zero, 197 00:11:33,770 --> 00:11:37,590 because it's at the end of period, close parenthesis, 198 00:11:37,590 --> 00:11:40,640 and you should have your principal payment for year one 199 00:11:41,500 --> 00:11:45,123 that you can auto-fill to year five. 200 00:11:46,750 --> 00:11:49,330 Go ahead and save through Control + S, 201 00:11:49,330 --> 00:11:51,213 and we'll start the next task. 202 00:11:52,650 --> 00:11:55,860 Task five says to go to the Depreciation worksheet. 203 00:11:55,860 --> 00:11:58,850 Pranjali needs to correct the errors on this worksheet 204 00:11:58,850 --> 00:12:02,900 before she can perform any depreciation calculations. 205 00:12:02,900 --> 00:12:05,560 So, we will correct the errors as follows. 206 00:12:05,560 --> 00:12:08,410 This time we will use the trace dependents arrows 207 00:12:08,410 --> 00:12:11,780 to determine whether the value error in cell D12 208 00:12:11,780 --> 00:12:14,780 is causing other errors in the worksheet. 209 00:12:14,780 --> 00:12:19,780 We will use the trace precedents arrows to find 210 00:12:19,820 --> 00:12:22,300 the source of the error in cell D12 211 00:12:22,300 --> 00:12:24,513 and then we'll correct that error. 212 00:12:30,060 --> 00:12:33,680 So, let's go ahead, go to the depreciation worksheet. 213 00:12:33,680 --> 00:12:37,263 We're specifically looking at cell D12, 214 00:12:41,430 --> 00:12:43,250 where there is an error. 215 00:12:43,250 --> 00:12:46,460 We need to use the trace dependent arrows. 216 00:12:46,460 --> 00:12:48,350 So, I'm gonna go to the Formulas tab 217 00:12:48,350 --> 00:12:50,020 in the Formula Auditing group 218 00:12:50,020 --> 00:12:53,160 and activate the Trace Dependents, 219 00:12:53,160 --> 00:12:57,100 and you can see that the cell below D12 and to the right 220 00:12:58,940 --> 00:13:01,810 is depending, both of those cells are depending 221 00:13:01,810 --> 00:13:05,150 on this cell to have the correct formula in there. 222 00:13:05,150 --> 00:13:07,630 So, it lets us know that there is a problem. 223 00:13:07,630 --> 00:13:11,610 If we look at the explanation of what 224 00:13:11,610 --> 00:13:13,423 the formula should be about, 225 00:13:18,410 --> 00:13:22,210 in task five c it tells us to correct the error, 226 00:13:22,210 --> 00:13:25,960 so the formula in D12 calculates the cumulative 227 00:13:25,960 --> 00:13:28,680 straight line depreciation of the medical van 228 00:13:29,770 --> 00:13:34,110 by adding the cumulative depreciation value in year one 229 00:13:34,110 --> 00:13:38,970 to the annual depreciation value in year two. 230 00:13:38,970 --> 00:13:41,940 So, in order to do that it looks like it has tried 231 00:13:41,940 --> 00:13:46,940 to take C12, which is year one, that part is correct, 232 00:13:47,380 --> 00:13:48,303 and add it to B11. 233 00:13:49,420 --> 00:13:53,210 B11 is once again a row heading. 234 00:13:53,210 --> 00:13:55,860 And so, our formula is not working well there. 235 00:13:55,860 --> 00:14:00,730 So, if we can double click this formula in cell D12, 236 00:14:00,730 --> 00:14:03,410 or go up to your Formula Bar and correct it, 237 00:14:03,410 --> 00:14:05,877 it should be C12 plus D11. 238 00:14:11,960 --> 00:14:16,460 Press Enter and it helps the numbers 239 00:14:16,460 --> 00:14:21,460 in the entire row 12 to be adjusted as well as in row 13. 240 00:14:24,670 --> 00:14:27,890 We are to remove any arrows that are still showing. 241 00:14:27,890 --> 00:14:30,840 So, I'll go back up to my ribbon in the Formulas tab, 242 00:14:30,840 --> 00:14:34,233 Formulas Auditing group, Remove Arrows. 243 00:14:39,070 --> 00:14:42,340 In task six, Pranjali wants to compare straight line 244 00:14:42,340 --> 00:14:45,450 depreciation amounts with declining balance depreciation 245 00:14:45,450 --> 00:14:48,470 amounts to determine which method is more favorable 246 00:14:48,470 --> 00:14:50,940 for the hospital's balance sheet. 247 00:14:50,940 --> 00:14:53,970 In the range D5 to D7 she estimates that 248 00:14:53,970 --> 00:14:58,070 the Neighborhood Nurse program will have $234,000 249 00:14:58,070 --> 00:15:00,200 in tangible assets at startup, 250 00:15:00,200 --> 00:15:02,900 and the useful life of these assets is seven years, 251 00:15:02,900 --> 00:15:07,180 with a salvage value of $37,440. 252 00:15:07,180 --> 00:15:09,210 We will start by calculating the straight line 253 00:15:09,210 --> 00:15:11,560 depreciation amounts as follows. 254 00:15:11,560 --> 00:15:14,680 We'll use the SLN function in cell C11 255 00:15:14,680 --> 00:15:17,310 to calculate the straight line depreciation. 256 00:15:17,310 --> 00:15:19,440 We'll use absolute references for the cost, 257 00:15:19,440 --> 00:15:22,120 salvage, and life arguments, 258 00:15:22,120 --> 00:15:25,260 and then we will fill the range D11 to I11 259 00:15:25,260 --> 00:15:27,930 with the formula in cell C11 260 00:15:27,930 --> 00:15:30,410 to calculate the annual and cumulative straight line 261 00:15:30,410 --> 00:15:32,943 depreciation in years two through seven. 262 00:15:35,420 --> 00:15:40,010 So, in cell C11 let's do the straight line depreciation. 263 00:15:40,010 --> 00:15:43,210 Formulas tab, Financial functions, 264 00:15:43,210 --> 00:15:46,610 specifically the straight line depreciation, 265 00:15:46,610 --> 00:15:49,490 or SLN, straight line. 266 00:15:49,490 --> 00:15:52,260 The cost can be found in cell D5, 267 00:15:52,260 --> 00:15:55,070 and must be an absolute cell reference. 268 00:15:55,070 --> 00:15:58,253 The salvage value can be found in cell D6. 269 00:15:59,450 --> 00:16:02,040 Absolute reference, and the life of the asset 270 00:16:02,040 --> 00:16:03,803 can be found in cell D7. 271 00:16:06,060 --> 00:16:08,210 Once again, only in those cells, 272 00:16:08,210 --> 00:16:10,220 and so, they're absolute references. 273 00:16:10,220 --> 00:16:13,320 Once we click OK, it calculates a straight line 274 00:16:13,320 --> 00:16:16,330 depreciation amount and can be auto-filled 275 00:16:16,330 --> 00:16:18,290 and you can see that each one of those amounts 276 00:16:18,290 --> 00:16:21,373 will be the same, because it's straight line depreciation. 277 00:16:23,300 --> 00:16:28,300 Let's now do then the declining balance depreciation method 278 00:16:28,860 --> 00:16:33,860 in cell C18 by following the same procedure that we did 279 00:16:34,170 --> 00:16:37,980 for the straight line, except for task seven D 280 00:16:40,520 --> 00:16:43,340 tells us, actually, it's seven b, 281 00:16:43,340 --> 00:16:46,740 is gonna have us use year one, which is cell C17 282 00:16:46,740 --> 00:16:50,450 as the current period for a declining balance function 283 00:16:50,450 --> 00:16:52,963 and then we'll fill the range appropriately. 284 00:16:56,110 --> 00:17:00,960 So, in cell C18, we'll use the declining balance function. 285 00:17:00,960 --> 00:17:04,770 This time, I will do it as a one line function 286 00:17:04,770 --> 00:17:08,893 equals DB for declining balance. 287 00:17:10,104 --> 00:17:11,870 The cost is required. 288 00:17:11,870 --> 00:17:16,570 So, we click on cell D5, make it an absolute reference, 289 00:17:16,570 --> 00:17:17,610 type a comma. 290 00:17:17,610 --> 00:17:22,400 The salvage from cell D6, making that an absolute reference. 291 00:17:22,400 --> 00:17:23,300 Type a comma. 292 00:17:23,300 --> 00:17:26,680 The life of the asset is in cell D7. 293 00:17:26,680 --> 00:17:28,820 Once again, make that an absolute cell reference. 294 00:17:28,820 --> 00:17:32,310 Type a comma, because we actually need the period now 295 00:17:32,310 --> 00:17:35,280 for the declining balance, and that can be found 296 00:17:35,280 --> 00:17:38,940 in cell C17, year one. 297 00:17:38,940 --> 00:17:41,930 Provide the closing parenthesis or press Enter 298 00:17:41,930 --> 00:17:43,425 and you'll see that the declining, 299 00:17:43,425 --> 00:17:47,350 the annual depreciation for declining balance number 300 00:17:47,350 --> 00:17:51,090 and then as you auto-fill this over to year seven 301 00:17:51,090 --> 00:17:52,800 you'll see that the numbers change 302 00:17:52,800 --> 00:17:57,800 and the depreciation becomes less as the years progress. 303 00:17:59,560 --> 00:18:01,883 Do a Save, Control + S to Save. 304 00:18:03,770 --> 00:18:05,510 And let's do task eight. 305 00:18:05,510 --> 00:18:08,690 Pranjali also wants to determine the depreciation balance 306 00:18:08,690 --> 00:18:12,220 for the first year and the last year of the useful life 307 00:18:12,220 --> 00:18:13,960 of the medical van. 308 00:18:13,960 --> 00:18:16,940 So, we will be using in cell E22 309 00:18:16,940 --> 00:18:20,160 we will use the sum of the years digit function, 310 00:18:20,160 --> 00:18:23,520 the SYD function, to calculate the depreciation 311 00:18:23,520 --> 00:18:28,440 for the first year, and then in cell E23 we will enter 312 00:18:28,440 --> 00:18:31,930 the formula using the sum of the years digit function 313 00:18:31,930 --> 00:18:35,230 to calculate the depreciation for the last year 314 00:18:35,230 --> 00:18:38,253 using the appropriate cell references to do that. 315 00:18:39,160 --> 00:18:41,713 So, let's go to cell E22. 316 00:18:45,620 --> 00:18:49,580 And we'll use the sum of the years digits formula, 317 00:18:49,580 --> 00:18:52,563 or function, from the Financial category. 318 00:18:53,630 --> 00:18:56,620 The cost is in cell D5. 319 00:18:56,620 --> 00:18:58,550 We're not copying this formula anywhere, 320 00:18:58,550 --> 00:19:01,423 so it doesn't need to have an absolute cell reference. 321 00:19:02,720 --> 00:19:05,770 The salvage value is in D6. 322 00:19:05,770 --> 00:19:09,520 The life is in cell D7 323 00:19:09,520 --> 00:19:13,760 and the period that we are looking for is the first year. 324 00:19:13,760 --> 00:19:17,890 So, you'll find that reference in cell C17, year one, 325 00:19:17,890 --> 00:19:18,843 and click OK. 326 00:19:20,690 --> 00:19:22,560 All right, let's do the sum of the years digit 327 00:19:22,560 --> 00:19:24,000 for the last year. 328 00:19:24,000 --> 00:19:29,000 I'll do one liner equals SYD for sum of the years digit. 329 00:19:30,440 --> 00:19:35,177 All right, the cost C5, or D5, comma. 330 00:19:36,390 --> 00:19:41,390 The salvage, D6, comma, and the life of asset 331 00:19:43,030 --> 00:19:48,030 is in cell D7, comma, and the period is year seven. 332 00:19:48,760 --> 00:19:51,760 So, you would select I7 for that. 333 00:19:51,760 --> 00:19:54,900 You can type a close parenthesis or you can press Enter 334 00:19:54,900 --> 00:19:58,090 and you will get the yearly depreciation allowance 335 00:19:58,090 --> 00:19:59,393 for the last year. 336 00:20:03,330 --> 00:20:04,730 Task nine tells us to go 337 00:20:04,730 --> 00:20:07,310 to the Earnings Projections worksheet. 338 00:20:07,310 --> 00:20:09,230 Pranjali has entered most of the income 339 00:20:09,230 --> 00:20:11,610 and expense data on the worksheet. 340 00:20:11,610 --> 00:20:13,760 She knows the income from municipal grants 341 00:20:13,760 --> 00:20:16,800 will be $25,000 in 2022, 342 00:20:16,800 --> 00:20:20,630 and estimates it will 40,000 in 2026. 343 00:20:20,630 --> 00:20:24,220 She needs to calculate the income from the municipal grants 344 00:20:24,220 --> 00:20:27,850 in the years 2023 through 2025. 345 00:20:27,850 --> 00:20:30,370 The grant should increase at a constant amount 346 00:20:30,370 --> 00:20:31,990 from year to year. 347 00:20:31,990 --> 00:20:35,370 So, we will be projecting the income from municipal grants 348 00:20:35,370 --> 00:20:40,370 for 2023 to 2025 using a linear trend interpolation. 349 00:20:45,170 --> 00:20:48,220 So, on the Earnings Projection worksheet 350 00:20:48,220 --> 00:20:49,910 we're going to row five. 351 00:20:49,910 --> 00:20:52,510 We're going to select the data for municipal grants, 352 00:20:52,510 --> 00:20:54,770 C5 to G5. 353 00:20:54,770 --> 00:20:56,763 We have a beginning and ending number. 354 00:20:59,250 --> 00:21:00,803 Then we'll go to the Home tab. 355 00:21:03,480 --> 00:21:05,980 And then in the Editing group under Fill 356 00:21:11,600 --> 00:21:12,603 Series. 357 00:21:14,110 --> 00:21:19,110 It has asked us to do a linear 358 00:21:20,840 --> 00:21:22,610 interpolation. 359 00:21:22,610 --> 00:21:24,450 Our series is in rows. 360 00:21:24,450 --> 00:21:27,850 The type is linear and it is a trend. 361 00:21:27,850 --> 00:21:31,070 So, we will choose those options and click OK. 362 00:21:31,070 --> 00:21:35,483 And we will get a forecast for the years 2023 through 2025. 363 00:21:38,038 --> 00:21:41,397 So, task 10 wants us to do a similar calculation 364 00:21:42,510 --> 00:21:45,340 to calculate the income from insurance reimbursements 365 00:21:45,340 --> 00:21:48,750 in the years 2023 to 2025. 366 00:21:48,750 --> 00:21:50,230 She knows the starting amount 367 00:21:50,230 --> 00:21:52,910 and has estimated the amount in 2026. 368 00:21:52,910 --> 00:21:54,860 She thinks this income will increase 369 00:21:54,860 --> 00:21:56,880 by a constant percentage. 370 00:21:56,880 --> 00:22:00,310 So, we will project the income from insurance reimbursements 371 00:22:00,310 --> 00:22:05,310 for 2023 to 2025 using a growth trend interpolation. 372 00:22:08,920 --> 00:22:12,980 So, we will be selecting C7 through G11. 373 00:22:12,980 --> 00:22:15,410 We'll go back on the Home tab in the editing group. 374 00:22:15,410 --> 00:22:18,620 We will find the Fill Series option 375 00:22:18,620 --> 00:22:21,910 and we once again our data is in a row. 376 00:22:21,910 --> 00:22:26,693 The type is growth, and it is a trend. 377 00:22:27,560 --> 00:22:32,100 So, the step value is not required in that situation. 378 00:22:32,100 --> 00:22:35,600 So, click OK and you'll see the trend 379 00:22:35,600 --> 00:22:38,210 and it will also adjust in the chart over here 380 00:22:38,210 --> 00:22:40,363 of the insurance reimbursements. 381 00:22:42,900 --> 00:22:46,230 Task 11 says that Pranjali needs to calculate 382 00:22:46,230 --> 00:22:51,230 the payroll expenses in the years 2023 through 2026. 383 00:22:52,200 --> 00:22:56,320 She knows the payroll will be 140,000 in 2022 384 00:22:56,320 --> 00:22:59,760 and will increase by at least 5% per year. 385 00:22:59,760 --> 00:23:03,410 So, we will project the payroll expenses as follows. 386 00:23:03,410 --> 00:23:07,450 Project the expenses for 2023 to 2026 387 00:23:07,450 --> 00:23:10,960 using a growth trend extrapolation, 388 00:23:10,960 --> 00:23:14,240 rather than an interpolation, and our step value 389 00:23:14,240 --> 00:23:18,520 will be 1.05, which is a 5% increase. 390 00:23:18,520 --> 00:23:21,020 So, this is all about payroll, 391 00:23:21,020 --> 00:23:23,393 and so, let's go ahead and do that. 392 00:23:27,196 --> 00:23:29,160 If you see the payroll beginning, 393 00:23:29,160 --> 00:23:31,670 payroll is 140,000 in 2022. 394 00:23:31,670 --> 00:23:35,260 We do not have an ending value in 2026. 395 00:23:35,260 --> 00:23:38,060 So, this makes this an extrapolation, 396 00:23:38,060 --> 00:23:40,320 rather than an interpolation. 397 00:23:40,320 --> 00:23:42,410 If you have your range selected then, 398 00:23:42,410 --> 00:23:45,363 go up to the Fill tool, choose Series. 399 00:23:46,820 --> 00:23:48,060 The data's in a row. 400 00:23:48,060 --> 00:23:51,930 It's going to be a growth extrapolation. 401 00:23:51,930 --> 00:23:54,700 We won't activate the Trend box, 402 00:23:54,700 --> 00:23:57,650 because our step value is going to 1.05 403 00:23:57,650 --> 00:24:00,793 which is a 5% increase in the step value. 404 00:24:01,720 --> 00:24:06,240 Go ahead and click OK and it kind of shows us 405 00:24:06,240 --> 00:24:08,610 a project of what will happen to income 406 00:24:08,610 --> 00:24:10,253 over the next few years. 407 00:24:12,180 --> 00:24:13,263 Go ahead and save. 408 00:24:17,220 --> 00:24:21,960 Task number 12 says the projected revenue line chart 409 00:24:21,960 --> 00:24:25,200 in the range H4 to Q19 shows the revenue 410 00:24:25,200 --> 00:24:29,740 Pranjali estimates for the years 2022 to 2026. 411 00:24:29,740 --> 00:24:34,160 She wants us to extend the projection into 2027. 412 00:24:34,160 --> 00:24:37,420 We will modify the projected revenue line chart as follows 413 00:24:37,420 --> 00:24:39,740 to forecast the future trend. 414 00:24:39,740 --> 00:24:42,280 We will add a linear trend line to the projected 415 00:24:42,280 --> 00:24:44,350 revenue line chart. 416 00:24:44,350 --> 00:24:46,760 Then we will format the trend line to forecast 417 00:24:46,760 --> 00:24:49,293 one period forward. 418 00:24:52,830 --> 00:24:54,740 So, on the Earnings Projection sheet, 419 00:24:54,740 --> 00:24:57,330 here's the projected revenues chart. 420 00:24:57,330 --> 00:24:59,863 Let's go ahead and click on the trend line here, 421 00:25:00,720 --> 00:25:03,763 and then we can choose our Add Chart Element tool, 422 00:25:05,830 --> 00:25:09,670 use the more arrow for different trend line options 423 00:25:09,670 --> 00:25:11,243 and choose Linear. 424 00:25:14,270 --> 00:25:17,240 After we've chosen Linear, we can go ahead 425 00:25:17,240 --> 00:25:20,340 and choose More Options and that will give us the ability 426 00:25:20,340 --> 00:25:24,103 to forecast one period forward. 427 00:25:25,810 --> 00:25:27,660 And you can see the change on the chart, 428 00:25:27,660 --> 00:25:30,130 and then I will close the Format Trendline 429 00:25:30,130 --> 00:25:32,270 task pane for a minute so you can see the chart 430 00:25:32,270 --> 00:25:33,523 just a little bit better. 431 00:25:35,150 --> 00:25:39,260 Task 13 says that the revenue trend scatter chart 432 00:25:39,260 --> 00:25:43,170 in range A21 to G40 is based on monthly revenue 433 00:25:43,170 --> 00:25:45,650 and estimates listed on 434 00:25:45,650 --> 00:25:48,290 the Monthly Revenue Projections worksheet. 435 00:25:48,290 --> 00:25:50,970 Pranjali wants to include a trend line for this chart 436 00:25:50,970 --> 00:25:53,650 that shows how revenues increase quickly at first 437 00:25:53,650 --> 00:25:56,160 and then level off in later months. 438 00:25:56,160 --> 00:25:59,630 So, we will modify the trend scatter chart as follows 439 00:25:59,630 --> 00:26:02,310 to include a logarithmic trend line. 440 00:26:02,310 --> 00:26:04,000 We will add a trend line to the chart 441 00:26:04,000 --> 00:26:06,400 and then format the trend line to use (mumbles), 442 00:26:09,018 --> 00:26:11,263 sorry, logarithmic option. 443 00:26:14,300 --> 00:26:17,070 So, on the Earnings Projections worksheet 444 00:26:17,070 --> 00:26:21,723 the revenue trend chart, click on the series revenue. 445 00:26:23,960 --> 00:26:27,093 Let's add a trend line. 446 00:26:32,370 --> 00:26:33,490 I'm gonna try that again. 447 00:26:33,490 --> 00:26:36,580 It seems like it made a strange selection for me. 448 00:26:36,580 --> 00:26:39,193 So, I'm gonna delete my trend line if I can. 449 00:26:40,550 --> 00:26:41,940 Let's try that again. 450 00:26:41,940 --> 00:26:46,940 Okay, so, with the chart selected, I can add a chart element 451 00:26:47,620 --> 00:26:49,150 and a trend line. 452 00:26:49,150 --> 00:26:52,430 That looks better, you can see the trend line taking place 453 00:26:52,430 --> 00:26:57,090 and then for more options I can choose 454 00:26:57,090 --> 00:26:59,340 to get my task pane open and choose 455 00:26:59,340 --> 00:27:02,263 the Logarithmic trend line option. 456 00:27:09,410 --> 00:27:12,513 Now, for some reason, I have more than one trend line here. 457 00:27:13,840 --> 00:27:16,690 So, make sure that you have the logarithmic 458 00:27:17,760 --> 00:27:19,563 trend line showing only. 459 00:27:25,860 --> 00:27:28,870 Task 14 wants us to go to the Investment worksheet. 460 00:27:28,870 --> 00:27:32,020 This worksheet should show the returns potential investors 461 00:27:32,020 --> 00:27:35,560 could realize if they invested $165,000 462 00:27:35,560 --> 00:27:37,960 in the Neighborhood Nurse program. 463 00:27:37,960 --> 00:27:40,460 Pranjali figures a desirable rate of return 464 00:27:40,460 --> 00:27:41,773 would be 7.3%. 465 00:27:42,750 --> 00:27:45,740 She estimates the investment would pay different amounts 466 00:27:45,740 --> 00:27:49,210 each year, which is in the range C7 to C12 467 00:27:49,210 --> 00:27:53,400 and wants to calculate the present value of the investment. 468 00:27:53,400 --> 00:27:56,550 So, we'll calculate the present value of the investment 469 00:27:56,550 --> 00:28:01,170 in cell C15 using the NPV function 470 00:28:01,170 --> 00:28:03,410 to calculate the present value of the investment 471 00:28:03,410 --> 00:28:06,810 in a medical van for the Neighborhood Nurse program. 472 00:28:06,810 --> 00:28:08,820 We will use the desired rate of return 473 00:28:08,820 --> 00:28:11,450 and cell C14 is the rate argument 474 00:28:11,450 --> 00:28:13,720 and the range of years one through six 475 00:28:13,720 --> 00:28:18,720 in cell C7 through C12 as the returns paid to the investors. 476 00:28:21,990 --> 00:28:26,473 All right, so, let's go to the investment worksheet, 477 00:28:30,250 --> 00:28:33,687 in cell C15. (computer chimes) 478 00:28:38,040 --> 00:28:40,530 And calculate the net present value. 479 00:28:40,530 --> 00:28:45,530 Formulas, Financial functions, net present value. 480 00:28:46,390 --> 00:28:50,280 The rate once again can be found in cell C14 just above. 481 00:28:50,280 --> 00:28:52,040 We don't need an absolute cell reference. 482 00:28:52,040 --> 00:28:55,930 We're not copying it anywhere, and value one should include 483 00:28:55,930 --> 00:28:59,290 the range C7 through C12, which is the payment 484 00:28:59,290 --> 00:29:00,713 over the six years. 485 00:29:03,240 --> 00:29:04,140 Close parenthesis. 486 00:29:05,170 --> 00:29:07,971 (computer chimes) 487 00:29:07,971 --> 00:29:09,476 Okay, let's see. 488 00:29:09,476 --> 00:29:12,900 Our instructions tell us that if we happen to find 489 00:29:14,400 --> 00:29:17,940 a formula that omits an adjacent cell error warning 490 00:29:17,940 --> 00:29:19,070 we're supposed to ignore it. 491 00:29:19,070 --> 00:29:21,720 So, don't worry about that if you've got an icon here 492 00:29:21,720 --> 00:29:22,850 with an exclamation point. 493 00:29:22,850 --> 00:29:24,980 They're having us ignore that. 494 00:29:24,980 --> 00:29:28,060 All right, let's then now do the last step, 495 00:29:28,060 --> 00:29:32,140 which is Pranjali also wants to calculate 496 00:29:32,140 --> 00:29:35,100 the internal rate of return on the investment. 497 00:29:35,100 --> 00:29:38,880 If it is 7% or higher, she is confident that 498 00:29:38,880 --> 00:29:41,020 she can attract investors. 499 00:29:41,020 --> 00:29:43,440 So, we will calculate the internal rate of return 500 00:29:43,440 --> 00:29:45,400 on the investment as follows. 501 00:29:45,400 --> 00:29:49,160 We will use the IRR function in cell C17 502 00:29:49,160 --> 00:29:52,680 to calculate that internal rate of return for investing 503 00:29:52,680 --> 00:29:56,000 in a medical van for the Neighborhood Nurse program, 504 00:29:56,000 --> 00:29:58,900 and we will use the payments for startup in years 505 00:29:58,900 --> 00:30:03,350 one through six, which are in the range C6 through C12 506 00:30:03,350 --> 00:30:06,063 as the returns paid to the investors. 507 00:30:08,260 --> 00:30:12,500 So, in cell C17 we will use the IRR 508 00:30:12,500 --> 00:30:15,543 internal rate of return function. 509 00:30:18,560 --> 00:30:21,780 All right, for values then, we want to just include 510 00:30:21,780 --> 00:30:26,370 the range C6, which includes the initial startup payment 511 00:30:26,370 --> 00:30:31,020 through C12, close parenthesis, and press Enter, 512 00:30:31,020 --> 00:30:35,030 and at 7.52% you would have the correct answer. 513 00:30:35,030 --> 00:30:38,060 To understand the IRR function just a little bit better, 514 00:30:38,060 --> 00:30:43,060 you could go to your e-text, specifically exercise 9-12c 515 00:30:43,320 --> 00:30:46,263 will give you more information about that function. 516 00:30:47,600 --> 00:30:50,720 So, your workbook should look like the final figures. 517 00:30:50,720 --> 00:30:53,110 I would double check those in the instructions. 518 00:30:53,110 --> 00:30:55,720 Let's do one last save of our document, 519 00:30:55,720 --> 00:30:58,670 so we have a final copy and we can go ahead 520 00:30:58,670 --> 00:31:00,360 and submit the project. 521 00:31:00,360 --> 00:31:01,373 Thank you everyone.