help please

Winter 2020 Budget Planning Following is selected information relating to the operations of Pocketchange Company, a tile distributor September 30, 2020 - Trial Balance information $ ................................. Accounts receivable..... ......... Inventory Plant and equipment, net of Acc. Dep. Accounts payable.............................. Capital stock......................... Retained earnings.... . ....... 8.000 24.800 39.600 428,200 24,050 150,000 326,550 500,600 $ 500,600 $ a. Actual and budgeted sales data: September (actual)................... October.... November................................ December................... .. January... BERBEDRE 62.000 66,000 80,000 94,000 48.000 b. Gross profit is 25% of sales (or 75% Cost of Goods Sold %). C. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at September 30 are a result of the September's credit sales d. At the end of each month, inventory on hand is to be equal to 80% of the following month's sales needs, stated at cost. e. One-half of a month's inventory purchases is paid in the month of purchase the other half is paid in the following month. The accounts payable at September 30 are a result of September purchases of inventory. 1 Monthly expenses are as follows: salaries and wages, 12% of sales, rent, $2,500 per month; other expenses (excluding depreciation). 6% of sales. Assume that these expenses are paid monthly. Depreciation is $1,300 per month (includes depreciation on new assets) Equipment costing $7,000 will be purchased for cash in the October and another $5,000 in November h. The company must maintain a minimum cash balance of $5,000. An open line of credit is available at a local bank. All borrowing is done at the beginning of a month, and all repayments are made at the end of a month: borrowing and repayments must be made in multiples of $1.000. The annual interest rate is 12%. Interest is paid only at the time of repayment of principles figure interest on the whole month (2/12, 3/12, and so forth). Any unpaid interest is accrued at the end of the quarter. Budget(excel) Required: Using the data above Sales 1 Complete the following schedule Schedule of Expected Cash Teash sales Credit sales Total collections 3 34330 2. Complete the following Inventory Purchase 49300 48.000 udgeted cost of goods sold Add: Desired ending inventory Total needs Less Beginning inventory Required purchases Oct Sales X 75% costatos Now Sales X 75% cost to X 80 39,500 Schedule of Expected Cash Disbursement Purchases Oct Sept purchases $ 24050 Oct. purchases 28,950 Nov purchases........ .... . Dec purchases Tegal bursements for purchases $ 53000 28.960 Quarter 2400 57 900 3 Complete the following Schedule of Expected Cash Disbursements-Operating Expenses Oct Dec. ....... Salaries and wages...... Rent Other expenses Total disbursements 2.500 3.980 Dec. Quarter 4. Complete the following cash budget Cash Budget (Cash Receipts and Disbursements) Oct Nov. Cash balance, beginning...... ........ 8,000 Add: cash collections............... 84.400 Total cash available................................ 72.400 Less: Cash disbursements................... For inventory... 53,000 For expenses... 14,380 For equipment... 7,000 Total cash disbursements............. 74 360 Expected cash balance (overdraft)... (1.980) Financing Borrowings (at beginning).... 7.000 Repayments (at end)... Interest (at 12% per year).................. Total financing Cash balance, ending............... 5,020 5. Prepare an income statement for the quarter ended December 31. (Use the functional format in preparing your income statement, as shown in the text) 6. Prepare a balance sheet as of December 31 Budget(excel) ANSWER SHEET Pocketchange Company Income Statement For the quarter ended December 31, 2020 Sales Beginning inventory......... Purchases........... Goods available Ending inventory Cost of goods sold Gross margin... Operating expenses: Salaries Rent... Depreciation Other expenses Net operating income... Less: Interest expenses......... Net income.... Pocketchange Company Balance Sheet December 31, 2017 Assets Cash (Part 4) Accounts receivable Inventory (Part 2) Total current assets Fixed assets - net of accumulated depreciation Total assets.... Liabilities and Equity Accounts payable Notes payable Interest payable Total liabilites Stockholders' equity: Capital stock Retained earnings Total liabilities and equity..... 150,000 Budget(excel)