Estimation of Cash Flows in MS Excel | Dr. Rishabh Gupta #BCH #BCP #3rdsemester #FinancialManagement — Transcript
Full transcript
- 0:00a very good morning to one and all
- 0:02myself Dr isab Gupta and in today's
- 0:05lecture we will be practicing a
- 0:07numerical on estimation of cash flows in
- 0:11msxl so here we begin M limited is
- 0:15evaluating a capital budgeting project
- 0:18cash outlay is given 120 lakh working
- 0:21capital requirement is 30 lakh other
- 0:24details sales volume 1 lakh units
- 0:28selling price R 150 per unit variable
- 0:32cost per unit is rup 80 fixed operating
- 0:36and maintenance cost excluding
- 0:39depreciation is rupees 15 lakh overhead
- 0:43allocation is 10% of sales depreciation
- 0:47is as for diminishing balance
- 0:50method next post tax salvage value at
- 0:53the end of eth year is rupees 2 lakh tax
- 0:57rate is
- 0:5840% life of a project is 8 years and the
- 1:02cut off rate is
- 1:0412% while the project is charged an
- 1:07overhead allocation it is not likely to
- 1:11have any effect on the total overhead
- 1:13expenses as
- 1:15such at the end of eighth year working
- 1:19capital is expected to be liquidated at
- 1:21par bearing an estimated loss of rupees
- 1:255 lakh on account of B debt which will
- 1:28be a tax deductable expense based upon
- 1:31this information you need to indicate
- 1:34the financial viability of a project
- 1:37using npv technique so here we begin
- 1:40this is a format for the evaluation of
- 1:43our Capital budgeting project involving
- 1:46initial cash flows subsequent cash flows
- 1:49and the terminal cash flows so let's
- 1:53begin so in this case your cash outlay
- 1:57is rupees 120 lakhs
- 2:00and the working capital is 30
- 2:04lakh now the next component is sales
- 2:08revenue so the question clearly specify
- 2:12that the selling price is rupees 150 per
- 2:14unit and there are 1 lakh units as such
- 2:18since we have specified that information
- 2:20in lak so our sales revenue will be 150
- 2:23in year 1 again 150 in year 2 and again
- 2:28150 in all the subsequent years that is
- 2:30from year 1 to year 8 so let's drag it
- 2:34from year 1 till year
- 2:368 the next component is variable cost
- 2:40the question clearly states that the
- 2:42variable cost per unit is rupees 80 and
- 2:45there are 1 lakh such unit so it will be
- 2:4780 lakh for year 1 again 80 lakh for
- 2:51year two and the same continues till
- 2:54year 10 as and the same continues till
- 2:57year 8 as well
- 3:01the next component is fixed operating
- 3:03overheads now in this numerical the
- 3:05fixed operating overheads are 15 l in
- 3:08all the years starting from year 1 till
- 3:11year 8 I again repeat since the
- 3:14information is being given in L so I can
- 3:16simply write 15 in year 1 again 15 in
- 3:20year 2 and drag it till year8 the next
- 3:24component is the calculation of
- 3:26depreciation now the question says that
- 3:29the depreciation is as per diminishing
- 3:31balance so first and foremost I will
- 3:34calculate the depreciation
- 3:37amount now in this case cost is 120
- 3:41salvage value is 2 and the life is 8
- 3:45years let's prepare a depreciation
- 3:48schedule
- 3:51year depreciation for the purpose of
- 3:54calculating the depreciation from year 1
- 3:57till year 8 I need to type equals to DB
- 4:02the moment I type equals to DB and
- 4:05double click on it it is asking for cost
- 4:08so I click on it representing my M7
- 4:12comma salvage value which is 2 lak in my
- 4:15case represented by M8 again comma life
- 4:20is 8 years which is represented by 07
- 4:24comma since I am calculating this
- 4:26diminishing balance period is l11
- 4:29bracket close and enter it will be
- 4:334812 in this case my cost salvage value
- 4:38and the life will remain same for all
- 4:41the years that is for year 1 for year 2
- 4:44until year 8 so I can simply put a
- 4:47dollar sign that is shift
- 4:50+4 before and after M7 I need to follow
- 4:55the same practice for M8 as well
- 4:57representing my salvage value by putting
- 5:00a dollar sign before and after M and
- 5:04same goes for the life as
- 5:07well represented by 07 and press
- 5:11enter now what I will do I will simply
- 5:14drag it till year 8 so in this way my
- 5:17depreciation as per diminishing balance
- 5:20method is
- 5:2248.12.1
- 5:24l 17.27%
- 5:306.19
- 5:323.71
- 5:332.22 and 1.33 L let me simply
- 5:38incorporate this information in this
- 5:40format so equals to select this cell
- 5:45M11 again equals to 28.820934
- 6:16so in this way I have noted down my
- 6:18depreciation as per diminishing balance
- 6:20method by incorporating the depreciation
- 6:23Excel function that is DB now the next
- 6:25step is the calculation of profit before
- 6:28tax so I need need to write equals to we
- 6:31all know that for the calculation of
- 6:33profit before tax it will be
- 6:37sales minus variable cost minus fixed
- 6:43operating
- 6:44overheads minus
- 6:47depreciation this will give you 6.88 L I
- 6:52need to Simply drag it till year 8 as
- 6:54well so the amount will be 6.88 lakh for
- 6:58year 1 20 2 6.18 lakh for year 2
- 7:0337.73 lakhs for year 3 44. 66 lakh for
- 7:07year 4 48.8 one lakh for year 5 51.2 1
- 7:13lakh for year 6 52.7 eight lakh for year
- 7:177 and
- 7:1953.67 lakh for year 8 the next component
- 7:22is the B deck loss the question says
- 7:25that the B de loss is 5 lakh rupees
- 7:30so the net profit before tax will be
- 7:33profit before tax minus B Deb
- 7:38loss so I can notice that the profit
- 7:42before tax or the net profit before tax
- 7:44is changing only in the last year that
- 7:46is 48.67 LH now I need to calculate my
- 7:50tax rate which is at the rate of
- 7:5340% equals to
- 7:550.4 multiplied by net profit Forex
- 8:00represented by C11 it comes out to be
- 8:052.75 simply drag it from year 1 till
- 8:08year 8 the next component is calculation
- 8:11of profit after tax that is profit
- 8:13before tax represented by C11 minus text
- 8:19represented by C12 press enter it comes
- 8:23out to be
- 8:244.13 now I need to add back the
- 8:26depreciation so again equals to see
- 8:30it let's drag
- 8:35it now the next component is the
- 8:37addition of B de which is equals to J10
- 8:41in this case salvage value is 2 L now I
- 8:45need to add the working capital
- 8:47recovered which is 25 lakh then I need
- 8:50to calculate the total which is equals
- 8:51to profit after tax plus
- 8:55depreciation plus b Deb plus salvage
- 8:59value
- 9:00plus working capital
- 9:02recovered let's simply drag it till year
- 9:058 so it comes out to be 52.2 lakh for
- 9:08year 1 44.5 3 lakh for year 2 39.917906
- 9:2962.5 3 lakh for year eight the next is
- 9:33the incorporation of discounting factor
- 9:37so my discount rate or the cost of
- 9:39capital my cost of capital is 12% in
- 9:42this case so I need to type equals to 1
- 9:46divided by 1
- 9:50+ cost of capital represented by M20
- 9:55bracket
- 9:56close shift + 6 that is RA to
- 10:02power one represented by
- 10:06C2 and press enter it comes out to be
- 10:100.893 now my cost of capital remains
- 10:13same in all the subsequent year so let
- 10:15me put a dollar sign before and after
- 10:19M and now let's simply drag it till A8
- 10:24so in this way I have noted down my PVF
- 10:27of 12% against 1 one year against second
- 10:30year third year against 8 year which
- 10:34comes out to be
- 10:360.404 now let's calculate pbci which is
- 10:39equals to non discounted cash flows
- 10:43multiplied by the discount rate it comes
- 10:47out to be 46.6 5 for year 1
- 10:5135.50 for year 2 28.4 for year three and
- 10:56so and so forth
- 10:59the total pvci will be equals
- 11:08to C20 +
- 11:11D20 plus
- 11:15E20 plus F20 +
- 11:18G20 +
- 11:21H20 + I2 and plus j20 so in this way I
- 11:24have calculated the total present value
- 11:27of cash in flow let let me total it
- 11:30equals to
- 11:34sum and bracket close so it comes out to
- 11:38be
- 11:3921234
- 11:42L the total pvco will be equals to some
- 11:46bracket open and select this it comes
- 11:50out to be 150 l so my npv comes out to
- 11:56be equals to present present value of
- 11:59cash inflows minus present value of cash
- 12:06outflow the value comes out to be 62.3 4
- 12:11lakhs now I need to
- 12:13identify that whether I will accept this
- 12:16project or reject it now I need to apply
- 12:19an IF function equals to
- 12:22if double click on it we all know that
- 12:26if npv is greater than zero then I will
- 12:29accept the project otherwise reject it
- 12:32now in this case I will write equals
- 12:35equals to if bracket open c23
- 12:39represented by npv value greater than
- 12:42zero if this logical test holds true
- 12:46then I will accept this project
- 12:48otherwise I will reject it bracket close
- 12:53and press enter so it comes out to be
- 12:56accept so I will accept this project
- 12:59because my npv is greater than
- 13:02zero that's all from my side we'll meet
- 13:04you in my next session has have a nice
- 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.