Question

First Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2020. Balance Sheet March 31, 2020 Assets Cash Accounts receivable Inventory Prepaid Insurance Net Fixtures Total Assets 2,000 25,000 29,400 2,000 25,000 83,400 Liabilities 25,480 6,000 31,480 Accounts payable Dividends payable Total Liabilities Stockholders' Equity Common Stock Retained Earnings Total liabilities and Stockholders' equity $ 14,920 37,000 51,920 83,400 $ Sales Revenue Month January February March April May June 60,000 50,000 40,000 50,000 60,000 70,000 90,000 25,000 Wages and salaries Depreciation Utilities Rent Prepaid insurance is for following months 1,000 2,000 4 Months Cash dividends are declared during the third month of each quarter and are paid during the first month of the following quarter = $6,000.

Answer 1

Answer is given below with all working

Also i have given excel screen shot of formulas please link excel work sheet accordingly

For any doubts please comment.

Sales Budget April $50,000 May $60,000 June $70,000 Quarter $180,000 Budgeted Sales 2 Schedule of Cash collected from Customers April May June Quarter Collection February Sales March Sales April Sales May Sales June Sales $5,000 $16,000 $25,000 $4,000 $20,000 $30,000 $5,000 $24,000 $35,000 $64,000 $5,000 $20,000 $50,000 $54,000 $35,000 $164,000 Total Cash Collections $46,000 $54,000 Cost of Goods sold Sales COGS -49% April $50,000 $24,500 May $60,000 $29,400 June $70,000 $34,300 Quarter $180,000 $88,200 July $90,000 $44,100 1 Purchase Budget July $44,100 Budgeted COGS Add: Closing stock ( 120% of sale of following month) Total Needs Less Opening Stock Required Purchase (Value) April | $24,500 $35,280 $59,780 $29,400 $30,380 May $29,400 $41,160 $70,560 $35,280 $35,280 June $34,300 $52,920 $87,220 $41,160 $46,060 Quarter $88,200 $52,920 $141,120 $29,400 $111,720 Closing stock for June ($44,100*120%) =$52,920 3 Schedule of Cash payment for purchases May June April $25,480 March Purchase April Purchase May Purchase $30,380 Quarter $25,480 $30,380 $35,280 $91,140 Total Cash Payments $25,480 $35,280 $35,280 $30,380 First Store Cash Budget For the Three months Ended June 30, 2020 April May $2,000 $2,520 June $2,140 Quarter $2,000 Opening Balance of Cash Receipt of Cash Collection from Customers Cash available for disbursement $46,000 $48,000 $54,000 $56,520 $64,000 $66,140 $164,000 $166,000 $25,480 $25,000 $1,000 $2,000 $6,000 $59,480 -$11,480 $30,380 $25,000 $1,000 $2,000 Merchandise purchase Wages and Salaries Utilities Rent Dividend Cash paid for disbursements Net Cash available Financing Borrowings (at the beginning of the month) Repayments at the end of month) Interest (at 1% per month) Total financing Closing cash balance $35,280 $25,000 $1,000 $2,000 $91,140 $75,000 $3,000 $6,000 $6,000 $181,140 -$15,140 $58,380 -$1,860 $63,280 $2,860 $14,000 $4,000 $18,000 $14,000 $4,00 $0 $2,860 $18,000 $2,860 $2,520 $2,140 Interest on short term borrowing @1% per month April $140 $140 Interest on loan at 1% Total May $180 $180 June $180 $180 Total $500 $500 1.00% First Store Budgeted Income Statement For the Three Months Ending June 30 | April May June $50,000 $24,500 $25,500 $60,000 $29,400 $30,600 $70,000 $34,300 $35,700 Total Quarter $180,000 $88,200 $91,800 Sales Less: Cost of Goods sold Gross profit Less: Operating expenses Wages and Salaries Utilities Rent Insurance Depreciation Interest on Loan Net Income $25,000 $1,000 $2,000 $500 $100 $140 -$3,240 $25,000 $1,000 $2,000 $500 $100 $180 $1,820 $25,000 $1,000 $2,000 $500 $100 $180 $6,920 $75,000 $3,000 $6,000 $1,500 $300 $500 $5,500 $2,860 $41,000 $52,920 $500 $97,280 First Store Budgeted Balance Sheet As of June 30 Assets Current Assets Cash Accounts receivable Inventory Prepaid Insurance Fixed Assets Property and Equipment Less : Depreciation during the Quarter Total Assets Liabilities and Stockholders' Equity Current liabilities Accounts payable Dividends payable Short term borrowing Interest on loan payable Total Liabilities $25,000 -$300 $24,700 $121,980 $46,060 $6,000 $18,000 $500 $70,560 Stockholders' equity Common stock Retained earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity $14,920 $36,500 $51,420 $121,980 Budgeted Statement of Retained Earnings Retained earnings Add: Net profit Less: Dividend proposed Retained earnings $37,000 $5,500 -$6,000 $36,500 Accounts receivable closing balance Opening Balance Add : Sales Less: Collection Closing Balance $25,000 $180,000 -$164,000 $41,000 Accounts payable closing balance Opening Balance Add: Purchases Less : Payment Closing Balance $25,480 $111,720 -$91,140 $46,060 7 When interest rate is changed from 12% to 10% Interest will change from $500 to $415 Budgeted interest will be $415 for the quarter It will affect following budgets cash budget Budgeted profit and loss account Budgeted Balance Sheet

Sales Budget April May June Quarter =SUM(D4:F4) Budgeted Sales 50000 6000 70000 Schedule of Cash collected from Customers May June Quarter Collection February Sales March Sales April Sales May Sales June Sales Total Cash Collections 5000 16000 -D4-50% 4000 =D4-40% =E4*50% =D4-D11-E11 =E4*40% =F4*50% =SUM(F9:F13) =SUM(D9:F9) =SUM(D10:F10) =SUM(D11:F11) =SUM(D12:F12) =SUM(D13:F13) =SUM(G9:13) =SUM(D9:013) =SUM(E9:E13) Cost of Goods sold April May June =F4 Sales COGS -49% =D4 =D19*49% =F4 =F19*49% Quarter =SUM(D19:F19) SUM(D20:F20) July 90000 =H19*49% =E19*49% June July =H20 Purchase Budget April Budgeted COGS =D20 Add: Closing stock ( 120% of sale of follow =E25*120% Total Needs =SUM(D25:026) Less Opening Stock 29400 Required Purchase (Value) =D27-D28 May =E20 =F25*120% =SUM(E25:E26) =D26 =E27-E28 =F20 =H25120% =SUM(F25:F26) =E26 =F27-F28 Quarter =SUM(D25:F25) =F26 =SUM(G25:626) =D28 =G27-G28

B Closing stock for June- ($44,100*120%)=$52, Schedule of Cash payment for purchases April May June 25480 =D29 March Purchase April Purchase May Purchase Total Cash Payments Quarter =SUM(D35:F35) =SUM(D36:F36) =SUM(D37:F37) =SUM(G35:37) =E29 -SUM(F35:F37) =SUM(D35:D37) =SUM(E35:E37) First Store Cash Budget For the Three months Ended June 30, 2020 April May =D63 June Quarter 2000 =E63 =D46 Opening Balance of Cash Receipt of Cash Collection from Customers Cash available for disbursement =D14 =SUM(D46:D48) =E14 =SUM(E46:E48) =F14 =SUM(F46:F48) =SUM(D48:F48) =SUM(G46:G48) E38 25000 1000 2000 =F38 25000 1000 2000 Merchandise purchase =D38 Wages and Salaries 25000 Utilities 1000 Rent 2000 Dividend 6000 Cash paid for disbursements =SUM(D51:055) Net Cash available =D49-D56 Financing Borrowings (at the beginning of the month 14000 Renavments at the end of month) =SUM(D51:F51) =SUM(D52:F52) =SUM(D53:F53) =SUM(D54:F54) =SUM(D55:F55) =SUM(G51:G55) =G49-656 =SUM(E51:E55) =E49-E56 =SUM(F51:F55) =F49-F56 4000 =SUM(D59:F59) -SUMIDEO-FOY

4000 Financing Borrowings (at the beginning of the month 14000 Repayments ( at the end of month) Interest (at 1% per month) Total financing =SUM(D59:061) Closing cash balance =D57+062 =SUM(D59:F59) =SUM(D60:F60) =SUM(D61:F61) =SUM(G59:G61) =G57+G62 =SUM(E59:E61) =E57+E62 -SUM(F59:F61) =F57+F62 Interest on short term borrowing @ 1% per mc April Interest on loan at 1% =D59*168 Total =SUM(D68:D68) May =18000*168 =SUM(E68:E68) June =18000*168 =SUM(F68:F68) Total =SUM(D68:F68) =SUM(D69:F69) 0.01 First Store Budgeted Income Statement For the Three Months Ending June 30 April May June =D4 =D77-49% =D77-078 =E77*49% =E77-E78 =F4 =F77*49% =F77-F78 Sales Less: Cost of Goods sold Gross profit Less: Operating expenses Wages and Salaries Utilities Rent Insurance Depreciation Doctoplote Total Quarter =SUM(D77:F77) =SUM(D78:F78) =SUM(D79:F79) =SUM(D80:F80) =SUM(D81:F81) =SUM(D82:F82) =SUM(D83:F83) =SUM(D84:F84) =SUM(D85:F85) -CUMANOS.COM =D52 =D53 =E52 =E53 =F52 =F53 =F54

Depreciation Interest on Loan Net Income 100 100 100 =SUM(D85:F85) =D68 =E68 =F68 =SUM(D86:F86) =D79-D81-D82-D83-D85-D84- =E79-E81-E82-E83-E85-E84-E8 =F79-F81-F82-F83-F85-F84-FE-SUM(D87:F87) =SUM(D95:098) First Store Budgeted Balance Sheet As of June 30 Assets Current Assets Cash =F63 Accounts receivable =D128 Inventory =F26 Prepaid Insurance 500 Fixed Assets Property and Equipment 25000 Less : Depreciation during the Quarter -300 Total Assets Liabilities and Stockholders' Equity Current liabilities Accounts payable =D134 Dividends payable 6000 Short term borrowing =SUM(659) Interest on loan payable =SUM(D86:F86) Total Liabilities =SUM(D100:D101) =SUM(E95:E101) =SUM(D105:D108) 111 Stockholders' equity Common stock Retained earnings Total Stockholders' Fauit 14920 =E121 114 -SUMID112-11131