YouTube2Text

Working Capital in MS Excel | Dr. Rishabh Gupta #BCH #BCP #3rdsemester #FinancialManagement — Transcript

by GYAN PRASAR KENDRA · 1,876 words · 335 segments · language en · Watch on YouTube

Full transcript

  1. 0:00a very good afternoon to one and all
  2. 0:02myself Dr RAB Gupta and in today's
  3. 0:05session we will be understanding the
  4. 0:08computation of working capital in msxl
  5. 0:12so let's begin from the following data
  6. 0:15compute the duration of operating cycle
  7. 0:19raw material it's 20,000 rupees in year
  8. 0:22one and 27,000 rupees in year two work
  9. 0:26in progress 14,000 rupees and 18,000
  10. 0:29rupees
  11. 0:30finished goods 21,000 rupees in year 1
  12. 0:34and 24,000 rupees in year 2 purchases is
  13. 0:38being given cost of good sales sales
  14. 0:42debors creditors you need to assume 360
  15. 0:46days in a year for the computational
  16. 0:48purpose based upon this information I
  17. 0:51need to compute the operating cycle so
  18. 0:54let's begin now in this case raw
  19. 0:57material is 20,000
  20. 1:00for year 1 and 27,000 for year 2 work in
  21. 1:05progress is 14,000 for year 1 and 18,000
  22. 1:10for year 2 similarly finished goods is
  23. 1:1421,000 and 24,000
  24. 1:18respectively for purchases it is 96,000
  25. 1:21in year one and
  26. 1:24135,000 in year two similarly cost of
  27. 1:28good sold is 1 lak 40,000 in year 1 and
  28. 1:331 lak 180,000 in year
  29. 1:362 sales 1 lakh
  30. 1:3960,000 and 2 lakh in year to for the
  31. 1:44gors it is
  32. 1:4532,000 and
  33. 1:4850,000 for the creditors 16,000 in year
  34. 1:521 and 18,000 in year two and we have
  35. 1:56assumed 360 days in a year now let's
  36. 2:00begin first of all I need to compute the
  37. 2:02raw material for the computation of raw
  38. 2:05material period the formula is average
  39. 2:08raw material divided by total purchase
  40. 2:11into 360 now in this case the inventory
  41. 2:15period or the raw material period will
  42. 2:17be equals to raw material represented by
  43. 2:21C3 that is 20,000 rupees divided by
  44. 2:26purchases represented by C6
  45. 2:30multiplied
  46. 2:31by 360 that is multiplied by C11 and
  47. 2:36press enter it comes out to be 75 days
  48. 2:41similarly for year two it will be raw
  49. 2:45material divided by
  50. 2:49purchases into
  51. 2:52360 this will give you 72 days the next
  52. 2:55is the calculation of creditor's period
  53. 2:58the formula is average creditors divided
  54. 3:01by total purchase multiplied by 360 now
  55. 3:05in this case the creditors are 16,000
  56. 3:08for year 1 divided by total purchase
  57. 3:13that is
  58. 3:1496,000 multiplied by 360 it comes out to
  59. 3:18be 60 days same way I can calculate for
  60. 3:21the year two as well equals
  61. 3:25to
  62. 3:2718,000 divided by
  63. 3:31135,000 multiplied by 360 that is 48
  64. 3:36days the next is the calculation of work
  65. 3:38in progress period the calculation of
  66. 3:41work in progress the formula is average
  67. 3:43work in progress divided by total cost
  68. 3:46of goods sold into 360 so equals to the
  69. 3:50work in progress for year 1 is
  70. 3:5314,000 divided by 1
  71. 3:56140,000 into 360 it comes to be 36 J
  72. 4:02similarly for year two it will be
  73. 4:0518,000 divided by 1H
  74. 4:09180,000 multiplied by
  75. 4:12360 that is 36 days for year two as well
  76. 4:16for the finished goods period the
  77. 4:18formula is average finished goods
  78. 4:20divided by total cost of goods sold into
  79. 4:23360 so in this case the finished goods
  80. 4:27is 21,00
  81. 4:30,000 divided by cost of goods sold 1
  82. 4:35140,000 multiplied by 360 that is 54
  83. 4:40days same way I can calculate it for the
  84. 4:42year two as well
  85. 4:4524,000 divided by 1H
  86. 4:49180,000 multiped by 360 that is 48 days
  87. 4:54and finally I need to calculate the DAT
  88. 4:57period the formula is average dat
  89. 5:00divided by total sales into
  90. 5:03360 so the dors in this case is
  91. 5:0732,000 divided by total sales which is
  92. 5:111H
  93. 5:12160,000 multiplied by 360 represented by
  94. 5:16C11 that is 72 days similarly for the
  95. 5:21year two it will be 50,000 divided by
  96. 5:25total sales that is 2 lakh multiplied by
  97. 5:29360
  98. 5:30that is 90 days now you need to
  99. 5:32calculate the net operating cycle the
  100. 5:34formula is inventory period Plus account
  101. 5:38receivable period minus account payable
  102. 5:41period so let's begin the inventory
  103. 5:43period includes raw material period Plus
  104. 5:47work in progress period plus finished
  105. 5:51goods period plus the account receivable
  106. 5:55period that is 72 days represented by
  107. 5:58c18
  108. 6:00minus the creditors period it comes out
  109. 6:04to be 177 days same way I can drag it
  110. 6:08for the year2 as well and it comes out
  111. 6:10to be 198 days so we can see that there
  112. 6:13is an increase in the net operating
  113. 6:15cycle from 177 days to 198 days so this
  114. 6:21increase in the net operating cycle is
  115. 6:23arising because
  116. 6:25of thator taking
  117. 6:29longer
  118. 6:31time to
  119. 6:40pay that is 18
  120. 6:42days similarly
  121. 6:47creditors receiving
  122. 6:52payment earlier it is equals
  123. 6:57to 60 days minus 48 days that is 12 days
  124. 7:03the next another reason is lower
  125. 7:06finished goods
  126. 7:09turnover lower finished
  127. 7:13goods
  128. 7:14turnover it
  129. 7:16is equals to 54 days minus 48 days that
  130. 7:23is 6j the next reason is lower raw
  131. 7:26material turnover
  132. 7:30which is equals
  133. 7:32to 75 -
  134. 7:3872 now both this information the
  135. 7:41increase in the operating cycle will be
  136. 7:44the increase in
  137. 7:47operating cycle will be equals to 18
  138. 7:51days plus 12 days minus 6 days minus 3
  139. 7:58days that is 21 days so there is an
  140. 8:01increase in the net operating cycle by
  141. 8:0421 days because of these reasons lising
  142. 8:08here now let's focus on question
  143. 8:12two XYZ limited Supply the following
  144. 8:16information sales and production for the
  145. 8:18year is
  146. 8:2069,000 finished goods in store 3 months
  147. 8:24raw material in store 2 months
  148. 8:27consumption production process proc 1
  149. 8:30month credit Allowed by creditors 2
  150. 8:34months selling price per unit rupees 50
  151. 8:38raw material 50% of selling price direct
  152. 8:43wages 10% of selling price and overheads
  153. 8:4820% of selling
  154. 8:52price 20% of sales are on cash basis and
  155. 8:57credit sales allow to customer for 1
  156. 9:00month overheads include rupees 5 as
  157. 9:05depreciation there is regular production
  158. 9:08and sales cycle and wages and overs acre
  159. 9:12evenly wages are paid in the next month
  160. 9:16of acral and overheads are paid 15 days
  161. 9:20in
  162. 9:20areas materials are introduced in the
  163. 9:23beginning of production cycle work in
  164. 9:26progress involves use of fully units of
  165. 9:29raw material in the beginning of
  166. 9:31manufacturing process and other
  167. 9:34conversion costs equivalent to
  168. 9:3750% you are required to calculate the
  169. 9:40working capital requirement on cash cost
  170. 9:43basis so let's begin now in this case
  171. 9:46the sales and production for the month
  172. 9:49is
  173. 9:5069,000 divided by 12 that is 5750
  174. 9:55units the monthly production is 5,7
  175. 10:00150 finished goods in store it's 3 month
  176. 10:04finished goods in store is 3 months raw
  177. 10:07material in store 2 months production
  178. 10:11process takes 1 month time credit
  179. 10:14Allowed by customer is two selling price
  180. 10:18is rupees
  181. 10:1950 raw material is rupe
  182. 10:2225 direct wages rupees 5 overheads is 10
  183. 10:28Rupees and and the overhead excluding
  184. 10:30depreciation is 5 rupees we know that
  185. 10:34based upon this selling price of rupees
  186. 10:3650 I can easily calculate my raw
  187. 10:39material which is 50% of the selling
  188. 10:42price similarly direct wages is 10% and
  189. 10:46over is
  190. 10:4920% let me mention
  191. 10:53it 50% of selling price
  192. 11:02similarly for the direct wages it is
  193. 11:0610% of selling
  194. 11:10price and for the overheads it is 20%
  195. 11:17of selling price now in this numerical
  196. 11:21my cash cost will
  197. 11:24be equals
  198. 11:26to raw material Plus direct wage plus 5
  199. 11:32rupes overx excluding depreciation that
  200. 11:36is 35 rupes now let's calculate the
  201. 11:39working capital we know that the formula
  202. 11:41for the working capital is current
  203. 11:42assets minus current
  204. 11:44liability first and foremost I need to
  205. 11:47calculate my raw
  206. 11:49materials it's been given for the raw
  207. 11:53material it will be
  208. 11:555,750
  209. 11:58units multiplied by 2
  210. 12:03months and multiplied by rupees 25 per
  211. 12:09unit so again it will be equals to
  212. 12:145,750
  213. 12:16units multiplied by 2
  214. 12:19months again multiplied by rupees
  215. 12:2325 it comes out to be 2 lakh
  216. 12:2687,500 now let's begin the computation
  217. 12:29of working capital we all know that the
  218. 12:32formula for the working capital is
  219. 12:34current assets minus current liability
  220. 12:37in this numerical first component of
  221. 12:39current assets is raw material it's been
  222. 12:42given that for computing my raw material
  223. 12:45it is
  224. 12:465,750 units multiplied by 2
  225. 12:51months multiplied by rupees 25 per
  226. 12:57unit let me put it in to the Excel
  227. 13:00function equals to
  228. 13:035750 units multiplied by 2 multiplied by
  229. 13:09rupes 25 that is 2H 87,500 now I need to
  230. 13:14compute my work in progress which
  231. 13:16includes raw material wages and
  232. 13:19overheads so again for the work in
  233. 13:22progress of raw material it's
  234. 13:255,750 units multiplied by rupees 20
  235. 13:29per
  236. 13:32unit let me put it into the Excel
  237. 13:34function equals to 5750 represented by
  238. 13:38C30 multiplied by production process of
  239. 13:421 month multiplied by rup
  240. 13:4725 that is 1
  241. 13:5114375 for the wage it is
  242. 13:55again
  243. 13:575750 units
  244. 14:00multiplied
  245. 14:01by 1
  246. 14:03month multiplied
  247. 14:06by rupe
  248. 14:085 per
  249. 14:13unit and multiply by 50%
  250. 14:19complete for the bages it will be equals
  251. 14:22to 5,750 units multiplied by 1 month
  252. 14:28multipied by
  253. 14:30direct wages of rupees 5 multiplied by
  254. 14:340.50 similarly for the overhead also the
  255. 14:37formula Remains the Same 5750 units
  256. 14:40multiplied by 1 month multiplied by cash
  257. 14:44over of rupe 5 per unit and multiplied
  258. 14:47by 50% so equals to
  259. 14:515750 multiped by 1 month multiplied
  260. 14:55by 5 per unit and multiplied by 50 that
  261. 15:00is
  262. 15:0214,375 for the finished quits it will be
  263. 15:065750
  264. 15:08units multiplied
  265. 15:11by
  266. 15:13rupees
  267. 15:1635 per
  268. 15:18unit so for the finished goods it will
  269. 15:20be 5750 units multiplied by 3
  270. 15:26months multiplied by rupe is 35 per
  271. 15:32unit so equals to
  272. 15:365750 multiplied
  273. 15:39by 3 months multiplied by the cash cost
  274. 15:43of rupes
  275. 15:4435 and press enter it comes out to be 6
  276. 15:48lak
  277. 15:493,750 for the dors it will be
  278. 15:535,750 units multiplied by 1 month
  279. 15:59again multiplied
  280. 16:01by rupees 35 per unit and multiplied by
  281. 16:08credit sales which is 80% of total sales
  282. 16:1280% of
  283. 16:14sales so for the dors it will be equals
  284. 16:17to 5750 units multiplied by the credit
  285. 16:22sales allowed to customer of 1 month
  286. 16:26multiplied by cash cost of rupes 35 per
  287. 16:29unit and multiplied by
  288. 16:330.80 it comes out to be 1 lakh
  289. 16:3761,000 the total current assets will be
  290. 16:39equals to
  291. 16:41sum select a range of
  292. 16:44value bracket close and press enter it
  293. 16:48comes out to be 12 lak
  294. 16:5024,750 rupees now let's focus on the
  295. 16:54calculation of current liabilities for
  296. 16:56the creditors it will be
  297. 17:005,750
  298. 17:02units multiplied by 2
  299. 17:06months multiplied by rupees 25 per
  300. 17:12unit so equals
  301. 17:16to
  302. 17:185,750 multiplied by 2
  303. 17:22months multiplied by 25
  304. 17:26rupees and press enter it comes out to
  305. 17:30be 2 lakh
  306. 17:3187,500
  307. 17:36rupees for the outstanding wages it will
  308. 17:38be 5750
  309. 17:41units multiplied by 1
  310. 17:45month multiplied by rupees 5 per
  311. 17:50unit so equals
  312. 17:53to
  313. 17:565750 multiplied by 1 1
  314. 17:59multiplied by rupees 5 per
  315. 18:03unit that is
  316. 18:0728,750 for the outstanding overx it will
  317. 18:10be 5750 units multiplied by 15 days
  318. 18:16multiplied by rupees 5 per unit so it
  319. 18:21will be equals
  320. 18:24to 5750 units
  321. 18:29multiplied by
  322. 18:320.50 multiplied
  323. 18:34by rupees 5 per unit that is
  324. 18:4014,375 the total current liabilities
  325. 18:42will be equals to
  326. 18:44sum select this range of value and
  327. 18:47bracket close that is 3 lakh 30,
  328. 18:51625 the net working capital will be
  329. 18:54equals to current assets represented by
  330. 18:57c51
  331. 18:59minus current liabilities represented by
  332. 19:03c57 and press enter the amount comes out
  333. 19:06to be 8 lakh
  334. 19:0994,5 rupees so that's all from my side
  335. 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.