Working Capital in MS Excel | Dr. Rishabh Gupta #BCH #BCP #3rdsemester #FinancialManagement — Transcript
Full transcript
- 0:00a very good afternoon to one and all
- 0:02myself Dr RAB Gupta and in today's
- 0:05session we will be understanding the
- 0:08computation of working capital in msxl
- 0:12so let's begin from the following data
- 0:15compute the duration of operating cycle
- 0:19raw material it's 20,000 rupees in year
- 0:22one and 27,000 rupees in year two work
- 0:26in progress 14,000 rupees and 18,000
- 0:29rupees
- 0:30finished goods 21,000 rupees in year 1
- 0:34and 24,000 rupees in year 2 purchases is
- 0:38being given cost of good sales sales
- 0:42debors creditors you need to assume 360
- 0:46days in a year for the computational
- 0:48purpose based upon this information I
- 0:51need to compute the operating cycle so
- 0:54let's begin now in this case raw
- 0:57material is 20,000
- 1:00for year 1 and 27,000 for year 2 work in
- 1:05progress is 14,000 for year 1 and 18,000
- 1:10for year 2 similarly finished goods is
- 1:1421,000 and 24,000
- 1:18respectively for purchases it is 96,000
- 1:21in year one and
- 1:24135,000 in year two similarly cost of
- 1:28good sold is 1 lak 40,000 in year 1 and
- 1:331 lak 180,000 in year
- 1:362 sales 1 lakh
- 1:3960,000 and 2 lakh in year to for the
- 1:44gors it is
- 1:4532,000 and
- 1:4850,000 for the creditors 16,000 in year
- 1:521 and 18,000 in year two and we have
- 1:56assumed 360 days in a year now let's
- 2:00begin first of all I need to compute the
- 2:02raw material for the computation of raw
- 2:05material period the formula is average
- 2:08raw material divided by total purchase
- 2:11into 360 now in this case the inventory
- 2:15period or the raw material period will
- 2:17be equals to raw material represented by
- 2:21C3 that is 20,000 rupees divided by
- 2:26purchases represented by C6
- 2:30multiplied
- 2:31by 360 that is multiplied by C11 and
- 2:36press enter it comes out to be 75 days
- 2:41similarly for year two it will be raw
- 2:45material divided by
- 2:49purchases into
- 2:52360 this will give you 72 days the next
- 2:55is the calculation of creditor's period
- 2:58the formula is average creditors divided
- 3:01by total purchase multiplied by 360 now
- 3:05in this case the creditors are 16,000
- 3:08for year 1 divided by total purchase
- 3:13that is
- 3:1496,000 multiplied by 360 it comes out to
- 3:18be 60 days same way I can calculate for
- 3:21the year two as well equals
- 3:25to
- 3:2718,000 divided by
- 3:31135,000 multiplied by 360 that is 48
- 3:36days the next is the calculation of work
- 3:38in progress period the calculation of
- 3:41work in progress the formula is average
- 3:43work in progress divided by total cost
- 3:46of goods sold into 360 so equals to the
- 3:50work in progress for year 1 is
- 3:5314,000 divided by 1
- 3:56140,000 into 360 it comes to be 36 J
- 4:02similarly for year two it will be
- 4:0518,000 divided by 1H
- 4:09180,000 multiplied by
- 4:12360 that is 36 days for year two as well
- 4:16for the finished goods period the
- 4:18formula is average finished goods
- 4:20divided by total cost of goods sold into
- 4:23360 so in this case the finished goods
- 4:27is 21,00
- 4:30,000 divided by cost of goods sold 1
- 4:35140,000 multiplied by 360 that is 54
- 4:40days same way I can calculate it for the
- 4:42year two as well
- 4:4524,000 divided by 1H
- 4:49180,000 multiped by 360 that is 48 days
- 4:54and finally I need to calculate the DAT
- 4:57period the formula is average dat
- 5:00divided by total sales into
- 5:03360 so the dors in this case is
- 5:0732,000 divided by total sales which is
- 5:111H
- 5:12160,000 multiplied by 360 represented by
- 5:16C11 that is 72 days similarly for the
- 5:21year two it will be 50,000 divided by
- 5:25total sales that is 2 lakh multiplied by
- 5:29360
- 5:30that is 90 days now you need to
- 5:32calculate the net operating cycle the
- 5:34formula is inventory period Plus account
- 5:38receivable period minus account payable
- 5:41period so let's begin the inventory
- 5:43period includes raw material period Plus
- 5:47work in progress period plus finished
- 5:51goods period plus the account receivable
- 5:55period that is 72 days represented by
- 5:58c18
- 6:00minus the creditors period it comes out
- 6:04to be 177 days same way I can drag it
- 6:08for the year2 as well and it comes out
- 6:10to be 198 days so we can see that there
- 6:13is an increase in the net operating
- 6:15cycle from 177 days to 198 days so this
- 6:21increase in the net operating cycle is
- 6:23arising because
- 6:25of thator taking
- 6:29longer
- 6:31time to
- 6:40pay that is 18
- 6:42days similarly
- 6:47creditors receiving
- 6:52payment earlier it is equals
- 6:57to 60 days minus 48 days that is 12 days
- 7:03the next another reason is lower
- 7:06finished goods
- 7:09turnover lower finished
- 7:13goods
- 7:14turnover it
- 7:16is equals to 54 days minus 48 days that
- 7:23is 6j the next reason is lower raw
- 7:26material turnover
- 7:30which is equals
- 7:32to 75 -
- 7:3872 now both this information the
- 7:41increase in the operating cycle will be
- 7:44the increase in
- 7:47operating cycle will be equals to 18
- 7:51days plus 12 days minus 6 days minus 3
- 7:58days that is 21 days so there is an
- 8:01increase in the net operating cycle by
- 8:0421 days because of these reasons lising
- 8:08here now let's focus on question
- 8:12two XYZ limited Supply the following
- 8:16information sales and production for the
- 8:18year is
- 8:2069,000 finished goods in store 3 months
- 8:24raw material in store 2 months
- 8:27consumption production process proc 1
- 8:30month credit Allowed by creditors 2
- 8:34months selling price per unit rupees 50
- 8:38raw material 50% of selling price direct
- 8:43wages 10% of selling price and overheads
- 8:4820% of selling
- 8:52price 20% of sales are on cash basis and
- 8:57credit sales allow to customer for 1
- 9:00month overheads include rupees 5 as
- 9:05depreciation there is regular production
- 9:08and sales cycle and wages and overs acre
- 9:12evenly wages are paid in the next month
- 9:16of acral and overheads are paid 15 days
- 9:20in
- 9:20areas materials are introduced in the
- 9:23beginning of production cycle work in
- 9:26progress involves use of fully units of
- 9:29raw material in the beginning of
- 9:31manufacturing process and other
- 9:34conversion costs equivalent to
- 9:3750% you are required to calculate the
- 9:40working capital requirement on cash cost
- 9:43basis so let's begin now in this case
- 9:46the sales and production for the month
- 9:49is
- 9:5069,000 divided by 12 that is 5750
- 9:55units the monthly production is 5,7
- 10:00150 finished goods in store it's 3 month
- 10:04finished goods in store is 3 months raw
- 10:07material in store 2 months production
- 10:11process takes 1 month time credit
- 10:14Allowed by customer is two selling price
- 10:18is rupees
- 10:1950 raw material is rupe
- 10:2225 direct wages rupees 5 overheads is 10
- 10:28Rupees and and the overhead excluding
- 10:30depreciation is 5 rupees we know that
- 10:34based upon this selling price of rupees
- 10:3650 I can easily calculate my raw
- 10:39material which is 50% of the selling
- 10:42price similarly direct wages is 10% and
- 10:46over is
- 10:4920% let me mention
- 10:53it 50% of selling price
- 11:02similarly for the direct wages it is
- 11:0610% of selling
- 11:10price and for the overheads it is 20%
- 11:17of selling price now in this numerical
- 11:21my cash cost will
- 11:24be equals
- 11:26to raw material Plus direct wage plus 5
- 11:32rupes overx excluding depreciation that
- 11:36is 35 rupes now let's calculate the
- 11:39working capital we know that the formula
- 11:41for the working capital is current
- 11:42assets minus current
- 11:44liability first and foremost I need to
- 11:47calculate my raw
- 11:49materials it's been given for the raw
- 11:53material it will be
- 11:555,750
- 11:58units multiplied by 2
- 12:03months and multiplied by rupees 25 per
- 12:09unit so again it will be equals to
- 12:145,750
- 12:16units multiplied by 2
- 12:19months again multiplied by rupees
- 12:2325 it comes out to be 2 lakh
- 12:2687,500 now let's begin the computation
- 12:29of working capital we all know that the
- 12:32formula for the working capital is
- 12:34current assets minus current liability
- 12:37in this numerical first component of
- 12:39current assets is raw material it's been
- 12:42given that for computing my raw material
- 12:45it is
- 12:465,750 units multiplied by 2
- 12:51months multiplied by rupees 25 per
- 12:57unit let me put it in to the Excel
- 13:00function equals to
- 13:035750 units multiplied by 2 multiplied by
- 13:09rupes 25 that is 2H 87,500 now I need to
- 13:14compute my work in progress which
- 13:16includes raw material wages and
- 13:19overheads so again for the work in
- 13:22progress of raw material it's
- 13:255,750 units multiplied by rupees 20
- 13:29per
- 13:32unit let me put it into the Excel
- 13:34function equals to 5750 represented by
- 13:38C30 multiplied by production process of
- 13:421 month multiplied by rup
- 13:4725 that is 1
- 13:5114375 for the wage it is
- 13:55again
- 13:575750 units
- 14:00multiplied
- 14:01by 1
- 14:03month multiplied
- 14:06by rupe
- 14:085 per
- 14:13unit and multiply by 50%
- 14:19complete for the bages it will be equals
- 14:22to 5,750 units multiplied by 1 month
- 14:28multipied by
- 14:30direct wages of rupees 5 multiplied by
- 14:340.50 similarly for the overhead also the
- 14:37formula Remains the Same 5750 units
- 14:40multiplied by 1 month multiplied by cash
- 14:44over of rupe 5 per unit and multiplied
- 14:47by 50% so equals to
- 14:515750 multiped by 1 month multiplied
- 14:55by 5 per unit and multiplied by 50 that
- 15:00is
- 15:0214,375 for the finished quits it will be
- 15:065750
- 15:08units multiplied
- 15:11by
- 15:13rupees
- 15:1635 per
- 15:18unit so for the finished goods it will
- 15:20be 5750 units multiplied by 3
- 15:26months multiplied by rupe is 35 per
- 15:32unit so equals to
- 15:365750 multiplied
- 15:39by 3 months multiplied by the cash cost
- 15:43of rupes
- 15:4435 and press enter it comes out to be 6
- 15:48lak
- 15:493,750 for the dors it will be
- 15:535,750 units multiplied by 1 month
- 15:59again multiplied
- 16:01by rupees 35 per unit and multiplied by
- 16:08credit sales which is 80% of total sales
- 16:1280% of
- 16:14sales so for the dors it will be equals
- 16:17to 5750 units multiplied by the credit
- 16:22sales allowed to customer of 1 month
- 16:26multiplied by cash cost of rupes 35 per
- 16:29unit and multiplied by
- 16:330.80 it comes out to be 1 lakh
- 16:3761,000 the total current assets will be
- 16:39equals to
- 16:41sum select a range of
- 16:44value bracket close and press enter it
- 16:48comes out to be 12 lak
- 16:5024,750 rupees now let's focus on the
- 16:54calculation of current liabilities for
- 16:56the creditors it will be
- 17:005,750
- 17:02units multiplied by 2
- 17:06months multiplied by rupees 25 per
- 17:12unit so equals
- 17:16to
- 17:185,750 multiplied by 2
- 17:22months multiplied by 25
- 17:26rupees and press enter it comes out to
- 17:30be 2 lakh
- 17:3187,500
- 17:36rupees for the outstanding wages it will
- 17:38be 5750
- 17:41units multiplied by 1
- 17:45month multiplied by rupees 5 per
- 17:50unit so equals
- 17:53to
- 17:565750 multiplied by 1 1
- 17:59multiplied by rupees 5 per
- 18:03unit that is
- 18:0728,750 for the outstanding overx it will
- 18:10be 5750 units multiplied by 15 days
- 18:16multiplied by rupees 5 per unit so it
- 18:21will be equals
- 18:24to 5750 units
- 18:29multiplied by
- 18:320.50 multiplied
- 18:34by rupees 5 per unit that is
- 18:4014,375 the total current liabilities
- 18:42will be equals to
- 18:44sum select this range of value and
- 18:47bracket close that is 3 lakh 30,
- 18:51625 the net working capital will be
- 18:54equals to current assets represented by
- 18:57c51
- 18:59minus current liabilities represented by
- 19:03c57 and press enter the amount comes out
- 19:06to be 8 lakh
- 19:0994,5 rupees so that's all from my side
- 19:13have a nice day thank you
About this transcript
This page contains the full transcript of Working Capital 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,876 words across 335 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.