Help please

B ACG 3341 Excel Project Master Budget The yellow cells are "data input" cells in which facts, assumptions or estimates are to be entered. The blue cells are "relationship" cells in which formulas are to be entered. The green cell are facts, assumptions or estimates from "data input" cells. SS OF Osumates from a input" cells Data input: Projected Balance Sheet as of December 31, 20x0 Cash Accounts receivable Marketable securities Inventory Buildings and equipment (net of depreciation) Total assets $ 28,000 276,000 16,000 154,000 628,000 $ 1.100.000 Projected sales for December Credit sales percent Percent collected during month of sale $ 400,000 75% 10% Cost of goods sold percent of sales Percent of purchases paid during month of purchase Ending inventory as percent of next month's Proj. COGS 70% 40% 5096 Accounts payable Bond interest payable Property taxes payable Bonds payable (10%, due in 20x6) Common stock Retained earnings Total liabilities and stockholders' equity $ 176,400 12,500 3,600 300,000 500,000 107 500 $ 1.100.000 Other monthly expenses Sales salaries Advertising and promotion Administrative salaries Depreciation Interest on bonds Property taxes Sales commission percent of sales $ 21,000 16,000 21,000 25,000 2.500 900 196 1 Expected monthly increase in sales (1st quarter) 10% Proposed equipment investment Minimum cash balance Minimum time for short term loan (months) Projected short-term interest rate $ 125,000 $ 19,000 3 10% Proposed dividend payment $50,000 a Sales budget January February March First Quarter Cash sales Cash collections from credit sales (current month) Cash collections from credit sales (previous month) Total cash receipts c. Purchases budget: December January February March First Quarter Budgeted cost of goods sold Add Desired ending inventory Total goods needed Less Expected beginning inventory Purchases 528,374 d. Cash disbursements budget 1 January First Quarter February March Inventory purchases Cash payments for purchases (current month) Cash payments for purchases (previous month) Total cash payments for inventory purchases $ 3 1 2 3 4 5 16 27 78 79 30 Other expenses Sales salaries Advertising and promotion Administrative salaries Interest on bonds Property taxes Sales commissions Total cash payments for other expenses 21,000 $ 16,000 21.000 21,000 $ 16,000 21,000 21,000 18.000 21.000 Total cash payments for other expenses Total cash disbursements e. Summary cash budget: January February March First Quarter Cash receipts Cash disbursements Change in cash balance during period due to operations Sale of marketable Securities Proceeds from bank loan Purchase of equipment Repayment of bank loan Interest on bank loan Payment of dividends Change in cash balance during first quarter Cash balance, 1/1/X1 Cash balance 3/31/X1 f. Analysis of short-term financing needs: Projected cash balance as of December 31, 20x0 Less: Minimum cash balance Cash available for equipment purchases Projected proceeds from sale of marketable securities Cash available Loss Cost of investment in equipment Required short-term borrowing 9 Universal Electronics Budgeted Income Statement for the First Quarter of 20X1 9 Universal Electronics Budgeted Income Statement for the First Quarter of 20x1 Sales revenue Less: Cost of goods sold Gross margin Selling and administrative expenses Sales salaries Sales commissions Advertising and promotion Administrative salaries Depreciation Interest on bonds Interest on short-term bank loan Property taxes Total selling and administrative expenses Net income h Universal Electronics Budgeted Statement of Retained Earnings for the First Quarter of 20x1 1 2 3 14 35 30 37 Retained earnings, 12/31/40 Add: Not income Deduct Dividends Retained earnings 3/31/1 Universal Electronics Balance Sheet Nel come 27 h. Universal Electronics Budgeted Statement of Retained Earnings for the First Quarter of 20x1 Retained earnings, 12/31/XO Add: Net income Deduct: Dividends Retained earnings, 3/31/X1 1. Universal Electronics Balance Sheet March 31, 20x1 28 29 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 Cash Accounts receiveable Inventory Buildings and equipment (net of accumulated depreciation) Total assets Accounts payable Bond interest payable Property taxes payable Bonds payable Common stock Retained earnings Total liabilities and stockholders' equity