(Inventory= Purchase (65% of the second following month) in the excel) Purchase of november = 65000 and december= 78000

(COGS= Purchase (65% of the second following month) in the excel) Purchase of november = 65000 and december= 78000

Show the calculation.

1) Sales cash collection Budget Particulars Jan 1,00,000 Feb 1,20,000 Year: 2014 and Amount in $ Mar April May 1,50,000 3,00,000 2.75,000 June 2,00,000 July 2,00,000 Aug 1,80,000 Sales Cash Sales collection: 20% in same month Cash collection: 50% in next month Cash collection: 30% in second next month Total cash collection in a month 50% in Jan (175000"50%)- $ 87,500 **30% in Jan (220000*30%) = $ 66,000 ***30% in feb (175000*30%) = $ 52,500 20,000 87,500 66,000 1,73,500 24,000 50,000 52,500 1,26,500 30,000 60,000 30,000 1,20,000 60,000 75,000 36,000 1,71,000 55,000 1,50,000 45,000 2,50,000 40,000 1,37,500 90,000 2,67,500 40,000 1,00,000 82,500 2,22,500 36,000 1,00,000 60,000 1,96,000 2) Purchase and payment Budget Particulars Jan 97,500 78,000 Year: 2014 and Amount in $ Feb Mar April 1,95,000 1,78,750 1,30,000 97,500 1,95,000 1,78,750 May 1,30,000 1,30,000 June 1.17,000 1,30,000 Purchase (65 % of second following month) Cash payment for Purchase in next mont) *** pur. For jan (150000*65%)= $97,500 ans same calculation for other month. payment for jan for dec. purchase(120000*65%) = $ Particulars Feb May June Opening Cash Balance (A) Jan 28,000 Year: 2014 and Amount in $ Mar April 93,500 92,500 92,500 1,26,500 1,20,000 1,71,000 Cash Receipt-Operating : (B) 1,73,500 2,50,000 2,67,500 Cash Payment Operating Material Purchased Rent Other Expenditure Tax Payment Total Payment 78,000 10,000 20,000 97,500 10,000 20,000 1.95,000 10,000 20,000 22.500 -2,47,500 1,78.750 10,000 20,000 1,30,000 10,000 20,000 1.30,000 10,000 20,000 22.500 -1,82,500 -1,00,000 - 1,27,500 -2,08,750 -1,60,000 60,000 Add:(less) Short term Loan (Take to maintain cash bal. $ 25,000) Interest 1%p.m. Total Financial interest and Principal inflow (Outflow) (D) 63,350 -600 62,750 -63767 -1,234 -65,001 -59404 -596 -60,000 60,000 Closing Cash Balance (A+B+CD) 93,500 92,500 25,000 25,000 25,000 25,000 Gross Margin and Cost of Goods Sold. Assume that their relationship to sales doesn't change over the contemplated period. a) Determine Inventory turnover and AR turnover b) How long is the operating cycle? C) Assume the operating cycle increases by 20% (x1,2) due to unforeseen reason, how will that impact the cash budget? 1) Sales cash collection Budget Particulars Jan 1,00,000 Feb 1,20,000 Year: 2014 and Amount in $ Mar April May 1,50,000 3,00,000 2.75,000 June 2,00,000 July 2,00,000 Aug 1,80,000 Sales Cash Sales collection: 20% in same month Cash collection: 50% in next month Cash collection: 30% in second next month Total cash collection in a month 50% in Jan (175000"50%)- $ 87,500 **30% in Jan (220000*30%) = $ 66,000 ***30% in feb (175000*30%) = $ 52,500 20,000 87,500 66,000 1,73,500 24,000 50,000 52,500 1,26,500 30,000 60,000 30,000 1,20,000 60,000 75,000 36,000 1,71,000 55,000 1,50,000 45,000 2,50,000 40,000 1,37,500 90,000 2,67,500 40,000 1,00,000 82,500 2,22,500 36,000 1,00,000 60,000 1,96,000 2) Purchase and payment Budget Particulars Jan 97,500 78,000 Year: 2014 and Amount in $ Feb Mar April 1,95,000 1,78,750 1,30,000 97,500 1,95,000 1,78,750 May 1,30,000 1,30,000 June 1.17,000 1,30,000 Purchase (65 % of second following month) Cash payment for Purchase in next mont) *** pur. For jan (150000*65%)= $97,500 ans same calculation for other month. payment for jan for dec. purchase(120000*65%) = $ Particulars Feb May June Opening Cash Balance (A) Jan 28,000 Year: 2014 and Amount in $ Mar April 93,500 92,500 92,500 1,26,500 1,20,000 1,71,000 Cash Receipt-Operating : (B) 1,73,500 2,50,000 2,67,500 Cash Payment Operating Material Purchased Rent Other Expenditure Tax Payment Total Payment 78,000 10,000 20,000 97,500 10,000 20,000 1.95,000 10,000 20,000 22.500 -2,47,500 1,78.750 10,000 20,000 1,30,000 10,000 20,000 1.30,000 10,000 20,000 22.500 -1,82,500 -1,00,000 - 1,27,500 -2,08,750 -1,60,000 60,000 Add:(less) Short term Loan (Take to maintain cash bal. $ 25,000) Interest 1%p.m. Total Financial interest and Principal inflow (Outflow) (D) 63,350 -600 62,750 -63767 -1,234 -65,001 -59404 -596 -60,000 60,000 Closing Cash Balance (A+B+CD) 93,500 92,500 25,000 25,000 25,000 25,000 Gross Margin and Cost of Goods Sold. Assume that their relationship to sales doesn't change over the contemplated period. a) Determine Inventory turnover and AR turnover b) How long is the operating cycle? C) Assume the operating cycle increases by 20% (x1,2) due to unforeseen reason, how will that impact the cash budget