Question

Can you please create an excel Assumption page with the given date to be used in a master budget that needs to be all referenced to the assumption page. Thank you

The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods Current assets as of March 31: Cash 8,500 24,000 45,600 $121,200 $ 27,300 $150,000 $ 22,000 Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings a. The gross margin is 25% of sales. b. Actual and budgeted sales data: $ 60,000 $76,000 $ 81,000 $106,000 $ 57,000 March (actual) April May June July 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.

Answer 1

Shillow Company Sales Budget Sales Cash Credit April 76000 45600 30400 May 81000 48600 32400 June 106000 63600 42400 Quarter 263000 157800 105200 Sales Collection May June March April April 24000 45600 30400 48600 Quarter 24000 76000 81000 63600 244600 May June Totals 32400 63600 96000 69600 79000 Purchase Budget Cost of goods sold Ending Inventory April 57000 48600 105600 -45600 60000 May 60750 63600 124350 -48600 75750 June Quarter 79500197250 34200 34200 113700231450 -63600 -45600 50100 185850 Beginning Invent. Purchases Purchase Payments May June April 27300 30000 March April May June Totals 30000 37875 Quarter 27300 60000 75750 25050 188100 37875 25050 62925 57300 67875

Other Expenses: Commissions Rent Expense Other Expenses April 9120 3300 4560 May 9720 3300 4860 June 12720 3300 6360 Quarter 31560 9900 15780 Cash Budget For the Quarter ending June 30 Opening Balance Collections Cash Available April 8500 69600 78100 May 4320 79000 83320 June Quarter 4565 8500 96000244600 100565 253100 Payments: Purchase Payments 57300 Commissions 9120 Rent Expense 3300 Other Expenses 4560 Equipment 2500 Total Payments Excess of Cash over Payments 1320 Financing Borrowing 3000 Repayment Interest Total 3000 Cash Balance 4320 3000 x 1% x 3 = 90; 7000 x 1% x 2 = 140 +90 = 230 67875 62925 188100 9720 12720 31560 3300 3300 9900 4860 6360 15780 2500 8575585305247840 -2435 15260 5260 7000 - 10000 -230 -10230 5030 10000 10000 -230 -230 5030 7000 4565

263000 Income Statement For the Quarter ending June 30 Sales Less: Cost of goods sold: Open. Inv. Purchase Closing Inv. Gross Profit 45600 185850 -34200 197250 65750 Expenses: Commissions Rent Expense Other Expenses Interest Depreciation Net Income 31560 9900 15780 230 2727 60197 5553 Balance sheet As at Jun 30 Assets Cash Accounts Receivable Inventory Buildings & Equipment net of depre Total Assets 5030 42400 34200 120973 202603 Liabilities & Stockholders Equity Accounts Payable Common Stock Retained Earnings Total Liabilities & Stockholders Equity 25050 150000 27553 202603 Retained Earnings = 22000 + 5553 = 27553 Buildings & Equipment net of depre = 121200 + 2500 - 2727 = 120973