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 show excel formula references/formulas

Answer 1

Answer is given below

for excel formula screen shot is given

For Q.No. 7 if interest rate is changed to 10% profit and loss account and balance sheet will change.

New budgeted interest expense will be $417 for the quarter

you can change the interest rate in below worksheet and check it

Sales Budgeted COGS -49% Desired ending inventory Total Needs Less Opening Stock 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,220 $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 May $30,380 Schedule of Cash disbursements April Purchase of merchandise prior month $25,480 Current Cash operating cost $28,000 Cash dividends $6,000 Total Cash Payments $59,480 June $35,280 $28,000 $28,000 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 Opening Balance of Cash $2,000 $2,520 Total cash receipts $46,000 $54,000 Cash available for disbursement $48,000 $56,520 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 $0 $18,000 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 Interest on loan at 1% April $140 $140 May $180 $180 June $180 $180 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 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 $500 $140 $28,740 $25,000 $100 $1,000 $2,000 $500 $180 $28,780 $1,820 $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 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 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

А в First Store Cash Budget For the Three months Ended June 30, 2019 April May 2000 =D60 =D34 =E34 =SUM(D49:050) =SUM(E49:E50) Opening Balance of Cash Total cash receipts Cash available for disbursement June =E60 =F34 =SUM(F49:F50) Quarter =D49 =SUM(D50:F50) =SUM(G49:650) =E41 =E51-E53 =F41 =F51-F53 =SUM(D53:F53) =G51-G53 4000 cash disbursements =D41 Excess(Deficiency cash available ove =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 1 =SUM(E56:E58) =E54+E59 =SUM(F56:F58) =F54+F59 Interest on short term borrowing @1 April Interest on loan at 1% =D56*1% Total =SUM(D65:D65) May =18000*1% =SUM(E65:E65) June |=18000*1% =SUM(F65:F65) Total =SUM(D65:F65) =SUM(D66:F66)

ТА В G H 67 68 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-574 =F4 =F73*49% =F73-F74 Total Quarter =SUM(D73:F73) =SUM(D74:F74) =SUM(D75:F75) Sales Less: Cost of Sales Gross profit Less: expenses Wages and Salaries Depreciation Utilities Rent Insurance Interest expense Total expenses Net Income 25000 100 1000 2000 O 25000 100 1000 2000 500 =D65 =SUM(D77:D82) =D75-D83 25000 100 1000 2000 500 =F65 =SUM(F77:F82) =F75-F83 500 =SUM(D77:F77) =SUM(D78:F78) =SUM(D79:F79) =SUM(D80:F80) =SUM(D81:F81) =SUM(D82:F82) =SUM(G77:682) =SUM(D84:F84) CO CO CO =E65 =SUM(E77:E82) =E75-E83 &

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