Assignment: Prepare a monthly cash budget for the six-month period, January thro

Question

Assignment: Prepare a monthly cash budget for the six-month period, January through June, 2016. Prepare a pro-forma income statement and balance sheet for the same period, January through June. Complete the assignment using the Excel template. Print out two pages (one including the cash budget and another with the pro forma statement) and turn in at the start of class on Tuesday, June 28th. You do not need to print the formulas. Information: a) Sales are 60% for cash, 40% on open account (credit sales). b) Of the credit sales, 10% are collected during the month of sale, 70% in the month following the month of sale, and 20% in the second month following the sale. c) Cost of goods sold is strictly materials and averages 65% of sales. d) The store follows a policy of purchasing enough inventory each month to cover the following month's sales at cost. e) All inventory purchases are paid for two months after they are made. f) A minimum cash balance of $8,000 is targeted for the store. g)"Other current liabilities" remain unchanged. h) Wages are paid one month after incurred. i) The corporate tax rate is 35%. As of December 31, 2015 all back taxes had been paid. No additional tax payment is due until July 15th. j) A $9,000 dividend payment will be made in June.

Explanation / Answer

Sales Budget Jan Feb Mar Ap May Jun Total Sales 10000 12000 18000 30000 33000 22000 125000 Cash 60% 6000 7200 10800 18000 19800 13200 75000 Credit 40% 4000 4800 7200 12000 13200 8800 50000 Total 10000 12000 18000 30000 33000 22000 125000 Collections from Credit Sales Total July Aug Nov Acc Receivabale 800 800 Dec Acc Receivable70:20 4200 1200 5400 Jan :10:70:20 400 2800 800 4000 Feb :10:70:20 480 3360 960 4800 Mar :10:70:20 720 5040 1440 7200 Apr :10:70:20 1200 8400 2400 12000 May :10:70:20 1320 9240 10560 2640 June :10:70:20 880 880 6160 1760 Total 5400 4480 4880 7200 11160 12520 45640 Purchases Budget Jan Feb Mar Ap May Jun Total July August Sales 10000 12000 18000 30000 33000 22000 125000 9000 COGS 65% 6500 7800 11700 19500 21450 14300 81250 Closing stock 5070 7605 12675 13942.5 9295 5850 5850 Stock reqd 11570 15405 24375 33442.5 30745 20150 87100 Op stock aval. 7000 5070 7605 12675 13942.5 9295 7000 Purchases 4570 10335 16770 20767.5 16802.5 10855 80100 Payment for purchases Total July August Nov Accounts Payable 6500 6500 Dec Accounts Payable 9750 9750 Jan 4570 4570 Feb 10335 10335 Mar 16770 16770 Apr 20767.5 20767.5 May 0 16802.5 Jun 0 10855 Total 6500 9750 4570 10335 16770 20767.5 68692.5 16802.5 10855 Cash Budget Jan Feb Mar Ap May Jun Total July August Opening Balance 8000 9850 8000 15860 25175 33240 8000 Cash Sales 6000 7200 10800 18000 19800 13200 75000 Collections 5400 4480 4880 7200 11160 12520 45640 Total cash available 19400 21530 23680 41060 56135 58960 128640 Payments: For Purchases 6500 9750 4570 10335 16770 20767.5 68692.5 Rent 800 800 800 800 800 800 4800 Wages 2000 3000 2000 4000 4500 3500 19000 2500 Other expenses 250 300 450 750 825 550 3125 Dividend 9000 9000 Total payments 9550 13850 7820 15885 22895 34617.5 104617.5 2500 Surplus/(Deficit) 9850 7680 15860 25175 33240 24342.5 24022.5 Add: Borrowings 320 320 Ending Cash balance 9850 8000 15860 25175 33240 24342.5 24342.5 Income statement Sales 125000 Less: COGS 81250 Gross Profit 43750 less: Op Expenes Rent 4800 Wages 19500 Other expenses 3125 Depreciation 150*6 900 28325 Income before Tax 15425 Less: Tax @ 35% 5398.75 Income after tax 10026.25 Less;Dividend 9000 Income trf. to Ret. Earn. 1026.25 Balance Sheet Assets Current Asset Cash 24342.5 Accounts Receivable 10560 Inventory 5850 Total Current Assets 40752.5 Fixed Assets Furniture & Fixtures 19000 Less:Acc depn(3000+900) -3900 15100 Total Assets 55852.5 liabilities & Equity Current Liability Accounts Payable 27657.5 Borrowings(ShortTerm) 320 Accured wages & Salaries 2500 Income tax Payable 5398.75 Other current Liabilities 7200 Total current liabilities 43076.25 Equity Capital Stock 3000 Retained Earnings(8750+1026.25) 9776.25 Total Equity 12776.25 Total liabilities & Equity 55852.5

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.