Looking for cell references and formulas

Master Budget Based on the following information, prepare the Master Budget for Brindle Arts,Inc. for Quarter 4, 2020 Beginning of quarter balances: Cash 150,000 Accounts Receivable 260,000 Finished Goods Inventory (pieces of art) Raw Materials inventory (kits) 610 Buildings and equipment 350,000 Accounts Payable 50,294 Common Stock 150,000 Retained Earnings 52,431 Actual and budgeted sales data (pieces of art): Sept. (act.) October November December January February 3,250 3,000 3,500 4,500 2,100 3,30 Artwork is sold for $200 each. Selling price per piece of artwork $ 200 Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at the beginning of the quarter are a result of September's credit sales. Sales: Cash 50% Credit 40% The finished goods inventory on hand at the end of each month must equal 10% of the next month's sales. Finished Goods inventory 10% of next month's sales The raw materials inventory on hand at the end of each month must equal 10% of the following month's production needs for raw materials. Raw materials inventory : 10% of next month's production needs Rawm materials needed per unit of artwork 2 finishing kits Raw materials cost: | $ 32.50 per finishing kit 75% of each raw materials purchases is paid for in the month of purchase; the other 25% is paid in the following month. The accounts payable at the beginning of the quarter are the result of September raw materials purchases. Purchases: Month of 75% Month after 25% Manufacturing and Selling and Administrative costs:Manufacturing and Selling and Administrative costs: Variable cost per unit Fixed costs per month: Direct materials $ 65.00 Manufacturing OH $ 170,000 Direct labor $ 30.00 Depreciation - Manuf. S 180,000 Var. MOH 15.00 Selling and Admin. 125,000 Var. S&A $ 10.00 Depreciation - S&A S 75,000 Office equipment costing $5,000 will be purchased for cash in October and new equipment will be purchased in December for $8,000. Office equipment | $ 5,000 in October Equipment S 8,000 in December purchase Management would like to maintain a minimum cash balance of at least $50,000 at the end of each month. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter. Minimum cash on hand | $ 50,000 Borrow in| $ 1,000 increments Interest (no compounding) 1% Use the data above, complete the following statements and schedules for the Quarter 4, 2020: Schedule of expected cash collections: October November December Quarter Cash Sales Credit sales Bonus (up to 2pts) Total cash collected Accounts Receivable at the end of the quarter Production budget October November December January Budgeted unit sales Add desired ending finished goods inventory Total needs Less beginning finished goods inventory Required production in unitsDirect materials budget October November December Quarter January Required production in units of finished goods (gift baskets) Units of raw materials needed to meet production (bags of snack mix) Add desired units of ending raw materials inventory bags of snack mix) Total units of raw materials needed bags of snack mix) ess units of beginning raw materials inventory (bags of snack mix) Units of raw materials to be purchased (bags of snack mix) Cost of raw materials to be purchased bags of snack mix) Schedule of expected cash disbursements for inventory purchases: October November December Quarter September purchases October purchases Cash Disbursed November purchases Bonus (up to 2pts) December purchases Total cash disbursements for purchases Direct labor budget October November December Quarter Required production, in units otal direct labor costCash budget October November December Quarter Beginning Cash balance Add cash collections Total cash available ess total cash disbursements: Purchases Direct labor Variable manufacturing OH Fixed manufacturing OH Variable S&A Fixed S&A Equipment Total cash disbursements Cash available before financing Financing: Borrowings (at beginnings of quarters) Repayments nterest Total Financing Ending Cash balancePrepare an absorption costing income statement for the Prepare a balance sheet as of December 31. Bonus (up to 3pts) quarter ending December 31. Brindle Arts, Inc. Brindle Arts, Inc. Income Statement Balance Sheet For Quarter Ended December 31 As of December 31 Sales revenue Assets Less variable costs: Current Assets: Cost of goods sold Cash Selling and admin. Accounts Receivable Total variable costs Inventory Contribution margin Total Current Assets Buildings and equipment - net Less fixed costs: Total Assets Manufacturing overhead Selling and admin. Liabilities and Stockholders' Equity Total fixed costs Current Liabilities: Operating income Accounts Payable Less interest expense Stockholders' equity: Net income (loss) Common Stock Retained Earnings Total Liabilities and Stockholders' Equity