Question

Garmin Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2019.

Balance Sheet March 31, 2020
Assets
Cash	$                                                      2,000
Accounts receivable	25,000
Inventory	29,400
Prepaid Insurance	2,000
Net Fixtures	25,000
Total Assets	$                                                    83,400

                             

	Liabilities
Accounts payable		25,480
Dividends payable		6,000
Total Liabilities			$        31,480
	Stockholders' Equity
Common Stock		$      14,920
Retained Earnings		37,000	51,920
Total liabilities and Stockholders' equity			$        83,400

              

Month	Sales Revenue
January	$                                                    60,000
February	50,000
March	40,000
April	50,000
May	60,000
June	70,000
July	90,000
Wages and salaries	$                                                    25,000
Depreciation	100
Utilities	1,000
Rent	2,000
Prepaid insurance is for following months	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.

Cost of goods sold is equal to 49% of sales revenue in month of sale. Ending Inventories are sufficient for 120% of next month's cost of sales. Purchases during any given month for merchandise are paid in full during the following month.                                                  

All sales are on account. 50% collected during month of sale, 40% collected during the next month after sale, 10% collected the second month after sale. New Loans will be repaid if excess cash is over the required minimum cash balance in increments of $1,000 at an annual interest rate of 12% or monthly of 1.00%. The company requires a minimum cash balance of $2,000 at beginning of each month. At the time the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and all repayment is at the end of the month. Therefore, interest is incurred the month of the borrowing and thereafter until paid at the end of the month when there is sufficient funds.

Prepare a cash budget for each month of the second quarter ending June 30, 2019. Include budgeted borrowings and repayments if applicable.

Prepare a budgeted income statement for each month of the second quarter ending June 30, 2019 along with a quarter column.

Prepare a budgeted balance sheet as of June 30, 2019.  

Answer 1

Answer is given below with all required schedules

April $50,000 May $60,000 June $70,000 Quarter $180,000 Budgeted Sales Schedule of Cash collected from Customers 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 Purchase Budget July $44,100 Budgeted COGS Add: Closing stock ( 20% 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 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 Garmin Store Cash Budget For the Three months Ended June 30, 2019 April May $2,000 $2,000 June $2,000 Quarter $2,000 Opening Balance of Cash Receipt of Cash Collection from Customers Cash available for disbursement $46,000 $48,000 $54,000 $56,000 $64,000 $66,000 $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 -$2,380 $63,280 $2,720 $13,480 $4,380 $17,860 $13,480 $2,000 $4,380 $2,000 $0 $2,720 $0 $17,860 $2,720 Interest on short term borrowing @ 1% per month Interest on loan at 1% Total April $135 $135 May $180 $180 June $180 $180 Total $495 $495 Garmin Store Budgeted Income Statement For the Three Months Ending June 30 Total April May June $50,000 $24,500 $25,500 $60,000 $29,400 $30,600 $70,000 $34,300 $35,700 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 $135 -$3,235 $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 $495 $5,505 $2,720 $41,000 $52,920 $500 $97,140 Garmin 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,840 $46,060 $6,000 $17,860 $495 $70,415 Stockholders' equity Common stock Retained earnings Total Stockholders' Equity I Total Liabilities and Stockholders' Equity $14,920 $36,505 $51,425 $121,840 Budgeted Statement of Retained Earnings Retained earnings Add: Net profit Less: Dividend proposed Retained earnings $37,000 $5,505 -$6,000 $36,505 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

2 Sales Budget April May June - 15000 April 6000 May 10000 4 Budgeted Sales June Quarter SUM(D4:54) 50000 - 60000 70000 April May 1 June Quarter 5000 6 Schedule of Cash collected from Customers 7 8 Collection 9 February Sales 10 March Sales 11 April Sales 12 May Sales 13 June Sales Total Cash Collections 16000 =D4-50% 4000 =D4-40% =E4*50% =D4-D11-E11 = 4*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) 17 Cost of Goods sold April May June July =E4 19 Sales 20 COGS -49% =D4 =D19*49% Quarter =SUM(D19:F19) -SUM(D20:F20) 90000 =H19*49% =E1949% =F19*49% 23 Purchase Budget July =H20 25 Budgeted COGS 26 Add: Closing stock ( 20% of sale of following month) 27 Total Needs 28 Less Opening Stock Required Purchase (Value) April =D20 =E25*120% =SUM(D25:026) 29400 1=D27-D28 May =E20 =F25120% =SUM(E25:E26) =D26 =E27-E28 June =F20 =H25.120% =SUM(F25:F26) =E26 =F27-F28 Quarter ESUM(D25:F25) =F26 =SUM(G25:26) =D28 =G27-G28

E 31 Closing stock for June- (544,100*120%)=552,920 32 T M April May Tune 25480 33 Schedule of Cash payment for purchases 34 35 March Purchase 36 April Purchase 37 May Purchase Total Cash Payments =D29 Quarter =SUM(D35:F35) =SUM(D36:F36) =SUM(D37:F37) -SUM(G35:637) =E29 -SUM(D35:D37) 5:037) =SUM(E35:E37) - SUM(E35:E37) =SUM(F35:F37) -SUM(F35:F37) Garmin Store Cash Budget For the Three months Ended June 30, 2019 April =D63 May June Quarter 2000 =E63 =D46 =D14 =SUM(D46:048) =E14 -SUM(E46:E48) =F14 =SUM(F46:F48) =SUM(D48:F48) -SUM(G46:G48) 46 Opening Balance of Cash 47 Receipt of Cash 48 Collection from Customers 49 Cash available for disbursement 50 51 Merchandise purchase 52 Wages and Salaries 53 Utilities 54 Rent 55 Dividend 56 Cash paid for disbursements 57 Net Cash available 58 Financing 59 Borrowings (at the beginning of the month) 60 Renaments at the end of month =D38 25000 1000 2000 6000 =SUM(D51:055) =D49-D56 =E38 25000 1000 2000 =F38 25000 1000 2000 =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 13480 4380 =SUM(D59:F59) -SUMIDEO-F601

E 66 Interest on short term borrowing @ 1% per month 68 Interest on loan at 1% 69 Total April =D59.1% -SUM(D68:D68) May =18000* 1% -SUM(E68:E68) June =18000*1% =SUM(F68:F68) Total =SUM(D68:F68) =SUM(D69:F69) =E4 77 Sales 78 Less: Cost of Goods sold 79 Gross profit 80 Less: Operating expenses 81 Wages and Salaries 82 Utilities 83 Rent 84 Insurance 85 Depreciation 86 Interest on Loan 87 Net Income Garmin Store Budgeted Income Statement For the Three Months Ending June 30 Total April May June Quarter =D4 =SUM(D77:F77) =D77-49% =E77.49% =F77.49% =SUM(D78:F78) =D77-078 =E77-E78 =F77-F78 =SUM(D79:F79) =SUM(D80:F80) =D52 =E52 =F52 =SUM(D81:F81) =D53 =E53 =F53 =SUM(D82:F82) =E54 =F54 =SUM(D83:F83) 500 500 500 =SUM(D84:F84) 100 100 =SUM(D85:F85) =D68 =E68 =F68 =SUM(D86:F86) =D79-D81-082-D83-D85-D84=E79-E81-E82-E83-E85-E84-E8 =F79-F81-F82-F83-F85-F84-F -SUM(D87:F87) -054 100 88

=SUM(D95:098) =SUM(D100:0101) -SUM(E95:E101) Garmin Store Budgeted Balance Sheet As of June 30 Assets 94 Current Assets 95 Cash =F63 96 Accounts receivable =D128 97 Inventory =F26 98 Prepaid Insurance 500 99 Fixed Assets 100 Property and Equipment 25000 101 Less : Depreciation during the Quarter -300 102 Total Assets 103 Liabilities and Stockholders' Equity 104 Current liabilities 105 Accounts payable =D134 106 Dividends payable 6000 107 Short term borrowing =SUM(659) 108 Interest on loan payable -SUM(D86:F86) 109 Total Liabilities 110 111 Stockholders' equity 112 Common stock 14920 113 Retained earnings =E121 114 Total Stockholders' Equity 115 Total Liabilities and Stockholders' Equity =SUM(D105:D108) =SUM(D112:D113) =SUM(E104:E114) 116

April $50,000 May $60,000 June $70,000 Quarter $180,000 Budgeted Sales Schedule of Cash collected from Customers 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 Purchase Budget July $44,100 Budgeted COGS Add: Closing stock ( 20% 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 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 Garmin Store Cash Budget For the Three months Ended June 30, 2019 April May $2,000 $2,000 June $2,000 Quarter $2,000 Opening Balance of Cash Receipt of Cash Collection from Customers Cash available for disbursement $46,000 $48,000 $54,000 $56,000 $64,000 $66,000 $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 -$2,380 $63,280 $2,720 $13,480 $4,380 $17,860 $13,480 $2,000 $4,380 $2,000 $0 $2,720 $0 $17,860 $2,720 Interest on short term borrowing @ 1% per month Interest on loan at 1% Total April $135 $135 May $180 $180 June $180 $180 Total $495 $495 Garmin Store Budgeted Income Statement For the Three Months Ending June 30 Total April May June $50,000 $24,500 $25,500 $60,000 $29,400 $30,600 $70,000 $34,300 $35,700 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 $135 -$3,235 $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 $495 $5,505 $2,720 $41,000 $52,920 $500 $97,140 Garmin 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,840 $46,060 $6,000 $17,860 $495 $70,415 Stockholders' equity Common stock Retained earnings Total Stockholders' Equity I Total Liabilities and Stockholders' Equity $14,920 $36,505 $51,425 $121,840 Budgeted Statement of Retained Earnings Retained earnings Add: Net profit Less: Dividend proposed Retained earnings $37,000 $5,505 -$6,000 $36,505 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