Module 9: End of Module Project 1

1 00:00:02,340 --> 00:00:07,140 all right this module line in the module

2 00:00:04,740 --> 00:00:09,420 project one and here in this step one it

3 00:00:07,140 --> 00:00:12,120 is saying Elena Gonzales works in the

4 00:00:09,420 --> 00:00:14,160 operation operations Department of a

5 00:00:12,120 --> 00:00:17,039 canyon transport a company providing

6 00:00:14,160 --> 00:00:20,100 delivery and Total Service okay so here

7 00:00:17,039 --> 00:00:21,900 is the main important instruction so go

8 00:00:20,100 --> 00:00:24,000 to the business plan worksheets on the

9 00:00:21,900 --> 00:00:26,939 bottom click here business plan tab

10 00:00:24,000 --> 00:00:30,180 interesting in the range A2 through b29

11 00:00:26,939 --> 00:00:31,859 L9 has already entered expenses asset

12 00:00:30,180 --> 00:00:34,980 and other information for the new year

13 00:00:31,859 --> 00:00:37,920 for total service now she needs to make

14 00:00:34,980 --> 00:00:40,980 a financial calculation in the range E4

15 00:00:37,920 --> 00:00:45,480 through h11 so in is in sale as 11 enter

16 00:00:40,980 --> 00:00:48,480 formula used with a PMT function

17 00:00:45,480 --> 00:00:51,539 okay so in cell E11

18 00:00:48,480 --> 00:00:54,960 it is saying PMD function so PMT and

19 00:00:51,539 --> 00:00:56,879 open parenthesis it takes rate NPR Okay

20 00:00:54,960 --> 00:00:59,699 so

21 00:00:56,879 --> 00:01:01,079 in function that uses rate for quarter

22 00:00:59,699 --> 00:01:02,640 so E10

23 00:01:01,079 --> 00:01:06,720 is the rate

24 00:01:02,640 --> 00:01:09,900 oops it is the total payment cell E8

25 00:01:06,720 --> 00:01:16,460 okay so E8 must be the NPR let's make

26 00:01:09,900 --> 00:01:18,780 sure so e 8 so this is NP here now uh

27 00:01:16,460 --> 00:01:20,820 the E4 as

28 00:01:18,780 --> 00:01:22,740 VV oops

29 00:01:20,820 --> 00:01:24,900 so E4

30 00:01:22,740 --> 00:01:28,140 so let's make sure here is the E4 this

31 00:01:24,900 --> 00:01:31,020 is PV and then let's see if this needs

32 00:01:28,140 --> 00:01:33,780 FP or not so E4 to calculate over okay

33 00:01:31,020 --> 00:01:35,400 so FB and type are the optional argument

34 00:01:33,780 --> 00:01:39,360 so we don't need this

35 00:01:35,400 --> 00:01:41,939 now close parenthesis and then hit enter

36 00:01:39,360 --> 00:01:46,079 all right so step two it is saying in

37 00:01:41,939 --> 00:01:51,119 cell F5 enter a formula with FB function

38 00:01:46,079 --> 00:01:52,140 okay so in sale F5 F5 it is an FB

39 00:01:51,119 --> 00:01:54,960 function

40 00:01:52,140 --> 00:01:57,240 okay so it takes read and pass the same

41 00:01:54,960 --> 00:01:59,220 thing that uses the rate per quarter so

42 00:01:57,240 --> 00:02:00,899 this time it's yeah 10

43 00:01:59,220 --> 00:02:03,420 and then

44 00:02:00,899 --> 00:02:06,299 here

45 00:02:03,420 --> 00:02:10,039 and then quarterly payment amount

46 00:02:06,299 --> 00:02:10,039 okay yes 11.

47 00:02:10,220 --> 00:02:18,000 and then principle value F4 so PV should

48 00:02:14,160 --> 00:02:20,700 be F4 to calculate so to calculate the

49 00:02:18,000 --> 00:02:22,319 future value okay so this time it

50 00:02:20,700 --> 00:02:25,080 requires to enter this additional value

51 00:02:22,319 --> 00:02:27,540 so F4 is PV

52 00:02:25,080 --> 00:02:29,879 all right so to calculate the future

53 00:02:27,540 --> 00:02:32,400 value of the loan assuming the quarterly

54 00:02:29,879 --> 00:02:34,560 payments are limited to fifteen thousand

55 00:02:32,400 --> 00:02:37,319 so this is not we don't require this

56 00:02:34,560 --> 00:02:39,360 information in this formula so that's

57 00:02:37,319 --> 00:02:43,500 all what we need

58 00:02:39,360 --> 00:02:45,599 now we hit enter okay so how do I know

59 00:02:43,500 --> 00:02:49,080 so this does not require enough formula

60 00:02:45,599 --> 00:02:51,780 is so when we when you enter this FB

61 00:02:49,080 --> 00:02:53,879 okay let's let me show you so when you

62 00:02:51,780 --> 00:02:55,980 in the react V and the open transition

63 00:02:53,879 --> 00:02:58,319 so these are the arguments you need to

64 00:02:55,980 --> 00:03:00,180 to complete this formula so it gave us

65 00:02:58,319 --> 00:03:02,580 red it gave us and we are it gave us

66 00:03:00,180 --> 00:03:04,140 this and it also gave us PV which is

67 00:03:02,580 --> 00:03:07,379 principal value

68 00:03:04,140 --> 00:03:10,200 but it didn't mean some type anywhere so

69 00:03:07,379 --> 00:03:12,000 that way we can know the formula is done

70 00:03:10,200 --> 00:03:13,640 and then that is just the additional

71 00:03:12,000 --> 00:03:15,360 value which is repeating somewhere

72 00:03:13,640 --> 00:03:17,159 fifteen thousand

73 00:03:15,360 --> 00:03:18,599 somewhere around here or it is doing

74 00:03:17,159 --> 00:03:24,480 some calculation

75 00:03:18,599 --> 00:03:27,300 okay now Ctrl C and oops

76 00:03:24,480 --> 00:03:29,640 so let's go to step three in cell G8 it

77 00:03:27,300 --> 00:03:35,220 is saying enter a formula with NP ER

78 00:03:29,640 --> 00:03:40,140 function so here is the G8

79 00:03:35,220 --> 00:03:43,200 using NPR function so it takes rate so

80 00:03:40,140 --> 00:03:46,560 it is saying Red Cell G10

81 00:03:43,200 --> 00:03:48,900 okay red and then PMT

82 00:03:46,560 --> 00:03:51,500 okay

83 00:03:48,900 --> 00:03:51,500 foreign

84 00:03:53,840 --> 00:04:04,019 and then PV will be this one G4

85 00:03:59,879 --> 00:04:07,440 and reducing G5 okay so you have the

86 00:04:04,019 --> 00:04:11,459 future value will be G5

87 00:04:07,440 --> 00:04:13,560 yes G5 now close parenthesis

88 00:04:11,459 --> 00:04:16,979 and then hit enter

89 00:04:13,560 --> 00:04:20,940 okay now step four in cell layouts 4 so

90 00:04:16,979 --> 00:04:24,600 in CL at 4 it is an interval now with PV

91 00:04:20,940 --> 00:04:25,860 function okay so PV function it takes

92 00:04:24,600 --> 00:04:29,400 rate

93 00:04:25,860 --> 00:04:32,699 that uses rate for quarter cell as 10

94 00:04:29,400 --> 00:04:36,780 okay and then total payment so nper as

95 00:04:32,699 --> 00:04:40,979 eight so here is that's it it is NP here

96 00:04:36,780 --> 00:04:44,280 and then PMT should be this S11 yeah as

97 00:04:40,979 --> 00:04:46,979 eleven to calculate the present value

98 00:04:44,280 --> 00:04:50,220 okay if they got really okay now close

99 00:04:46,979 --> 00:04:53,880 parenthesis and they need enter

100 00:04:50,220 --> 00:04:56,520 okay now is day five it is saying now

101 00:04:53,880 --> 00:04:58,919 Elena asks you to calculate the annual

102 00:04:56,520 --> 00:05:02,040 principle and interest payment for the

103 00:04:58,919 --> 00:05:04,860 year for Total Service expansion go to

104 00:05:02,040 --> 00:05:07,919 the low end details worksheet okay so

105 00:05:04,860 --> 00:05:12,259 here you want details worksheet and then

106 00:05:07,919 --> 00:05:16,259 in cell B9 enter a formula using so here

107 00:05:12,259 --> 00:05:19,320 okay in cell B9 oops

108 00:05:16,259 --> 00:05:23,340 in cell B9

109 00:05:19,320 --> 00:05:26,820 all right so it is saying come

110 00:05:23,340 --> 00:05:29,880 okay come clink so cumulative principle

111 00:05:26,820 --> 00:05:32,940 uh to calculated principle paid for year

112 00:05:29,880 --> 00:05:35,699 one payment one in cell B7 through

113 00:05:32,940 --> 00:05:39,000 payment four in cell B8

114 00:05:35,699 --> 00:05:40,620 okay and it is saying U zero as the type

115 00:05:39,000 --> 00:05:42,360 argument in your formula because

116 00:05:40,620 --> 00:05:44,759 payments are made at the end of the

117 00:05:42,360 --> 00:05:47,880 period use absolute reference for the

118 00:05:44,759 --> 00:05:50,300 rate Envy as so now so you you should

119 00:05:47,880 --> 00:05:52,919 make sure to read all these instruction

120 00:05:50,300 --> 00:05:54,780 because sometimes it doesn't give the

121 00:05:52,919 --> 00:05:56,820 instructions so for instance it is

122 00:05:54,780 --> 00:05:58,860 saying u0 as the type argument which is

123 00:05:56,820 --> 00:06:02,160 the last type but after that it is

124 00:05:58,860 --> 00:06:03,840 saying use rate NPR from this range so

125 00:06:02,160 --> 00:06:06,300 that's why you need to

126 00:06:03,840 --> 00:06:09,000 read all the instructions first if you

127 00:06:06,300 --> 00:06:11,100 get confused so this this needs to be

128 00:06:09,000 --> 00:06:13,680 done at the last so let's do this

129 00:06:11,100 --> 00:06:15,780 pattern first and it is saying use these

130 00:06:13,680 --> 00:06:20,039 which are listed on the Range A3 through

131 00:06:15,780 --> 00:06:21,539 G3 and okay so relative reference all

132 00:06:20,039 --> 00:06:25,440 right

133 00:06:21,539 --> 00:06:27,840 so far it needs red which is at a three

134 00:06:25,440 --> 00:06:29,880 through G3 so let's see so this is the

135 00:06:27,840 --> 00:06:34,020 rate and then let's see where is the

136 00:06:29,880 --> 00:06:36,780 amperes and we are here payments and NPR

137 00:06:34,020 --> 00:06:39,479 and then PV so let's see what is so

138 00:06:36,780 --> 00:06:41,699 here's the PV loan freebie so and you

139 00:06:39,479 --> 00:06:43,319 make sure select this not this you can't

140 00:06:41,699 --> 00:06:47,039 select the text you want to select the

141 00:06:43,319 --> 00:06:50,220 value Excel value now start period

142 00:06:47,039 --> 00:06:54,240 okay so stop your start period is from

143 00:06:50,220 --> 00:06:56,639 payment one through payment four one and

144 00:06:54,240 --> 00:06:59,280 then end period will be payment for so

145 00:06:56,639 --> 00:07:02,220 payment 1 and payment four and in Period

146 00:06:59,280 --> 00:07:04,639 will be payment 4 and the decent type so

147 00:07:02,220 --> 00:07:04,639 zero

148 00:07:05,819 --> 00:07:13,259 okay and then close parenthesis

149 00:07:09,240 --> 00:07:15,600 and then it enter all right okay one

150 00:07:13,259 --> 00:07:20,580 second I think I made a mistake so

151 00:07:15,600 --> 00:07:23,099 instead of one or four you can do this

152 00:07:20,580 --> 00:07:25,979 and then

153 00:07:23,099 --> 00:07:28,500 this and now this instruction makes

154 00:07:25,979 --> 00:07:31,020 sense so it is a use relative reference

155 00:07:28,500 --> 00:07:33,000 for the start and in the argument so you

156 00:07:31,020 --> 00:07:36,060 can adjust enter one and four you need

157 00:07:33,000 --> 00:07:38,639 to give a risk reference to the cell

158 00:07:36,060 --> 00:07:42,660 all right so be saving in this is the

159 00:07:38,639 --> 00:07:44,880 start one payment one and then B8 is the

160 00:07:42,660 --> 00:07:46,699 in the payment four all right so hit

161 00:07:44,880 --> 00:07:50,220 enter

162 00:07:46,699 --> 00:07:53,039 now it is saying you feel the range C9

163 00:07:50,220 --> 00:07:56,039 through F9 okay

164 00:07:53,039 --> 00:08:00,780 so C9 through F9

165 00:07:56,039 --> 00:08:02,819 and then to calculate okay so go to go

166 00:08:00,780 --> 00:08:04,319 to the corner plus sign up here and then

167 00:08:02,819 --> 00:08:07,740 drag it until

168 00:08:04,319 --> 00:08:09,660 you have nine oops

169 00:08:07,740 --> 00:08:12,120 let's see

170 00:08:09,660 --> 00:08:14,220 oh it was saying absolutely so we missed

171 00:08:12,120 --> 00:08:15,240 it it was saying use absolute reference

172 00:08:14,220 --> 00:08:17,340 for this

173 00:08:15,240 --> 00:08:19,800 you might have done that

174 00:08:17,340 --> 00:08:21,319 but I missed it okay so for absolute

175 00:08:19,800 --> 00:08:23,699 reference you you so you can either

176 00:08:21,319 --> 00:08:28,680 surround this by dollar sign or just

177 00:08:23,699 --> 00:08:30,599 press F4 select the cell press F4 again

178 00:08:28,680 --> 00:08:32,039 so instant period and infinity is saying

179 00:08:30,599 --> 00:08:35,640 relative so we don't need to do anything

180 00:08:32,039 --> 00:08:38,399 and then zero is just the value okay so

181 00:08:35,640 --> 00:08:41,339 it will give same thing but when we

182 00:08:38,399 --> 00:08:44,219 expand this so when we expand this this

183 00:08:41,339 --> 00:08:46,620 absolute will make it constant uh

184 00:08:44,219 --> 00:08:49,140 constant so it will not change so for

185 00:08:46,620 --> 00:08:51,540 this p7 and V8 will change in other cell

186 00:08:49,140 --> 00:08:54,120 because it is relative reference but in

187 00:08:51,540 --> 00:08:56,279 this absolute reference so D is also

188 00:08:54,120 --> 00:08:59,580 locked and then three is also locked so

189 00:08:56,279 --> 00:09:02,399 if we put dollar sign then this

190 00:08:59,580 --> 00:09:04,620 and these column and three zero so row

191 00:09:02,399 --> 00:09:07,080 is also locked and then column is also

192 00:09:04,620 --> 00:09:10,200 locked so it will be same in every every

193 00:09:07,080 --> 00:09:13,080 cell so if we lay here so this is change

194 00:09:10,200 --> 00:09:17,399 C7 C additions but these are locked

195 00:09:13,080 --> 00:09:20,880 okay so step 5 is down now step six

196 00:09:17,399 --> 00:09:23,580 in cell B10 okay B10 it is saying enter

197 00:09:20,880 --> 00:09:28,560 formula using okay

198 00:09:23,580 --> 00:09:30,899 so it is cumulative and then ipmt okay

199 00:09:28,560 --> 00:09:33,120 interest paid between two period so

200 00:09:30,899 --> 00:09:34,980 double click and it will feel it and

201 00:09:33,120 --> 00:09:38,339 then it is saying

202 00:09:34,980 --> 00:09:42,060 okay so payment one in CLP seven through

203 00:09:38,339 --> 00:09:44,640 payment four in cell B8

204 00:09:42,060 --> 00:09:47,339 and then use absolute reference for so

205 00:09:44,640 --> 00:09:51,000 it is basically the same same arguments

206 00:09:47,339 --> 00:09:54,720 so for read these and then NPR is these

207 00:09:51,000 --> 00:09:56,100 and then PV is these and then each start

208 00:09:54,720 --> 00:09:58,980 period is

209 00:09:56,100 --> 00:10:01,740 this and then end period is this and

210 00:09:58,980 --> 00:10:04,279 then it is saying to enter 0 in the type

211 00:10:01,740 --> 00:10:07,140 yeah here

212 00:10:04,279 --> 00:10:10,920 but don't forget this will be absolute

213 00:10:07,140 --> 00:10:13,260 so these three should be absolute so

214 00:10:10,920 --> 00:10:16,740 make sure you surrounded by dollar sign

215 00:10:13,260 --> 00:10:20,640 or press F4 by selecting this set now

216 00:10:16,740 --> 00:10:21,839 hit enter okay it is saying C 10 through

217 00:10:20,640 --> 00:10:25,260 F10

218 00:10:21,839 --> 00:10:28,740 so let's drag it on the Left End

219 00:10:25,260 --> 00:10:30,899 alrighty so step seven it is saying go

220 00:10:28,740 --> 00:10:32,940 to the buyer lease worksheet so on the

221 00:10:30,899 --> 00:10:35,100 button click here and then it's saying

222 00:10:32,940 --> 00:10:37,560 area wants to compare the cost of buying

223 00:10:35,100 --> 00:10:39,480 a subtle boss with the cost of leasing

224 00:10:37,560 --> 00:10:44,120 once he has entered information for both

225 00:10:39,480 --> 00:10:44,120 scenario okay okay okay so

226 00:10:44,420 --> 00:10:50,519 so this is the important one so initial

227 00:10:47,279 --> 00:10:54,860 G4 enter a formula that subtracts oops

228 00:10:50,519 --> 00:10:54,860 E4 my back so in cell

229 00:10:56,640 --> 00:11:02,160 E4 okay

230 00:11:00,120 --> 00:11:05,579 enter formula that subtracts the result

231 00:11:02,160 --> 00:11:07,860 of TV function from initial asset which

232 00:11:05,579 --> 00:11:09,899 is E3 so it is saying from that means we

233 00:11:07,860 --> 00:11:12,120 need to subtract this we need to

234 00:11:09,899 --> 00:11:16,740 subtract this from this cell so from

235 00:11:12,120 --> 00:11:18,540 means E3 negative subtract and then DV

236 00:11:16,740 --> 00:11:20,880 function okay so let's see what goes

237 00:11:18,540 --> 00:11:23,220 into the DV function

238 00:11:20,880 --> 00:11:25,500 highlights a bit in this and in DB

239 00:11:23,220 --> 00:11:29,100 function use absolute reference for cost

240 00:11:25,500 --> 00:11:30,959 and then self is in life are you Miss

241 00:11:29,100 --> 00:11:35,760 which are listed on the Range B3 through

242 00:11:30,959 --> 00:11:39,079 B5 all right so cost here is the cost B3

243 00:11:35,760 --> 00:11:42,920 and then here is the Salvage and then

244 00:11:39,079 --> 00:11:42,920 life okay

245 00:11:43,079 --> 00:11:46,260 and it is saying use absolutely solids

246 00:11:45,600 --> 00:11:49,019 too

247 00:11:46,260 --> 00:11:51,480 you try it because we might forget later

248 00:11:49,019 --> 00:11:54,000 all right now for the life

249 00:11:51,480 --> 00:11:55,980 so it is saying a use a relative reason

250 00:11:54,000 --> 00:11:57,240 for the period again so for period

251 00:11:55,980 --> 00:12:01,320 agreements it is saying relative

252 00:11:57,240 --> 00:12:03,959 reference so just do D for nothing else

253 00:12:01,320 --> 00:12:06,600 okay so we don't need a month that is

254 00:12:03,959 --> 00:12:08,579 optional now close parenthesis and then

255 00:12:06,600 --> 00:12:12,360 hit enter now it is saying fill the

256 00:12:08,579 --> 00:12:15,540 range E5 through E39 okay

257 00:12:12,360 --> 00:12:16,980 using formula without formatting

258 00:12:15,540 --> 00:12:19,860 all right so it is saying without

259 00:12:16,980 --> 00:12:22,920 formatting so just Ctrl C copy this

260 00:12:19,860 --> 00:12:25,320 formula and then select it until

261 00:12:22,920 --> 00:12:27,800 until E39

262 00:12:25,320 --> 00:12:27,800 okay

263 00:12:27,839 --> 00:12:35,240 until E39 and then right click and then

264 00:12:31,740 --> 00:12:35,240 paste only the formulas

265 00:12:38,399 --> 00:12:45,420 okay now paste just the floor all right

266 00:12:42,980 --> 00:12:48,240 and then it escape to

267 00:12:45,420 --> 00:12:51,480 that

268 00:12:48,240 --> 00:12:54,180 okay so fill the range E5

269 00:12:51,480 --> 00:12:58,880 okay E4 was done E5

270 00:12:54,180 --> 00:12:58,880 through e 39 all right

271 00:12:59,220 --> 00:13:03,899 now arena is ready to calculate the net

272 00:13:01,500 --> 00:13:05,880 net present value of buying if it is

273 00:13:03,899 --> 00:13:09,060 greater than okay okay you can escape

274 00:13:05,880 --> 00:13:11,760 these let's see so in the cell B10 so

275 00:13:09,060 --> 00:13:14,760 this is the this is the important part

276 00:13:11,760 --> 00:13:16,079 and this is also input in fact but if

277 00:13:14,760 --> 00:13:18,959 you start reading this you might get

278 00:13:16,079 --> 00:13:21,660 confused because it is saying this B22

279 00:13:18,959 --> 00:13:23,880 display these B22 display this but we

280 00:13:21,660 --> 00:13:26,519 actually need to enter formula line B20

281 00:13:23,880 --> 00:13:29,459 this is the step for B20

282 00:13:26,519 --> 00:13:31,920 so while you read the question makes

283 00:13:29,459 --> 00:13:34,440 your resource for drum like this in cell

284 00:13:31,920 --> 00:13:37,560 these insult this this is also important

285 00:13:34,440 --> 00:13:40,320 but you might get confused okay so let's

286 00:13:37,560 --> 00:13:42,600 start here in cell B20 internal formula

287 00:13:40,320 --> 00:13:47,399 that adds the initial invest investment

288 00:13:42,600 --> 00:13:50,880 amount of buying the subtle pass so in

289 00:13:47,399 --> 00:13:53,720 cell B20 let's go to beat one

290 00:13:50,880 --> 00:13:53,720 right here

291 00:13:54,180 --> 00:13:59,459 equals to sine

292 00:13:55,860 --> 00:14:02,399 and then that adds the initial

293 00:13:59,459 --> 00:14:05,880 after buying so you have three

294 00:14:02,399 --> 00:14:07,519 that adds F3 to the result of npv

295 00:14:05,880 --> 00:14:10,380 function okay

296 00:14:07,519 --> 00:14:13,380 npv function let's see what goes into

297 00:14:10,380 --> 00:14:17,399 npv function and now in the npv function

298 00:14:13,380 --> 00:14:19,500 use the monthly discount rate clp19 okay

299 00:14:17,399 --> 00:14:21,839 so b19 further read

300 00:14:19,500 --> 00:14:24,480 as the rate of the return and the buy

301 00:14:21,839 --> 00:14:27,660 scenario value for months 1 through 36

302 00:14:24,480 --> 00:14:29,160 and the ending value okay so these are

303 00:14:27,660 --> 00:14:33,620 the range

304 00:14:29,160 --> 00:14:38,459 for Value 1 so range is

305 00:14:33,620 --> 00:14:38,459 4 through f 40.

306 00:14:39,139 --> 00:14:45,480 okay so 1 through 36 for months 1

307 00:14:42,660 --> 00:14:48,660 through 36 so these are the ones one

308 00:14:45,480 --> 00:14:52,980 through 36 and the ending value so

309 00:14:48,660 --> 00:14:55,860 ending value means this F40 so here is

310 00:14:52,980 --> 00:14:59,160 F4 through F40

311 00:14:55,860 --> 00:15:02,160 okay as the glass gas for flows all

312 00:14:59,160 --> 00:15:03,959 right that's it so close parenthesis and

313 00:15:02,160 --> 00:15:07,139 then hit enter

314 00:15:03,959 --> 00:15:09,060 okay now step 10 it is saying Elena also

315 00:15:07,139 --> 00:15:11,279 also needs to compare the straight line

316 00:15:09,060 --> 00:15:12,839 D3 application amount with declining

317 00:15:11,279 --> 00:15:14,760 balance so go to the application

318 00:15:12,839 --> 00:15:16,940 worksheet

319 00:15:14,760 --> 00:15:16,940 foreign

320 00:15:26,480 --> 00:15:31,320 has already entered the security deposit

321 00:15:29,040 --> 00:15:34,740 in G3

322 00:15:31,320 --> 00:15:38,339 okay let's see G3 G3

323 00:15:34,740 --> 00:15:41,220 yeah Z3 as the initial investment for

324 00:15:38,339 --> 00:15:44,699 the leasing and then in cell B21 okay so

325 00:15:41,220 --> 00:15:47,180 that is the main part so in cell B21 it

326 00:15:44,699 --> 00:15:47,180 is saying

327 00:15:48,199 --> 00:15:53,040 this is basically the same thing as a

328 00:15:50,519 --> 00:15:56,240 book that's why I got confused okay so

329 00:15:53,040 --> 00:16:00,959 it is the same thing we need to add G3

330 00:15:56,240 --> 00:16:03,360 with npv function and it takes rate so

331 00:16:00,959 --> 00:16:08,339 rate will be b19

332 00:16:03,360 --> 00:16:10,320 okay and then range will be G4 through G

333 00:16:08,339 --> 00:16:13,199 40

334 00:16:10,320 --> 00:16:16,019 okay and then close parenthesis

335 00:16:13,199 --> 00:16:17,699 all right so for this it was F4 through

336 00:16:16,019 --> 00:16:21,060 F40

337 00:16:17,699 --> 00:16:22,199 already now step 10 it is you're gonna

338 00:16:21,060 --> 00:16:24,480 also need to compute the state

339 00:16:22,199 --> 00:16:26,880 identification okay so go to the

340 00:16:24,480 --> 00:16:29,880 duplication worksheet

341 00:16:26,880 --> 00:16:31,440 click here duplication and then initial

342 00:16:29,880 --> 00:16:33,839 B9 okay

343 00:16:31,440 --> 00:16:36,300 so B9

344 00:16:33,839 --> 00:16:40,680 it is an intervala using the sln

345 00:16:36,300 --> 00:16:42,480 function okay let's enter s l n and

346 00:16:40,680 --> 00:16:44,579 let's see what it takes to cause Salvage

347 00:16:42,480 --> 00:16:46,620 and life

348 00:16:44,579 --> 00:16:48,720 okay function use absolute reference for

349 00:16:46,620 --> 00:16:51,980 this okay which are stored in the range

350 00:16:48,720 --> 00:16:51,980 B3 through B5

351 00:16:52,019 --> 00:16:58,579 let's see so this is cost

352 00:16:54,300 --> 00:17:02,339 and then this is always and then this is

353 00:16:58,579 --> 00:17:06,660 it was saying absolute reference groups

354 00:17:02,339 --> 00:17:09,839 so select all these and then F4

355 00:17:06,660 --> 00:17:10,799 okay now close parenthesis and then

356 00:17:09,839 --> 00:17:14,640 enter

357 00:17:10,799 --> 00:17:18,000 ing field there is C5 c93 F9 with the

358 00:17:14,640 --> 00:17:21,059 formula in there is to calculate okay

359 00:17:18,000 --> 00:17:23,400 so c93 F9

360 00:17:21,059 --> 00:17:26,280 all right so go to the corner plus

361 00:17:23,400 --> 00:17:29,660 signal right here and then drag it until

362 00:17:26,280 --> 00:17:29,660 we have an idea

363 00:17:30,440 --> 00:17:37,280 so we use the absolute reference so it

364 00:17:33,960 --> 00:17:37,280 will be the same same value

365 00:17:37,320 --> 00:17:42,660 all right now in cell p15 internal

366 00:17:40,980 --> 00:17:44,039 formula using the DB function to

367 00:17:42,660 --> 00:17:49,320 calculate okay

368 00:17:44,039 --> 00:17:53,100 beef in B15 here let's see here it is

369 00:17:49,320 --> 00:17:55,860 saying you use DB function

370 00:17:53,100 --> 00:17:59,160 and then it takes these arguments so

371 00:17:55,860 --> 00:18:00,480 function to calculate use ER 1 b 14 as a

372 00:17:59,160 --> 00:18:02,700 current period

373 00:18:00,480 --> 00:18:04,740 oops DB function

374 00:18:02,700 --> 00:18:06,900 okay

375 00:18:04,740 --> 00:18:10,340 we use absolute reference only for the

376 00:18:06,900 --> 00:18:13,620 cost yeah so current period is is here

377 00:18:10,340 --> 00:18:16,320 so we need to read whole whole

378 00:18:13,620 --> 00:18:18,240 instructions so now it is saying for

379 00:18:16,320 --> 00:18:21,059 cost always and life it is saying

380 00:18:18,240 --> 00:18:24,320 absolute value and then

381 00:18:21,059 --> 00:18:28,679 okay so these are the same value cost

382 00:18:24,320 --> 00:18:32,419 and then some ways and then life but for

383 00:18:28,679 --> 00:18:35,460 periods it has given here so period as b

384 00:18:32,419 --> 00:18:37,860 14 as current period and then it was

385 00:18:35,460 --> 00:18:40,260 saying absolute reference for these okay

386 00:18:37,860 --> 00:18:42,500 so absolute reference for this you have

387 00:18:40,260 --> 00:18:42,500 four

388 00:18:42,780 --> 00:18:48,539 and then let's see it requires month or

389 00:18:45,299 --> 00:18:50,880 not so yeah it does not require month so

390 00:18:48,539 --> 00:18:53,940 you can do close parenthesis and then

391 00:18:50,880 --> 00:18:58,380 enter noticing fill the range C15

392 00:18:53,940 --> 00:18:59,100 through yeah 15 using the formula okay

393 00:18:58,380 --> 00:19:02,880 thank you

394 00:18:59,100 --> 00:19:06,320 so let's see so drag it until you have

395 00:19:02,880 --> 00:19:08,880 15 oops have it okay

396 00:19:06,320 --> 00:19:11,160 now step 12 it is saying go to the

397 00:19:08,880 --> 00:19:13,679 profit and loss worksheet all right

398 00:19:11,160 --> 00:19:15,240 profit and loss worksheet and the

399 00:19:13,679 --> 00:19:17,220 editing relay has entered most of the

400 00:19:15,240 --> 00:19:22,220 income expenses data on the worksheet

401 00:19:17,220 --> 00:19:27,120 see estimates Revenue will be okay so

402 00:19:22,220 --> 00:19:29,520 825 000 in year one and 1.4 million in

403 00:19:27,120 --> 00:19:32,340 year 5 of the shuttle service she needs

404 00:19:29,520 --> 00:19:34,620 to calculate revenue for year 2 through

405 00:19:32,340 --> 00:19:37,260 4 so revenues will increase at a

406 00:19:34,620 --> 00:19:38,960 constant amount from year to year so

407 00:19:37,260 --> 00:19:42,059 okay

408 00:19:38,960 --> 00:19:46,020 project the revenue for year two through

409 00:19:42,059 --> 00:19:47,160 four okay so C7 through E7

410 00:19:46,020 --> 00:19:50,640 here

411 00:19:47,160 --> 00:19:53,700 using a linear Trend interpolation

412 00:19:50,640 --> 00:19:56,340 all right so we need to use linear Trend

413 00:19:53,700 --> 00:19:58,080 interpolation to predict this value

414 00:19:56,340 --> 00:20:00,539 and it is saying it will raise in

415 00:19:58,080 --> 00:20:02,700 constant in constant amount from year to

416 00:20:00,539 --> 00:20:05,640 year so for that

417 00:20:02,700 --> 00:20:08,039 it's easy so for that just select all

418 00:20:05,640 --> 00:20:11,400 these range so from this to this and

419 00:20:08,039 --> 00:20:14,280 here also and then go to editing tab

420 00:20:11,400 --> 00:20:16,679 or if you are in full window here is the

421 00:20:14,280 --> 00:20:18,240 editing group and then click here and

422 00:20:16,679 --> 00:20:20,220 then click on series

423 00:20:18,240 --> 00:20:22,320 all right so I will

424 00:20:20,220 --> 00:20:25,620 I'll count this view so editing group

425 00:20:22,320 --> 00:20:28,080 and then fill and then see this

426 00:20:25,620 --> 00:20:30,299 okay so this is the row and then it is

427 00:20:28,080 --> 00:20:32,820 saying linear linear Trend interpolation

428 00:20:30,299 --> 00:20:35,039 so make sure you click this linear it is

429 00:20:32,820 --> 00:20:37,260 in default and it is sync trained so

430 00:20:35,039 --> 00:20:40,679 make sure you check mark this trend and

431 00:20:37,260 --> 00:20:42,419 then hit OK all right now it also it

432 00:20:40,679 --> 00:20:45,539 will grow in constant amount from this

433 00:20:42,419 --> 00:20:48,059 to this so it will so if you subtract

434 00:20:45,539 --> 00:20:49,860 this by this and then this by this so

435 00:20:48,059 --> 00:20:51,660 This value and this value will be same

436 00:20:49,860 --> 00:20:55,140 because it is growing and constant

437 00:20:51,660 --> 00:20:57,600 amount and it is figured out by Excel

438 00:20:55,140 --> 00:21:02,220 okay

439 00:20:57,600 --> 00:21:05,039 now so yeah step 12 is done now Step 13

440 00:21:02,220 --> 00:21:07,200 last step it is saying Elena also needs

441 00:21:05,039 --> 00:21:10,020 to calculate expenses for payroll and

442 00:21:07,200 --> 00:21:11,820 range okay seniors she knows the

443 00:21:10,020 --> 00:21:14,720 starting amount for each expenses and

444 00:21:11,820 --> 00:21:17,100 estimate the range rent in year five

445 00:21:14,720 --> 00:21:20,400 estimates the range in year five will be

446 00:21:17,100 --> 00:21:22,500 sixty four thousand she expects expects

447 00:21:20,400 --> 00:21:27,080 the payroll expenses to increase by at

448 00:21:22,500 --> 00:21:27,080 least six percent okay so project

449 00:21:28,880 --> 00:21:33,780 so rent will increase at constant rate

450 00:21:31,440 --> 00:21:34,740 but expenses will increase at six

451 00:21:33,780 --> 00:21:37,980 percent

452 00:21:34,740 --> 00:21:43,200 okay so project the expenses for payroll

453 00:21:37,980 --> 00:21:47,820 in year two to five so C 13 through f13

454 00:21:43,200 --> 00:21:51,179 so here you see 13 through f13

455 00:21:47,820 --> 00:21:54,000 using growth Trend interpolation okay so

456 00:21:51,179 --> 00:21:57,960 we also need to select these

457 00:21:54,000 --> 00:22:00,120 okay so this is payroll and payroll will

458 00:21:57,960 --> 00:22:02,720 increase okay

459 00:22:00,120 --> 00:22:06,000 and payroll will increase as

460 00:22:02,720 --> 00:22:08,280 1.06 six percent increase

461 00:22:06,000 --> 00:22:10,559 okay so same thing go to editing field

462 00:22:08,280 --> 00:22:14,480 series

463 00:22:10,559 --> 00:22:16,860 and anything growth growth at

464 00:22:14,480 --> 00:22:19,260 1.06 value that means six percent

465 00:22:16,860 --> 00:22:20,820 increase so it will increase by six

466 00:22:19,260 --> 00:22:22,559 percent and then it will take this value

467 00:22:20,820 --> 00:22:24,659 again increase by six percent six

468 00:22:22,559 --> 00:22:26,340 percent six percent so we don't need to

469 00:22:24,659 --> 00:22:29,880 check mark the trend because it is not

470 00:22:26,340 --> 00:22:31,620 the trend and so so in the before step

471 00:22:29,880 --> 00:22:33,659 here we have given the first value and

472 00:22:31,620 --> 00:22:35,400 the last value and then it calculates

473 00:22:33,659 --> 00:22:36,720 the value in Britain using the trend

474 00:22:35,400 --> 00:22:40,020 because

475 00:22:36,720 --> 00:22:41,820 it figured out if we if we subtract this

476 00:22:40,020 --> 00:22:44,100 by this it will give one value and then

477 00:22:41,820 --> 00:22:45,960 if we subtract this by this it will give

478 00:22:44,100 --> 00:22:48,299 the same value it figured out using the

479 00:22:45,960 --> 00:22:50,520 trend so here we don't need to use Trend

480 00:22:48,299 --> 00:22:51,720 because it will figure out by adding

481 00:22:50,520 --> 00:22:54,179 this with

482 00:22:51,720 --> 00:22:56,220 one with six percent of this and and

483 00:22:54,179 --> 00:22:59,220 then again these biodic six percent of

484 00:22:56,220 --> 00:23:01,919 this value so it will do like this so we

485 00:22:59,220 --> 00:23:05,120 don't need to check mark the trend now

486 00:23:01,919 --> 00:23:08,280 hit OK all right

487 00:23:05,120 --> 00:23:10,559 but for this rent we need to check mark

488 00:23:08,280 --> 00:23:12,720 the trend because we have only given

489 00:23:10,559 --> 00:23:15,600 these and this we have not given this

490 00:23:12,720 --> 00:23:18,179 growth percentage growth percentage so

491 00:23:15,600 --> 00:23:20,159 without using Trend we cannot find this

492 00:23:18,179 --> 00:23:22,020 value

493 00:23:20,159 --> 00:23:23,960 okay so make sure we select this and

494 00:23:22,020 --> 00:23:27,600 this oops

495 00:23:23,960 --> 00:23:30,720 this from this and then it is saying

496 00:23:27,600 --> 00:23:32,760 okay project expenses using a growth

497 00:23:30,720 --> 00:23:36,720 Trend interpolation all right

498 00:23:32,760 --> 00:23:39,000 so fill and then see this and then

499 00:23:36,720 --> 00:23:40,919 growth oops growth and then make sure

500 00:23:39,000 --> 00:23:42,780 you take month to trade

501 00:23:40,919 --> 00:23:45,659 and I need okay

502 00:23:42,780 --> 00:23:48,659 so here we so your Trend makes sense

503 00:23:45,659 --> 00:23:51,960 because this Excel does not know at what

504 00:23:48,659 --> 00:23:53,820 percentage or at what value this this

505 00:23:51,960 --> 00:23:56,039 will increase from this cell to this

506 00:23:53,820 --> 00:23:58,620 cell so it needs strain to figure out

507 00:23:56,039 --> 00:24:01,940 and then it did the

508 00:23:58,620 --> 00:24:01,940 and create a job