YouTube2Text

Estimation of Cash Flows in MS Excel | Dr. Rishabh Gupta #BCH #BCP #3rdsemester #FinancialManagement — Transcript

by GYAN PRASAR KENDRA · 1,509 words · 225 segments · language en · Watch on YouTube

Full transcript

  1. 0:00a very good morning to one and all
  2. 0:02myself Dr isab Gupta and in today's
  3. 0:05lecture we will be practicing a
  4. 0:07numerical on estimation of cash flows in
  5. 0:11msxl so here we begin M limited is
  6. 0:15evaluating a capital budgeting project
  7. 0:18cash outlay is given 120 lakh working
  8. 0:21capital requirement is 30 lakh other
  9. 0:24details sales volume 1 lakh units
  10. 0:28selling price R 150 per unit variable
  11. 0:32cost per unit is rup 80 fixed operating
  12. 0:36and maintenance cost excluding
  13. 0:39depreciation is rupees 15 lakh overhead
  14. 0:43allocation is 10% of sales depreciation
  15. 0:47is as for diminishing balance
  16. 0:50method next post tax salvage value at
  17. 0:53the end of eth year is rupees 2 lakh tax
  18. 0:57rate is
  19. 0:5840% life of a project is 8 years and the
  20. 1:02cut off rate is
  21. 1:0412% while the project is charged an
  22. 1:07overhead allocation it is not likely to
  23. 1:11have any effect on the total overhead
  24. 1:13expenses as
  25. 1:15such at the end of eighth year working
  26. 1:19capital is expected to be liquidated at
  27. 1:21par bearing an estimated loss of rupees
  28. 1:255 lakh on account of B debt which will
  29. 1:28be a tax deductable expense based upon
  30. 1:31this information you need to indicate
  31. 1:34the financial viability of a project
  32. 1:37using npv technique so here we begin
  33. 1:40this is a format for the evaluation of
  34. 1:43our Capital budgeting project involving
  35. 1:46initial cash flows subsequent cash flows
  36. 1:49and the terminal cash flows so let's
  37. 1:53begin so in this case your cash outlay
  38. 1:57is rupees 120 lakhs
  39. 2:00and the working capital is 30
  40. 2:04lakh now the next component is sales
  41. 2:08revenue so the question clearly specify
  42. 2:12that the selling price is rupees 150 per
  43. 2:14unit and there are 1 lakh units as such
  44. 2:18since we have specified that information
  45. 2:20in lak so our sales revenue will be 150
  46. 2:23in year 1 again 150 in year 2 and again
  47. 2:28150 in all the subsequent years that is
  48. 2:30from year 1 to year 8 so let's drag it
  49. 2:34from year 1 till year
  50. 2:368 the next component is variable cost
  51. 2:40the question clearly states that the
  52. 2:42variable cost per unit is rupees 80 and
  53. 2:45there are 1 lakh such unit so it will be
  54. 2:4780 lakh for year 1 again 80 lakh for
  55. 2:51year two and the same continues till
  56. 2:54year 10 as and the same continues till
  57. 2:57year 8 as well
  58. 3:01the next component is fixed operating
  59. 3:03overheads now in this numerical the
  60. 3:05fixed operating overheads are 15 l in
  61. 3:08all the years starting from year 1 till
  62. 3:11year 8 I again repeat since the
  63. 3:14information is being given in L so I can
  64. 3:16simply write 15 in year 1 again 15 in
  65. 3:20year 2 and drag it till year8 the next
  66. 3:24component is the calculation of
  67. 3:26depreciation now the question says that
  68. 3:29the depreciation is as per diminishing
  69. 3:31balance so first and foremost I will
  70. 3:34calculate the depreciation
  71. 3:37amount now in this case cost is 120
  72. 3:41salvage value is 2 and the life is 8
  73. 3:45years let's prepare a depreciation
  74. 3:48schedule
  75. 3:51year depreciation for the purpose of
  76. 3:54calculating the depreciation from year 1
  77. 3:57till year 8 I need to type equals to DB
  78. 4:02the moment I type equals to DB and
  79. 4:05double click on it it is asking for cost
  80. 4:08so I click on it representing my M7
  81. 4:12comma salvage value which is 2 lak in my
  82. 4:15case represented by M8 again comma life
  83. 4:20is 8 years which is represented by 07
  84. 4:24comma since I am calculating this
  85. 4:26diminishing balance period is l11
  86. 4:29bracket close and enter it will be
  87. 4:334812 in this case my cost salvage value
  88. 4:38and the life will remain same for all
  89. 4:41the years that is for year 1 for year 2
  90. 4:44until year 8 so I can simply put a
  91. 4:47dollar sign that is shift
  92. 4:50+4 before and after M7 I need to follow
  93. 4:55the same practice for M8 as well
  94. 4:57representing my salvage value by putting
  95. 5:00a dollar sign before and after M and
  96. 5:04same goes for the life as
  97. 5:07well represented by 07 and press
  98. 5:11enter now what I will do I will simply
  99. 5:14drag it till year 8 so in this way my
  100. 5:17depreciation as per diminishing balance
  101. 5:20method is
  102. 5:2248.12.1
  103. 5:24l 17.27%
  104. 5:306.19
  105. 5:323.71
  106. 5:332.22 and 1.33 L let me simply
  107. 5:38incorporate this information in this
  108. 5:40format so equals to select this cell
  109. 5:45M11 again equals to 28.820934
  110. 6:16so in this way I have noted down my
  111. 6:18depreciation as per diminishing balance
  112. 6:20method by incorporating the depreciation
  113. 6:23Excel function that is DB now the next
  114. 6:25step is the calculation of profit before
  115. 6:28tax so I need need to write equals to we
  116. 6:31all know that for the calculation of
  117. 6:33profit before tax it will be
  118. 6:37sales minus variable cost minus fixed
  119. 6:43operating
  120. 6:44overheads minus
  121. 6:47depreciation this will give you 6.88 L I
  122. 6:52need to Simply drag it till year 8 as
  123. 6:54well so the amount will be 6.88 lakh for
  124. 6:58year 1 20 2 6.18 lakh for year 2
  125. 7:0337.73 lakhs for year 3 44. 66 lakh for
  126. 7:07year 4 48.8 one lakh for year 5 51.2 1
  127. 7:13lakh for year 6 52.7 eight lakh for year
  128. 7:177 and
  129. 7:1953.67 lakh for year 8 the next component
  130. 7:22is the B deck loss the question says
  131. 7:25that the B de loss is 5 lakh rupees
  132. 7:30so the net profit before tax will be
  133. 7:33profit before tax minus B Deb
  134. 7:38loss so I can notice that the profit
  135. 7:42before tax or the net profit before tax
  136. 7:44is changing only in the last year that
  137. 7:46is 48.67 LH now I need to calculate my
  138. 7:50tax rate which is at the rate of
  139. 7:5340% equals to
  140. 7:550.4 multiplied by net profit Forex
  141. 8:00represented by C11 it comes out to be
  142. 8:052.75 simply drag it from year 1 till
  143. 8:08year 8 the next component is calculation
  144. 8:11of profit after tax that is profit
  145. 8:13before tax represented by C11 minus text
  146. 8:19represented by C12 press enter it comes
  147. 8:23out to be
  148. 8:244.13 now I need to add back the
  149. 8:26depreciation so again equals to see
  150. 8:30it let's drag
  151. 8:35it now the next component is the
  152. 8:37addition of B de which is equals to J10
  153. 8:41in this case salvage value is 2 L now I
  154. 8:45need to add the working capital
  155. 8:47recovered which is 25 lakh then I need
  156. 8:50to calculate the total which is equals
  157. 8:51to profit after tax plus
  158. 8:55depreciation plus b Deb plus salvage
  159. 8:59value
  160. 9:00plus working capital
  161. 9:02recovered let's simply drag it till year
  162. 9:058 so it comes out to be 52.2 lakh for
  163. 9:08year 1 44.5 3 lakh for year 2 39.917906
  164. 9:2962.5 3 lakh for year eight the next is
  165. 9:33the incorporation of discounting factor
  166. 9:37so my discount rate or the cost of
  167. 9:39capital my cost of capital is 12% in
  168. 9:42this case so I need to type equals to 1
  169. 9:46divided by 1
  170. 9:50+ cost of capital represented by M20
  171. 9:55bracket
  172. 9:56close shift + 6 that is RA to
  173. 10:02power one represented by
  174. 10:06C2 and press enter it comes out to be
  175. 10:100.893 now my cost of capital remains
  176. 10:13same in all the subsequent year so let
  177. 10:15me put a dollar sign before and after
  178. 10:19M and now let's simply drag it till A8
  179. 10:24so in this way I have noted down my PVF
  180. 10:27of 12% against 1 one year against second
  181. 10:30year third year against 8 year which
  182. 10:34comes out to be
  183. 10:360.404 now let's calculate pbci which is
  184. 10:39equals to non discounted cash flows
  185. 10:43multiplied by the discount rate it comes
  186. 10:47out to be 46.6 5 for year 1
  187. 10:5135.50 for year 2 28.4 for year three and
  188. 10:56so and so forth
  189. 10:59the total pvci will be equals
  190. 11:08to C20 +
  191. 11:11D20 plus
  192. 11:15E20 plus F20 +
  193. 11:18G20 +
  194. 11:21H20 + I2 and plus j20 so in this way I
  195. 11:24have calculated the total present value
  196. 11:27of cash in flow let let me total it
  197. 11:30equals to
  198. 11:34sum and bracket close so it comes out to
  199. 11:38be
  200. 11:3921234
  201. 11:42L the total pvco will be equals to some
  202. 11:46bracket open and select this it comes
  203. 11:50out to be 150 l so my npv comes out to
  204. 11:56be equals to present present value of
  205. 11:59cash inflows minus present value of cash
  206. 12:06outflow the value comes out to be 62.3 4
  207. 12:11lakhs now I need to
  208. 12:13identify that whether I will accept this
  209. 12:16project or reject it now I need to apply
  210. 12:19an IF function equals to
  211. 12:22if double click on it we all know that
  212. 12:26if npv is greater than zero then I will
  213. 12:29accept the project otherwise reject it
  214. 12:32now in this case I will write equals
  215. 12:35equals to if bracket open c23
  216. 12:39represented by npv value greater than
  217. 12:42zero if this logical test holds true
  218. 12:46then I will accept this project
  219. 12:48otherwise I will reject it bracket close
  220. 12:53and press enter so it comes out to be
  221. 12:56accept so I will accept this project
  222. 12:59because my npv is greater than
  223. 13:02zero that's all from my side we'll meet
  224. 13:04you in my next session has have a nice
  225. 13:07day

About this transcript

This page contains the full transcript of Estimation of Cash Flows in MS Excel | Dr. Rishabh Gupta #BCH #BCP #3rdsemester #FinancialManagement by GYAN PRASAR KENDRA , generated from the public captions YouTube serves with the video. The transcript has 1,509 words across 225 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.