Question

Can someone help me solve this problem please?

Problem 7-23B Preparing a master budget for a retail company with no beginning account balances

Inwood Gifts Corporation begins business today, December 31, 2017. Rebecca Ortiz, the president, is trying to prepare the company’s master budget for the first three months (January, February, and March) of 2018. Since you are her good friend and an accounting student, Ms. Ortiz asks you to prepare the budget based on the following specifications:

Required

January sales are estimated to be $500,000, of which 30 percent will be cash and 70 percent will be credit. The company expects sales to increase at the rate of 10 percent per month. Prepare a sales budget.

The company expects to collect 100 percent of the accounts receivable generated by credit sales in the month following the sale. Prepare a schedule of cash receipts.

The cost of goods sold is 50 percent of sales. The company desires to maintain a minimum ending inventory equal to 20 percent of the next month’s cost of goods sold. The ending inventory of March is expected to be $66,000. Assume that all purchases are made on account. Prepare an inventory purchases budget.

The company pays 60 percent of accounts payable in the month of purchase and the remaining 40 percent in the following month. Prepare a cash payments budget for inventory purchases.

Budgeted selling and administrative expenses per month follow:

Salary expense (fixed)	$50,000
Sales commissions	8% of sales
Supplies expense	4% of sales
Utilities (fixed)	$3,600
Depreciation on store fixtures (fixed)*	$10,000
Rent (fixed)	$?14,400
Miscellaneous (fixed)	$4,000
*The capital expenditures budget indicates that Inwood will spend $700,000 on January 1 for store fixtures. The fixtures are expected to have a $100,000 salvage value and a five-year (60-month) useful life.

Use this information to prepare a selling and administrative expenses budget.

Utilities and sales commissions are paid the month after they are incurred; all other expenses are paid in the month in which they are incurred. Prepare a cash payments budget for selling and administrative expenses.

The company borrows funds, in increments of $1,000, and repays them in any amount available on the last day of the month. It pays interest of 1.5 percent per month in cash on the last day of the month. For safety, the company desires to maintain a $100,000 cash cushion. The company pays its vendors on the last day of the month. Prepare a cash budget.

Prepare a pro forma income statement for the quarter.

Prepare a pro forma balance sheet at the end of the quarter.

Prepare a pro forma statement of cash flows for the quarter.

Answer 1

Answer is given below

Sales Budget Sales Budget Cash sales Sales on Account January $500,000 $150,000 $350,000 February $550,000 $165,000 $385,000 March Total $605,000 $1,655,000 $181,500 $496,500 $423,500 $1,158,500 B Schedule of Cash Receipts January $150,000 Current Cash Sales Plus collections from A/R Total Collections February $165,000 $350,000 $515,000 March Total $181,500 $496,500 $385,000 $735,000 $566,500 $1,231,500 $150,000 Cost of Goods Sold Sales Cost of Goods Sold -50% January $500,000 $250,000 February $550,000 $275,000 March Total $605,000 $1,655,000 $302,500 $827,500 C Inventory Purchase Budget COGS Add: Closing stock (20% of next month COGS) Inventory Needed Less Opening Stock Required Purchase (On Account) January $250,000 $55,000 $305,000 February $275,000 $60,500 $335,500 $55,000 $280,500 March $302,500 $66,000 $368,500 $60,500 $308,000 T otal $827,500 $66,000 $893,500 $0 $893,500 $305,000 D Schedule of Cash payment for purchases January February March Total $183,000 Payment Payment of Current Month Account payable Payment for prior month Accounts payable Total payment for purchases $168,300 $122,000 $290,300 $184,800 $112,200 $297,000 $536,100 $234,200 $770,300 $183,000 E Selling and Administrative Expense budget Salary expense Sales commission (8% of Sales) Supplies expense ( 4% of Sales) Utilities Depreciation on stores fixtures Rent Miscellaneous January $50,000 $40,000 $20,000 $3,600 $10,000 $14,400 $4,000 February $50,000 $44,000 $22,000 $3,600 $10,000 $14,400 $4,000 March $50,000 $48,400 $24,200 $3,600 $10,000 $14,400 $4,000 Total $150,000 $132,400 $66,200 $10,800 $30,000 $43,200 $12,000 Total Selling and administrative expense 142,000 148,000 154,600 444,600 F Schedule of Cash payments for S & A Expenses January $50,000 Salary expense Sales commission (8% of Sales) Supplies expense ( 4% of Sales) Utilities Rent Miscellaneous Total cash for Selling and administrative expense $20,000 February $50,000 $40,000 $22,000 $3,600 $14,400 $4,000 $134,000 March $50,000 $44,000 $24,200 $3,600 $14,400 Total $150,000 $84,000 $66,200 $7,200 $43,200 $12,000 $362,600 $14,400 $4,000 $88,400 I $140,200 G Cash Budget January February $100,100 $515,000 $615,100 March Total $100,000 $566,500 $1,231,500 $666,500 $1,231,500 $150,000 $150,000 $290,300 $134,000 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 Stores fixtures Cash paid for disbursements Net Cash available Financing Borrowings (at the beginning of the month) Borrowings (at the end of the month) Repayments at the end of month) Interest (at 1.5% per month) Total financing Closing cash balance $183,000 $88,400 $700,000 $971,400 -$821,400 $297,000 $770,300 $140,200 $362,600 $700,000 $437,200 $1,832,900 $229,300 $601,400 $424,300 $190,800 $700,000 $232,000 $700,000 $232,000 $116,472 -$193,292 -$12,828 $37,308 $129,300 $701,400 $100,000 $100,000 $76,820 $13,980 -$90,800 $100,000 -$10,500 $921,500 $100,100 $1,655,000 $827,500 $827,500 INWOOD GIFTS CORPORATION Performa Income Statement For the Quarter ended March 31st Sales Less Cost of Goods Sold Gross profit Salary expense Sales commission Supplies expense Utilities Depreciation on stores fixtures Rent Miscellaneous Interest on short term loans $150,000 $132,400 $66,200 $10,800 $30,000 $43,200 $12,000 $37,308 $481,908 $345,592 Net Income INWOOD GIFTS CORPORATION Proforma Balance Sheet March 31st Assets Current Assets Cash Accounts receivable Inventory $100,000 $423,500 $66,000 $589,500 $700,000 $30,000 $670,000 $1,259,500 Fixed Assets Stores fixture Less: Depreciation during the year Total Assets Current liabilities Accounts payable Outstanding expense Short term borrowing Total Current Liabilities Stockholders' equity Retained earnings Total Liabilities and Stockholders' Equity $123,200 $52,000 $738,708 $913,908 $345,592 $1,259,500 Budgeted Statement of Retained Earnings Retained earnings - Opening Balance Add: Net profit Retained earnings - Closing Balance 345,592 345,592 Accounts receivable closing balance Opening Balance Add : Sales Less: Collection Closing Balance 1,655,000 -1,231,500 423,500 Accounts payable closing balance Opening Balance Add: Purchases Less: Payment Closing Balance 893,500 -770,300 123,200

INWOOD GIFTS CORPORATION Proforma Statement of Cash flows For the Quarter Ended March 31st Cash Flow from Operating Activities Cash received from Customers $1,231,500 Cash payment to Vendors - $770,300 Cash payment for Operating Expenses -$362,600 Interest paid to Bank -$37,308 Net cash from Operating Activities $61,292 Cash Flow from Investing Activities Purchase of Stores Fixtures -$700,000 $932,000 -$193,292 Cash Flow from Financing Activities Short term borrowing Repayment of short term borrowing Net cash from Financing Activities Net Cash inflow/(Outflow) Cash-Closing balance $738,708 $100,000 $100,000