Deluxe is a retail company that sells specialized gardening products. The company is considering opening a new store on October 1 Year1. As budget coordinator, you have been asked to prepare a master budget for the first 3 months of the company's operation. You have gathered the following information: October sales are estimated to be $200000 of which 45 percent will be cash and the remainder will be on credit. The company expects all sales to increase at the rate of 20 percent per month for November and December. Sales in January Year 2 are expected to be $220000 The company expects to collect 100 percent of the accounts receivable generated by credit sales in the month following the sale Prepare a sales budget and a schedule of cash receipts using these facts and your excel template. Check your answers here before moving to the next part, by completing the cells requested in the chart below OctoberNovember ecember Total-Qtr 327600 a. Sales Budget 108000 Cash sales 158400 Sales on account 728000 200000 Total budgeted sales b. Schedule ot Cash Receipts Current cash sales October November December Total-Qtr 110000 Plus collections from A/R 90000 569600 Total collections The cost of goods sold is 70 percent of sales. The company desires to maintain a minimum ending inventory equal to 40 percent of the next month's cost of goods sold. (Ending inventory for December is based on budgeted January Year2 sales.) Assume that all inventory purchases are made on account (on credit). The company pays 60 percent of accounts payable in the month of purchase and the remaining amount in the following month n excel, prepare an inventory purchases budget and a cash payments budget for inventory purchases. Use the check figures below before you continue c. Inventory Purchases Budget Budgeted cost of goods sold November December Total-Qtr October 201600 80640 Plus desired ending inventory . . The cost of goods sold is 70 percent of sales The company desires to maintain a minimum ending inventory equal to 40 percent of the next month's cost of goods sold (Ending inventory for December is based on budgeted January Year2 sales.) Assume that allinventory purchases are made on account (on credit). The company pays 60 percent of accounts payable in the month of purchase and the remaining amount in the following month In excel, prepare an inventory purchases budget and a cash payments budget for inventory purchases Use the check figures below before you continue c. Inventory Purchases Budget Budgeted cost of goods sold Total-Qtr November December October 201600 80640 Plus desired ending inventory Inventory needed Less beginning inventory Required purchases (on account) d. Cash payments for inventoryOctoberNovember December Total-Qtr Payment of current month's A/P Payment for prior month's A/P Total budgeted payments Budgeted selling and administrative expenses per month follow - salary expense (fIixed): $ 18700 Sales commissions: 4 percent of Sales Supplies expense: 1 percent oft Sales Utilities (fixed): $1600 Depreciation on store equipment (fixed) . Rent (fixed) $ 8000 You compute Miscellaneous (fixed) $ 940 The capital expenditures budget indicates that the company will spend $77500 on October 1 for store follow per month expenses Budgeted selling and administrative . Salary expense (fixed) $ 18700 . Sales commissions: 4 percent of Sales . Supplies expense: 1 percent of Sales . Utilities (fixed): $1600 You compute . Depreciation on store equipment (fixed) . Rent (fixed) $ 8000 . Miscellaneous (fixed) $ 940 The capital expenditures budget indicates that the company will spend $77500 on October 1 for store fixtures, which are expected to have a $5500 residual value and a 36 month useful life 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 In excel, prepare the selling and administrative expenses budget and the cash payments budget for selling and administrative expenses. Check the key figures below e. Selling and Admin.Expense Budget Salary expense November December Total-Qtr Sales commissions Supplies expense Utilities Depreciation on store fixtures Rent Miscellaneous Total S&A expenses t. Cash payments for s&A Total-Qtr October November December Sales commissions Supplies expense Utilities Depreciation on store fixtures Rent Miscellaneous Total payments for S&A expenses Deluxe issued common stock for $62000 on October 5. A dividend of $31000 was paid on December 15 The company borrows and repays funds in increments of $1,000 on the last day of the month. The company also pays its vendors on the last day of the month. It pays interest of 1 percent per month in cash on the last day of the month. To be prudent, the company desires to maintain a $20000 cash cushion Prepare a cash budget on your excel template. Check key figure below g. Cash Budget Beginning cash balance November DecemberTotal-Qtr October Issuance of stock Collections from customers Cash available For inventory purchases For S&A expenses Purchase of store fixtures Pay dividend Interest expense Total budgeted payments Cash balance before borrow/repay Borrowing (repayment) Ending cah balance Income statement Input expenses as negatives. Use a minus sign in front of the number Sales revenue Cost of goods sold Gross margin S&A expenses Operating income Interest expense Net income Balance Sheet Enter any contra-assets as negative numbers. Use a minus sign. Assets Cash Accounts receivable Inventory Store fixtures Accumulated depreciation Total assets Liabilities Accounts payable Utilities payable Sales commissions payable Line of credit liability Total liabilities Equity Common stock Retained earnings Total equity Total liabilgies and equity