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.

Please use above formats for each budget and show cell references/formulas for all parts to receive credit

Answer 1

Answer is given below. For Excel Screen shot of formulas is given .please link the excel accordingly

Budgeted Sales Current Cost of Goods sold -49% Total Inventory needed Total Needs Less Beginning inventory Budgeted Purchase Purchase Budget April $50,000 $24,500 $35,280 $59,780 $29,400 $30,380 May $60,000 $29,400 $41,160 $70,560 $35,280 $35,280 June $70,000 $34,300 $52,920 $87,2201 $41,160 $46,060 Quarter $180,000 $88,200 $52,920 $141,120 $29,400 $111,720 Closing stock for June ($44,100*120%)=$52,920 June Quarter Schedule of Budgeted cash collections April May Collection 50% of Current month sales $25,000 $30,000 40% of last month sales $16,000 $20,000 10% of sales two months prior $5,000 $4,000 Total Cash Collections $46,000 $54,000 $35,000 $24,000 $5,000 $64,000 $90,000 $60,000 $14,000 $164,000 Schedule of Cash disbursements April $25,480 $28,000 $6,000 $59,480 May $30,380 $28,000 June $35,280 $28,000 Purchase of merchandise prior month Current Cash operating cost Cash dividends Total Cash Payments Quarter $91,140 $84,000 $6,000 $181,140 $58,380 $63,280 First Store Cash Budget For the Three months Ended June 30, 2019 April May $2,000 $2,520 $46,000 $54,000 $48,000 $56,520 Cash balance beginning Total cash receipts Cash available for disbursement June $2,140 $64,000 $66,140 Quarter $2,000 $164,000 $166,000 $59,480 $11,480 $58,380 -$1,860 $63,280 $2,860 $181,140 -$15,140 $14,000 $4,000 $18,000 Total cash disbursements Excess(Deficiency cash available over disbursements Short term financing New Loans Repayments Interest (at 1% per month) Total financing Cash balance ending $14,000 $2,520 $4,000 $2,140 $0 $2,860 $18,000 $2,860 Interest on short term borrowing @ 1% per month April $140 $140 Interest on loan at 1% May $180 $180 June $180 $180 Interest rate 12% per year 1.00% per month Total $500 $500 Total First Store Budgeted Income Statement For the Three Months Ending June 30, 2020 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 $25,000 $100 Sales Less: Cost of Sales Gross profit Less: expenses Wages and Salaries Depreciation Utilities Rent Insurance Interest expense Total expenses Net Income $25,000 $100 $1,000 $2,000 $1,000 $2,000 $500 $25,000 $100 $1,000 $2,000 $500 $180 $28,780 $6,920 $75,000 $300 $3,000 $6,000 $1,500 $500 $86,300 $5,500 $500 $140 $28,740 -$3,240 $180 $28,780 $1,820 Assets Current Assets Cash Accounts receivable Inventory Prepaid Insurance Fixed Assets Property and Equipment Less: Depreciation during the Quarter First Store Budgeted Balance Sheet As of June 30,2020 Liabilities and Stockholders' Equity Current liabilities $2,860 Accounts payable $46,060 $41,000 Dividends payable $6,000 $52,920 Loans payable $18,000 $500 $97,280 Interest payable $500 Total Liabilities $25,000 -$300 $24,700 Stockholders' equity Common stock $14,920 Retained earnings $36,500 Total Stockholders' Equity $121,980 Total Liabilities and Stockholders' Equity | $70,560 $51,420 $121,980 Total Assets 7 If Interest rate is changed to 10% Budgeted Income Statement and Budgeted Balance sheet will change New Interest will be $417 against current interest expense of $500 for the quarter Working notes: Budgeted Statement of Retained Earnings Retained earnings - Opening balance 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

D G Budgeted Sales Current Cost of Goods sold -49% Total Inventory needed Total Needs Less Beginning inventory Budgeted Purchase April =D4 =D18 =E19*120% =SUM(D19:D20) 29400 =D21-D22 May =E4 =E18 =F19*120% =SUM(E19:E20) =D20 =E21-E22 June =F4 =F18 =M19*120% =SUM(F19:F20) =E20 =F21-F22 Quarter =SUM(D17:F17) =SUM(D19:F19) =F20 =SUM(G19:20) =D22 =G21-G22 Closing stock for June- ($44,100*120 Schedule of Budgeted cash collections May April June Quarter Collection 50% of Current month sales 40% of last month sales 10% of sales two months prior I Total Cash Collections 25000 16000 5000 | =SUM(D31:D33) 30000 20000 4000 =SUM(E31:E33) 35000 24000 5000 =SUM(F31:F33) =SUM(D31:F31) =SUM(D32:F32) =SUM(D33:F33) =SUM(G31:G33) June April Purchase of merchandise prior mon25480 Current Cash operating cost 28000 Cash dividends 6000 Total Cash Payments =SUM(D38:D40) Schedule of Cash disbursements May =D23 28000 =E23 28000 Quarter =SUM(D38:F38) =SUM(D39:F39) =SUM(D40:F40) =SUM(G38:640) =SUM(E38:E40) =SUM(F38:F40)

B H D Cash Budget For the Three months Ended June 30, 2019 April May June 2000 =D60 =E60 =D34 =E34 =F34 -SUM(D49:D50) =SUM(E49:E50) =SUM(F49:F50) Cash balance beginning Total cash receipts Cash available for disbursement Quarter =D49 =SUM(D50:F50) =SUM(G49:650) =E41 =E51-E53 =F41 =F51-F53 =SUM(D53:F53) =G51-G53 4000 Total cash disbursements =D41 Excess(Deficiency cash available ov=D51-D53 Short term financing New Loans 14000 Repayments Interest (at 1% per month) Total financing =SUM(D56:D58) Cash balance ending =D54+059 =SUM(D56:F56) =SUM(D57:F57) =SUM(D58:F58) =SUM(G56:658) =G54+G59 =SUM(E56:E58) =E54+E59 =SUM(F56:F58) =F54+F59 Interest on short term borrowing @ May April =D56*$166 =SUM(D65:D65) Interest on loan at 1% Total Interest rate 0.12 =165/12 June =18000*$166 =SUM(F65:F65) |=18000*$166 =SUM(E65:565) Total =SUM(D65:F65) =SUM(D66:F66) per year per month

67 68 5 69 First Store Budgeted Income Statement For the Three Months Ending June 30, 2020 April May June =D4 =D73*49% =D73-D74 =E4 =E73*49% =E73-E74 =F4 =F73*49% =F73-F74 Total Quarter =SUM(D73:F73) =SUM(D74:F74) =SUM(D75:F75) 25000 100 Sales Less: Cost of Sales Gross profit Less: expenses Wages and Salaries Depreciation Utilities Rent Insurance Interest expense Total expenses Net Income 1000 2000 25000 100 1000 2000 500 =D65 =SUM(D77:D82) =D75-D83 25000 100 1000 2000 500 =E65 =SUM(E77:E82) =E75-E83 500 =SUM(D77:F77) =SUM(D78:F78) =SUM(D79:F79) =SUM(D80:F80) =SUM(D81:F81) |=SUM(D82:F82) =SUM(G77:G82) =SUM(D84:F84) =F65 =SUM(F77:F82) =F75-F83

H Assets Current Assets Cash Accounts receivable Inventory Prepaid Insurance Fixed Assets Property and Equipment Less : Depreciation during the Quarter First Store Budgeted Balance Sheet As of June 30, 2020 Liabilities and Stockholders' Current liabilities Accounts payable Dividends payable Loans payable =SUM(D92:095) Interest payable Total Liabilities =F60 =D123 =F20 500 =D129 6000 =SUM(656) =SUM(D82:F82) =SUM(192:195) 25000 |-300 =SUM(D97:D98) Stockholders' equity Common stock Retained earnings Total Stockholders' Equity Total Liabilities and Stockhol 14920 =E116 =SUM(199:1100) =SUM(191:J101) Total Assets =SUM(E92:E98)

Working notes: 111 112 Budgeted Statement of Retained Earnings Retained earnings - Opening balance Add: Net profit Less: Dividend proposed Retained earnings 37000 =SUM(D84:F84) -6000 115 116 =SUM(E113:E115) 118 119 121 Accounts receivable closing balance Opening Balance Add : Sales Less: Collection Closing Balance 25000 =G4 --G50 =SUM(D120:D122) 125 126 127 Accounts payable closing balance Opening Balance Add: Purchases Less : Payment Closing Balance =D38 =G23 --G38 =SUM(D126:D128) 129 130