Please answer E, F, G, and H if possible. And show it as an excel sheet with formulas used (shown). Thank you!
Check figures are: Total sales for the quarter - $340,000 Total purchases for the quarter - $128,960 Total S & A expenses for the quarter - $157,200 Total cash receipts for the quarter - $351,000 Total cash disbursements on inventory purchases for the quarter - $133,464 Ending cash balance for the quarter - $7,636 Budgeted income for the quarter - $18,900 Total Assets at the end of the quarter - $172,396
These are the answers for A, B, C, D that will help answer the E, F, G, H.
Delbert Corporation prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for the first quarter of 2019: a. As of December 31, 2018 (the end of the prior quarter), the company's general ledger showed the following account balances: Credits Debits $10,000 60,000 40,000 90,000 Cash Accounts Receivable Inventory Plant and Equip (net) Accounts Payable Short-term Notes Payable Capital Stock Retained earnings $25,000 15,000 115.000 45,000 $200.000 $200.000 b. Actual sales for December and budgeted sales for the next four months are as follows: December, 2018 January, 2019 February, 2019 March, 2019 April, 2019 $80,000 110,000 160.000 _70,000 _40,000 c. Sales are 30 percent for cash and the rest on account. All sales on account are collected the month following sale. The accounts receivable on December 31 are a result of December credit sales. d. The company's gross profit rate is 52 percent of sales. e. Monthly expenses are budgeted as follows: salaries and wages, $9,000 per month; rent, $20,000 per month; utilities, 1 percent of sales; depreciation, $5,000 per month; other expense, 2 percent of sales; and advertising, $15,000 per month. f. At the end of each month, inventory is to be on hand equal to 30 percent of the following month's sales needs, stated at cost. g. Thirty percent of a month's inventory purchases are paid for in the month of purchase; the rest is paid for in the following month. h. During January, the company will purchase a new computer for $15,000 in cash. During February, other equipment will be purchased for cash at a cost of $20,000. Assume there will be no equipment purchases in March 2019. i. During February and March, the company will declare and pay $15,000 in cash dividends per month. Assume no dividends will be paid in January. j. The company must maintain a minimum cash balance of $7,500. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. All borrowing is done at the beginning of a month, and all repayments are made at the end. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid at the end of each month. The interest rate is 12 percent per annum. (Figure interest in whole months, e.g., 1/12, 2/12.) Required: Prepare an interactive budgeting spreadsheet. It should automatically update when changes are made to the input data, such as changes in sales forecasts, equipment purchases, etc. e. Spreadsheets Hints 1. Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs." 2. Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included: a. Sales Budget b. Inventory Purchases Budget c. Selling and Administrative Budget d. Cash Collections from Customers Schedule Cash Paid for Inventory Purchases Schedule f. Cash Budget g. Budgeted Income Statement h. Budgeted Balance Sheet 3. Each budget should: a. be on a separate worksheet b. have a heading centered over the rest of the budget that includes the following: i. Name of Company ii. Name of Budget iii. Date: _"March 31, 2019 or For the Quarter ended March 31, 2019" be prepared on the monthly basis with a total column for the quarter. The budgeted income statement and budgeted balance sheet should be quarterly (not monthly). 4. All worksheets should be interactive (i.e. all worksheet pages except the Input worksheet, should be formula-driven) c. 1 Sales Budget January $110,000 Total Sales February $160,000 March Quarter $70,000 $340,000 April $40,000 2 Inventory purchase budget Budgeted Cost of goods sold Add: Desired ending inventory Total Needs Less Beginning inventory Budgeted purchase January $52,800 $23,040 $75,840 $40,000 $35,840 February $76,800 $10,080 $86,880 $23,040 $63,840 March Quarter $33,600 $163,200 $5,760 $5,760 $39,360 $168,960 $10,080 $40,000 $29,280 $128,960 3 Selling and administrative expenses Salaries and wages Rent expense Utilities expense - 1% of sales Depreciation Other expense - 2% of sales Advertising Budgeted selling and administrative expenses January $9,000 $20,000 $1,100 $5,000 $2,200 $15,000 $52,300 February $9,000 $20,000 $1,600 $5,000 $3,200 $15,000 $53,800 March Quarter $9,000 $27,000 $20,000 $60,000 $700 $3,400 $5,000 $15,000 $1,400 $6,800 $15,000 $45,000 $51,100 $157,200 4 Schedule of expected Cash collections Cash sales - 30% Credit sales* January $33,000 $60,000 $93,000 February $48,000 $77,000 $125,000 March Quarter $21,000 $102,000 $112,000 $249,000 $133,000 $351,000 Total Collections
