Question

Can you do this in excel and show what functions you use to get the quarter totals and other totals. Can you also do the sales budget

The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,500 $ 24,000 $ 45, 600 $ 121,200 $ 27,300 $ 150,000 $ 22,000 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ 60,000 $ 76,000 $ 81,000 $ 106,000 $ 57,000 c. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales. d. Each month's ending inventory should equal 80% of the following month's budgeted cost of goods sold, e. One-half of a month's inventory purchases is paid for in the month of purchase; the other half is paid for in the following month. The accounts payable at March 31 are the result of March purchases of inventory. f. Monthly expenses are as follows: commissions, 12% of sales, rent, $3,300 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $909 per month (includes depreciation on new assets. g. Equipment costing $2,500 will be purchased for cash in April. h. Management would like to maintain a minimum cash balance of at least $4,000 at the end of each month. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $20,000. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.

I need to create an entire master budget.

Answer 1

Answer with working is given below

Normal add and minus formulas are used in excel. if you understand conceptually master budget excel formulas can be easily put.

if you have any other doubts do please let me know please

Sales Budget Sales Budget Cash sales Sales on Account April $76,000 $45,600 $30,400 May $81,000 $48,600 $32,400 June $106,000 $63,600 $42,400 Total $263,000 $157,800 $105,200 A Schedule of Expected Cash Collection Cash Sales Credit Sales April $45,600 $24,000 $69,600 May $48,600 $30,400 $79,000 June $63,600 $32,400 $96,000 Total $157,800 $86,800 $244,600 Total Collections Cost of Goods Sold April $76,000 Sales Cost of Goods Sold -75% May $81,000 $60,750 June $106,000 $79,500 Total $263,000 $197,250 July $57,000 $42,750 Inventory Purchase Budget Budgeted Cost of Goods Sold Add: Closing stock (80% of next month COGS) Total Needs Less Opening Stock Required Purchase (On Account) April $57,000 $48,600 $105,600 $45,600 $60,000 May $60,750 $63,600 $124,350 $48,600 $75,750 June $79,500 $34,200 $113,700 $63,600 $50,100 Total $197,250 $34,200 $231,450 $45,600 $185,850 B Schedule of Cash payment for purchases April May Total $27,300 $30,000 Payment March Purchase April Purchase May Purchase June Purchase Total payment for purchases $30,000 $37,875 $37,875 $25,050 $62,925 $27,300 $60,000 $75,750 $25,050 $188,100 $67,875 Selling and Administrative Expense budget Sales commission (12% of Sales) Other expenses ( 6% of Sales) Rent Selling and Administrative Expense paid in Cash Depreciation Total Selling and administrative expense April $9,120 $4,560 $3,300 $16,980 $909 $17,889 May $9,720 $4,860 $3,300 $17,880 $909 $18,789 June $12,720 $6,360 $3,300 $22,380 $909 $23,289 Total $31,560 $15,780 $9,900 $57,240 $2,727 $59,967 | C Cash Budget April $8,500 $69,600 $78,100 May $4,320 $79,000 $83,320 June $4,565 $96,000 $100,565 Total $8,500 $244,600 $253,100 $67,875 $17,880 $62,925 $22,380 Opening Balance of Cash Cash Collections from customers Cash available for disbursement Payments for Merchandise purchase (paid at end of the month) Selling and administrative expense Purchase of Equipment Cash paid for disbursements Net Cash available Financing Borrowings (at the beginning of the month) Repayments at the end of Quarter) Interest (at 1% per month) Total financing Closing cash balance $57,300 $16,980 $2,500 $76,780 $1,320 $188,100 $57,240 $2,500 $247,840 $5,260 $85,755 -$2,435 $85,305 $15,260 $3,000 $7,000 -$10,000 -$230 $10,000 $10,000 -$230 -$230 $5,030 $3,000 $4,320 $7,000 $4,565 $5,030 $263,000 $197,250 $65,750 Shilow Company Absorption Costing Format Income Statement For the Quarter Ended June 30 Sales Less Cost of Goods Sold Gross profit Selling and administration expenses Sales commission (12% of Sales) $31,560 Other expenses ( 6% of Sales) $15,780 Rent $9,900 Depreciation $2,727 Profit Before Interest Interest on Short term loan Net Income $59,967 $5,783 $230 $5,553 Shilow Company Balance Sheet As on June 30 Assets Current Assets Cash Accounts receivable Inventory $5,030 $42,400 $34,200 $81,630 Fixed Assets Building and Equipment Additions during the quarter $121,200 $2,500 $123,700 -$2,727 $120,973 $202,603 Less : Depreciation during the year Total Assets Total Liabilities and Shareholders Equity Current liabilities Accounts payable $25,050 Stockholders' equity Common Stock Retained earnings $150,000 $27,553 $177,553 Total Liabilities and Stockholders' Equity $202,603 Budgeted Statement of Retained Earnings Retained earnings - Opening Balance Add: Net profit Retained earnings - Closing Balance $22,000 $5,553 $27,553 Accounts receivable closing balance Opening Balance Add : Sales Less: Collection Closing Balance $24,000 $263,000 -$244,600 $42,400 Accounts payable closing balance Opening Balance Add: Purchases Less: Payment Closing Balance $27,300 $185,850 -$188,100 $25,050