Please do 132 and after, thank you. I need this asap

qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx Chapter 7: Applying Excel Year End December 2015 Master Budget Given Data - Assumptions Quarter 1 15,000 Budgeted sales in cases Sales Budget Selling price per case Sales collected in the quarter sales are made Sales collected in the quarter after sales are made 2 20,000 3 35,000 $20 per case 70% 30% Production Budget Desired ending finished goods inventory at quarter end is Q4 desired units in ending finished goods inventory is assumed to be 20% of the budgeted unit sales of the n 3,000 Direct Materials Budget Raw materials required to produce one case Cost per pound of raw material Percentage of net quarter's production needs in ending inventory Q4 production needs in ending inventory is assumed to be Percentage of purchases paid in the quarter purchased Percentage of purchases paid in the quarter after purchased 15 pounds 0.20 10% 22,500 50% 50% Enter a formula into each of the cells coded grey. Link as much to the Data/Assumptions Box as Possible. Prepare the Sales Budget-Schedule 1 Quarter Budgeted case sales Selling price per case Total sales $ $ 1 15,000 20 $ 300,000 $ 2 3 20,000 35,000 20 $ 20 400,000 $ 700,000 Prepare the Schedule of Expected Cash Collections Quarter Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Fourth-quarter sales Total cash collections $ $ 1 90,000 210,000 $ 300,000 $ Prepare the Production Budget-Schedule 2 2 3 90,000 280,000 $ 120,000 490,000 370,000 $ 610,000 Quarter 1 15,000 4,000 Total number of budgeted case sales (Schedule 1) Add desired finished goods inventory at quarter end Page 1 2 20,000 7,000 3 35,000 8,000 qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx Total needs Less beginning inventory (# of cases) Required production in cases 19,000 2,000 17,000 Prepare the Raw (direct) Materials Purchases Budget Schedule 3 Required production - number of cases - Schedule 2 Raw materials in lbs. required to produce one case Total production needs (pounds) Add desired ending inventory of raw materials (pounds) Total needs (pounds) Less beginning inventory of raw materials (pounds) Raw materials to be purchased (pounds) Cost of raw materials per pound Total cost of raw materials to be purchased 27,000 4,000 23,000 43,000 7,000 36,000 Quarter 1 17,000 15 255,000 34,500 289,500 21,000 268,500 $0.20 $53,700 2 23,000 15 345,000 54,000 399,000 34,500 364,500 $0.20 $72,900 Prepare the Schedule of Expected Cash Payments 3 36,000 15 540,000 52,500 592,500 54,000 538,500 $0.20 $107,700 Quarter Accounts payable, beginning balance Cash paid in the first-quarter for purchases Cash paid in the second-quarter for purchases Cash paid in the third-quarter for purchases Cash paid in the fourth-quarter for purchases Total cash disbursements $ 1 25,800 26,850 $ $ 52,650 $ $ $ 1 17,000 0.40 $ 6,800 15.00 $ 102,000 $ Prepare the Direct Labor Budget-Schedule 4 Required production in cases - Schedule 2 Direct labor-hours per case Total direct labor-hours needed Direct labor cost per hour Total direct labor cost 2 3 26,850 36,450 $ 63,300 $ 36,450 53,850 90,300 Quarter 2 3 23,000 36,000 0.40 $ 0.40 9,200.0 14,400 15.00 $ 15.00 138,000 $ 216,000 Prepare a Manufacturing Overhead Budget-Schedule 5 Quarter Budgeted direct labor-hours (Schedule 4) Variable manufacturing overhead rate per hour Total variable manufacturing overhead Fixed manufacturing overhead Total manufacturing overhead Less depreciation - non cash expense Cash disbursements to manufacturing overhead $ $ $ $ $ $ Total manufacturing overhead (a) Budgeted direct labor-hours (b) Predetermined overhead rate for the year (a) / (b) Page 2 1 6,800 4.54 30,872 60,600 91,472 15,000 76,472 2 $ $ $ $ $ 9,200 4.54 41,768 60,600 102,368 15,000 87,368 $ $ $ $ $ 3 14,400 4.54 65,376 60,600 125,976 15,000 110,976 qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx Prepare an Ending Finished Goods Inventory Budget-Schedule 6 Item Production cost per case Direct materials Direct labor Manufacturing overhead Unit product cost Quantity Cost 15.00 pounds 0.40 hours 0.40 hours 0.20 15.00 10.00 Budgeted finished goods inventory: Ending finished goods inventory in cases (Schedule 2) Unit product cost (see above) Ending finished goods inventory in dollars Prepare a Selling and Administrative Budget-Schedule 7 Quarter Budgeted number of case sales (Schedule 1) Variable selling and administrative expense per case Variable selling and administrative expense Fixed selling and administrative expenses: Advertising Executive salaries Insurance Property taxes Depreciation Total fixed selling and administrative expenses Total selling and administrative expenses Less depreciation Cash disbursements for selling and administrative expenses $ $ 1 15,000 1.80 $ 27,000 $ $ 18,000 40,000 9,500 3,500 6,000 77,000 104,000 6,000 98,000 $ 2 20,000 1.80 $ 36,000 $ 3 35,000 1.80 63,000 18,000 18,000 40,000 40,000 9,500 9,500 3,500 3,500 6,000 6,000 77,000 77,000 113,000 140,000 6,000 6,000 107,000 $ 134,000 Prepare a Cash Budget-Schedule 8 Quarter Beginning cash balance Add cash receipts: Collections from customers - from Schedule 1 Total cash available Less cash disbursements: Direct materials - from Schedule 3 Direct labor - from Schedule 4 Manufacturing overhead - from Schedule 5 Selling and administrative- from Schedule 7 Equipment purchases Dividends Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings (at the beginning of quarters) $ 1 42,500 3 300,000 342,500 370,000 370,000 610,000 610,000 53,700 102,000 91,472 104,000 50,000 8,000 409,172 (44,822) 72,900 138,000 102,368 113,000 30,000 8,000 464,268 (38,290) 107,700 216,000 125,976 140,000 20,000 8,000 617,676 364,350 Page 3 2 qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx Repayments (at end of the year) Interest expense (will calculate for you) Total financing Ending cash balance (also next Q beginning balance) Prepare a Budgeted Income Statement -Schedule 9 Sales - from Schedule 1 Cost of goods sold - from Schedules 1 & 6 Gross margin Selling and administrative expenses - from Schedule 7 Net operating income Interest expense - from Schedule 8 Net income Prepare a Budgeted Balance Sheet -Schedule 10 Assets Current assets: Cash - from Schedule 8 Accounts receivable - from Schedule 1 Raw materials inventory- from Schedule 3 Finished goods inventory - from Schedule 6 Total current assets Plant, property, and equipment: Land Buildings and equipment (BS + Schedule 8) Accumulated depreciation (BS + Sched 5 + Sched 7) Property, plant, and equipment, net Total assets Liabilities and Stockholders'' Equity Current liabilities: Accounts payable (raw materials) - from Schedule 3 Stockholders' equity: Common stock, no par (PY Balance sheet) Retained earnings (PY balance sheet, CY income statement, Schedule 8) Total stockholders' equity Total liabilities and stockholders' equity Page 4 (376,000) will calculate for you; will include p qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx 4 40,000 les of the next quarter Year 4 1 40,000 110,000 $ 20 $ 20 $ 800,000 $ 2,200,000 4 Year $ 90,000 300,000 400,000 $ 210,000 700,000 560,000 560,000 $ 770,000 $ 2,050,000 4 40,000 3,000 Year 110,000 3,000 Page 5 qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx 43,000 8,000 35,000 113,000 2,000 111,000 4 35,000 15 525,000 22,500 547,500 52,500 495,000 $0.20 $99,000 Year 111,000 15 1,665,000 22,500 1,687,500 21,000 1,666,500 $0.20 $333,300 4 $ 53,850 49,500 $ 103,350 $ Year 25,800 53,700 72,900 107,700 49,500 309,600 4 35,000 $ 0.40 14,000 $ 15.00 $ $ 210,000 $ Given: Year Each case requires 111,000 Each worker is paid 0.40 44,400 15.00 666,000 $ $ $ $ $ $ $ 4 14,000 4.54 63,560 60,600 124,160 15,000 109,160 $ $ $ $ $ $ Year 44,400 (b) 4.54 201,576 242,400 443,976 (a) 60,000 383,976 $ 0.40 DLH 15.00 per hour Given: Variable manufacturing overhead rate Fixed manufacturing overhead is Depreciation 443,976 44,400 10.00 Page 6 $ 4.54 DLH $ 60,600 per Q $ 15,000 per Q qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx Cost Total per pound per hour per hour $ $ 3.00 6.00 4.00 13.00 $ $ 3,000 13.00 38,999 4 40,000 $ 1.80 $ $ 72,000 $ Year 110,000 1.80 198,000 18,000 40,000 9,500 3,500 6,000 77,000 149,000 6,000 $ 143,000 $ 72,000 160,000 38,000 14,000 24,000 308,000 506,000 24,000 482,000 4 Year 770,000 770,000 2,050,000 2,050,000 99,000 210,000 124,160 149,000 20,000 8,000 610,160 333,300 666,000 443,976 506,000 120,000 32,000 Given: Variable selling and administrative expense per case Fixed selling and administrative: Advertising Executive salaries Depreciation Insurance Property taxes $ 1.80 $ 18,000 per Q $ 40,000 per Q $ 6,000 per Q $ 9,500 per Q $ 3,500 per Q Given: A minimum of $20,000 cash is required in the bank The company may borrow any amount at the beginning of any quarter in increments of $10,000 Annual simple interest rate 5% Loans must be repaid at year end including all interest incurred Dividends paid $ 8,000 Q1-Q4 Equipment purchases $ 50,000 Q1 $ 30,000 Q2 $ 20,000 Q3 $ 20,000 Q4 Page 7 qattachments_d0df0936046a932e12c2805797ae71390fa1abab.xlsx (18,218) ill include prior year accumulated depreciation ending balance per the balance sheet plus current year depreciation expense Page 8 Hampton Freeze, Inc. Balance Sheet December 31, 2014 Assets Current assets: Cash Accounts receivable Raw materials inventory (21,000 lbs.) Finished goods inventory (2,000 cases) Total current assets Plant, property, and equipment: Land Buildings and equipment Accumulated depreciation Property, plant, and equipment, net Total assets Liabilities and Stockholders' Equity Current liabilities: Accounts payable Stockholders' equity: Common stock, no par Retained earnings Total stockholders' equity Total liabilities and stockholders' equity $ 42,500 90,000 4,200 26,000 162,700 $ 80,000 700,000 (292,000) 488,000 650,700 $ 25,800 $ 175,000 449,900 624,900 650,700 Construct the sales budget-Schedule 1 Total sales $ 300,000 $ 400,000 $ 700,000 Construct the schedule of expected cash collections Total cash collections $ 300,000 $ 370,000 $ 610,000 Construct the production budget-Schedule 2 Required production in cases $ 17,000 $ 23,000 $ 36,000 Construct the raw (direct) materials purchases budget Schedule 3 Cost of raw materials to be purchased $53,700 $72,900 $107,700 Construct the schedule of expected cash payments Total cash disbursements $ 52,650 $ 63,300 $ 90,300 Construct the direct labor budget-Schedule 4 Total direct labor cost $ 102,000 $ 138,000 $ 216,000 Construct a manufacturing overhead budget-Schedule 5 Cash disbursements to manufacturing overhead $ 76,472 $ 87,368 $ 110,976 Predetermined overhead rate for the year (a) / (b) Construct ending finished goods inventory budget-Schedule 6 Unit product cost Ending finished goods inventory in dollars Construct a selling and administrative budget-Schedule 7 Variable selling and administrative expense $ 27,000 $ 36,000 $ 63,000 Total fixed selling and administrative expenses $ 77,000 $ 77,000 $ 77,000 Cash disbursements for selling and administrative expenses $ 98,000 $ 107,000 $ 134,000 Prepare a cash budget-Schedule 8 Total cash available $ 342,500 $ 395,378 $ 631,710 Total cash disbursements Excess (deficiency) of cash available over disbursements $ 387,122 $ 433,668 $ 579,276 $ (44,622) $ (38,290) $ 52,434 Ending cash balance (also next Q beginning balance) $ 23,378 $ 21,710 $ 52,434 Prepare a budgeted income statement -Schedule 9 Net income $ 258,274 Prepare a budgeted balance sheet -Schedule 10 Total assets $ 900,673 Total liabilities and stockholders' equity $ 900,674 difference due to rounding $ 800,000 $ 2,200,000 $ 770,000 $ 2,050,000 $ 35,000 $ 111,000 $99,000 $333,300 $ 103,350 $ 309,600 $ 210,000 $ 666,000 $ 109,160 $ 383,976 $ 10 $ 13 $ 38,999 $ 72,000 $ 198,000 $ 77,000 $ 308,000 $ 143,000 $ 482,000 $ 822,434 $ 2,092,500 $ 593,510 $ 1,993,576 $ 228,924 $ 98,924 $ 93,174 $ ue to rounding 93,174