Esteem is a retail company that sells specialized gardening products. The compan
ID: 2526437 • Letter: E
Question
Esteem 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 $350000 of which 40 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 $320000 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 termplate. Check your answers here before moving to the next part, by completing the cells requested in the chart below. a. Sales Budget Cash sales October November 168000 509600 Sales on account 302400 Total budgeted sales 350000 127400 October December Total-Qtr b. Schedule of Cash Receipts Current cash sales Plus collections from A/R 210000 Total collections 140000 971600 The cost of goods sold is 60 percent of sales. The company desires to maintain a minimum ending inventory equal to 10 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 30 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. Inventory Purchases Budget October November December Total-Qtr d cost of goods sold 302400 s desired ending inventory nventory needed ess beginning inventory equired purchases (on account) 30240 783600 25200 783600 tober November mber Cash payments for inventory Payment of current month's A/P Payment for prior month's A/P Total budgeted payments 164640 344568 579648Explanation / Answer
Answer a Sales Budget Oct Nov Dec Total Sales in $ 350,000.00 420,000.00 504,000.00 1,274,000.00 Cash Sales - 40% of Sales 140,000.00 168,000.00 201,600.00 509,600.00 Credit Sales - 60% of Sales 210,000.00 252,000.00 302,400.00 764,400.00 Answer b Schedule of Cash Receipts Oct Nov Dec Total Cash Sales 140,000.00 168,000.00 201,600.00 509,600.00 Collection from Accounts Receivables Oct Sales - 210,000.00 210,000.00 Nov Sales - - 252,000.00 252,000.00 Dec Sales - - - - Total cash Collections 140,000.00 378,000.00 453,600.00 971,600.00 Answer c Inventory Purchase Budget Oct Nov Dec Total Budgeted Cost of Goods Sold - 60% of Sales 210,000.00 252,000.00 302,400.00 764,400.00 Add: Closing Inventory 25,200.00 30,240.00 36,288.00 36,288.00 Total Needs 235,200.00 282,240.00 338,688.00 800,688.00 Less: Beginning Inventory - (25,200.00) (30,240.00) - Required Purchases in $ 235,200.00 257,040.00 308,448.00 800,688.00 Answer d Schedule of Cash payments For Inventory Oct Nov Dec Total Cash Payment Oct Purchases 70,560.00 164,640.00 235,200.00 Nov Purchases 77,112.00 179,928.00 257,040.00 Dec Purchases 92,534.40 92,534.40 Total Cash Payment to Suppliers 70,560.00 241,752.00 272,462.40 584,774.40 Answer e Selling & Admn. Budget Oct Nov Dec Total Salaries Expense 28,900.00 28,900.00 28,900.00 86,700.00 Sales Comm. - 5% of sales 17,500.00 21,000.00 25,200.00 63,700.00 Supplies Commission - 2% of Sales 7,000.00 8,400.00 10,080.00 25,480.00 Utilities 1,500.00 1,500.00 1,500.00 4,500.00 Depreciation on Store Fixtures 7,500.00 7,500.00 7,500.00 22,500.00 Rent 13,000.00 13,000.00 13,000.00 39,000.00 Miscellaneous 2,700.00 2,700.00 2,700.00 8,100.00 Total 78,100.00 83,000.00 88,880.00 249,980.00 Schedule of Cash payments of Selling & Admn. Budget April May June Total Salaries Expense 28,900.00 28,900.00 28,900.00 86,700.00 Sales Comm. - 5% of sales 17,500.00 21,000.00 38,500.00 Supplies Commission - 2% of Sales 7,000.00 8,400.00 10,080.00 25,480.00 Utilities 1,500.00 1,500.00 3,000.00 Rent 13,000.00 13,000.00 13,000.00 39,000.00 Miscellaneous 2,700.00 2,700.00 2,700.00 8,100.00 Total 51,600.00 72,000.00 77,180.00 200,780.00 Answer f. Cash budget April May June Total Opening cash Balance - 30,840.00 30,458.00 - Add: receipts Issuance of Stock 220,000.00 220,000.00 Collection from Customers 140,000.00 378,000.00 453,600.00 971,600.00 Total Cash available 360,000.00 408,840.00 484,058.00 1,191,600.00 Less: Disbursements For Inventory Purchase 70,560.00 241,752.00 272,462.40 584,774.40 Selling & Admn. Exp. 51,600.00 72,000.00 77,180.00 200,780.00 Purchase of Equipment 370,000.00 - - 370,000.00 Dividend Paid - - 115,000.00 115,000.00 Interest Expense 1,630.00 1,000.00 2,630.00 Total Budgeted Payments 492,160.00 315,382.00 465,642.40 1,273,184.40 Cash Balance before Borrow / Repay (132,160.00) 93,458.00 18,415.60 (81,584.40) Add: Financing 163,000.00 12,000.00 175,000.00 Less: Borrowing Repayment - (63,000.00) - (63,000.00) Net Cash Balance Closing 30,840.00 30,458.00 30,415.60 30,415.60
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.