*please only answer #5-#10

TEMPLATES to follow

INFO/assumptions

Traverse Recreation Company manufactures a single product that is popular with outdoor enthusiasts. The company sells its product to retailers throughout the midwestern section of the United States. It is in the process or creating a master budget for 2020 and reports a balance sheet at December 31, 2019 as follows The company's chief financial omcer (CFO) in consultation with various managers across the organization has developed the following set of assumptions to help create the 2020 budget 1. The budgeted unit sales are 12,000 units, 37,000 units, 15,000 units, and 25,000 units for quarters 1-4, respectively. Notice that the company experiences peak sales in the second and fourth quarters. The budgeted selling price for the year is $32 per unit. The budgeted unit sales for the first quarter of 2021 is 13,000 units 2. All sales are on Credit Uncollectible accounts are negligible and can be ignored Seventy-five percent of all credit sales are collected in the quarter of the sale and 25% are collected in the subsequent quarter 3. Each quarter's ending finished goods inventory should equal 15% of the next quarter's unit sales 4. Each unit of finished goods requires 3.5 yards of raw material that costs $3.00 per vard. Each quarter's ending raw materials inventory should equal 10% of the next quarter's production needs. The estimated ending raw materials inventory on December 31, 2020 is 5,000 yards Seventy percent of each quarter's purchases are paid for in the quarter of purchase. The remaining 30% of each quarter's purchases are paid in the following quarter 6. Direct laborers are paid $18 an hour and each unit of finished goods requires 0.25 direct labor hours to complete. Al direct labor costs are paid in the quarter incurred The budgeted variable manufacturing overhead per direct labor hour is $3.00. The quarterly fored manufacturing overhead is $150,000 including $20,000 of depreciation on equipment. The number of direct labor-hours is used as the allocation base for the budgeted plantwide overhead rate. All overhead costs (excluding depreciation) are paid in the quarter incurred 8. The budgeted variable selling and administrative expense is $1.25 per unit sold. The foxed selling and administrative expenses per quarter include advertising ($25.000), executive salanes (554 000), insurance (512,000). property tax ($8,000) and depreciation expense ($8,000). AL selling and administrative expenses (excluding depreciation) are paid in the quarter incurred 9 The company plans to maintain a minimum cash balance at the end of each quarter of $30,000 Assume that any borrowings take place on the first day of the quarter. To the extent posible, the company will repay principal and interest on any borrowings on the last day of the fourt quarter The company's lender imposes a simple interest rate of 3% per quarter on any borrowings 10 Dividends of $15,000 will be declared and paid in each quarter 11. The company uses a last-in, first-out (LIFO) Inventory now assumption. This means that the most recently purchased raw materials are the first out to production and the most recently completed finished goods are the first-out to customers Required: The company's CFO has asked you to prepare the 2020 master budget. To fulfill this request, prepare the following budget schedules and financial statements. Your leader mandates that all data be linked to only budget assumptions and related schedules. All of the schedules must be formula driven. NO HARD CELLS The schedules must be created in Excel. A template is provided in Moodle. Each schedule will be on a different tab as indicated by name. DO NOT move the tabs, reorganize the tabs, or rename the tabs Submit the file upon completion using the Moodle link Refer to Ch 8 in the textbook for formatting the schedules for each requirement. 1. Quarterly sales budget including a schedule of expected cash collections. 2. Quarterly production budget 3. Quarterly direct materials budget including a schedule of expected cash disbursements for purchases of materials. 4. Quarterly direct labor budget. 5. Quarterly manufacturing overhead budget. 6. Ending finished goods inventory budget at December 31, 2020. (LIFO inventory assumption) 7 Quarterly selling and administrative expense budget 8. Quarterly cash budget. Determine any borrowing that would be needed to maintain the minimum cash balance as indicated in your data set. (This will require the use of an ir statement in Excel) 9. Income statement for the year ended December 31, 2020, 10. Balance sheet at December 31, 2020. BEC Hampton Freeze, Inc. Manufacturing Overhead Budget For the Year Ended December 31, 2017 Quarter 23 Year 2Budgeted direct labor-hours (Schedule 4) 7,600 40.400 8 Variable manufacturing overhead rate $ 400 $ 400 $ 4.00 $ 400 $ 400 400 Variable manufacturing overhead $ 22.400 $ 51,200 $ 57,600 $ 30,400 $ 161,600 10 Fixed manufacturing overhead 60.000 60.600 60.600 60.600 242.400 11 Total manufacturing overhead 83.000 111,800 118,200 W 91,000 404,000 12 Less depreciation 15.000 15.000 15.000 15.000 60.000 13 Cash disbursements for manufacturing overhead S 68000 S 96,800 $1000 $ 76.000 S344.000 15 Total manufacturing overhead (a) $ 404000 16 Budgeted direct labor-hours (b) 40.400 17 Predetermined overhead rate for the year (a)-(6) $10.00 14 Schedule Schedule Schedule 5 Schadensee Schedule TANGH Hampton Freeze, Inc. Ending Finished Goods Inventory Budget (absorption costing basis) For the Year Ended December 31, 2017 N Quantity Cost Total $ 3.00 Item 7 Production cost per case: Direct materials Direct labor 10 Manufacturing overhead 11 Unit product cost 15.00 pounds 0.40 hours 0.40 hours $ 0.20 per pound $ 15.00 per hour $10.00 per hour 6.00 4.00 13.00 $ 3,000 13 Budgeted finished goods inventory 14 Ending finished goods inventory in cases (Schedule 2) 15 Unit product cost (see above) 16 Ending finished goods inventory in dollars Schedule 3 Schedule 4% Schedule 5 Schedule 6 Sc 13.00 $ 39.000 2 Hampton Freeze, Inc. Selling and Administrative Expense Budget For the Year Ended December 31, 2017 For the y 3 4 40.000 20.000 S 180 S 1.80 $72.000 $ 30,000 Year 100.000 $ 1.80 $180,000 1 2 z Budgeted units sales (Schedule 1) 10,000 30,000 Variable seling and administrative expense per case $ 180 S 180 9 Variable seling and administrative expense $ 18,000 $ 54000 10 Fored selling and administrative expenses SA 11 Advertising 2 0,000 20,000 12 Executive salaries 55000 55,000 1) Insurance S 1 0,000 10,000 14 Property taxes 4 ,000 4,000 15 Depreciation 10.000 10000 16 Total fixed selling and administrative expenses 99.000 99.000 17. Total selling and administrative expenses 117.000 153.000 10 Less depreciation _10.000 10.000 19 Cash disbursements for selling and administrative expenses $107.000 $143.000 Schedule Schedule Schedule Schedule Schee Schedule 10 20,000 55.000 10,000 4,000 10000 99.000 171.000 _10.000 $161.000 20,000 55,000 10,000 4,000 10000 99.000 135,000 10.000 $125.000 80.000 220,000 40,000 16,000 40000 396.000 570,000 40.000 $536.000 . CDEF Hampton Freeze, Inc. Cash Budget For the Year Ended December 31, 20 Schedule Quarter 1 $ 42,500 $ 35,000 $, 33,900 230.000 480.000 740.000 272,500 516.000 773.900 $ 165,650 Year $42,500 520.000 685650 1970.000 2012 500 7 Beginning cash balance . Add cash receipts 9 Collections from customers 10 Total cash available 11 Less cash disbursements: 12 Direct materials 13 Direct labor 14 Manufacturing overhead 15 Selling and administrative 16 Equipment purchases 17 Dividends 10 Total cash disbursements Excess (deficiency) of cash available over disbursements 20 Financing 21 Borrowings (at the beginnings of quarters) 22 Repayments (at end of the year) 23 Interest 24 Total financing 25 Ending cash balance 49,500 84,000 68.000 107000 50.000 8.000 386,500 (04000) 72,300 192.000 96.800 143,000 40.000 3.000 552 100 00100) 100,050 216,000 103 200 161 000 20 000 8.000 608 250 165,650 79 350 114000 114.000 78,000 125.000 20.000 8.000 422 350 263.300 301,200 606,000 344 000 536,000 130.000 32,000 1949 200 63.300 130 000 70.000 (200.000) (21.900) 130 000 70.000 1221 900) $ 35.000 - $ 33.900 - $ 165,650 $ 41.400 200.000 (200,000) (21.900) 21.900) $ 41,400 schedy 6. Schedule Schedule Schedule Schedule 102 The Traverse Recreation Company's balance sheet as of December 31, 2019 is given below: Assets Current Assets: Cash Accounts Receivable (net Raw Materials Inventory (4,500 yards) Finished Goods Inventory (1,500 units) Total current assets 46,200 260,000 11,250 32,250 $ 349,700 Property and Equipment: Buildings and Equipment Accumulated Depreciation Plant and Equipment (net) Total Assets 900,000 (292,000) 608,000 $957,700 $ 158,000 S Liabilities and Stockholder's Equity 7. Current Liabilities: 3 Accounts Payable 9 Stockholder's Equity 0 Common Stock 21 Retained Earnings 22 Total Stockholder's Equity Total Liabilities and Stockholder's Equity 419,800 379,900 799.700 $957.700 Quarters 37,000 15,000 12.000 $32.00 25,000 Sales budget Budegeted Sales in units Selling Price per unit Percentage of sales collected in the quarter of sale Percentage of sales collected in the quarter after sale 3.5 yards $3 70% 30% 0.25 $18.00 1 Production Budget 2 Percentage of next quarters sales in ending finished good iventor 3 4 Diect Materials Budget 5 Yards of Raw Material per unit 6 Cost per Yard 18 Percentage of purhcases paid in the quarter purchased 19 Percentage of purhoases paid in the quarter after purchased 20 21 Direct Labor Budget 22 Direct labor hours required per unit 23 Direct labor cost per hour 24 25 Manufacturing Over Head Budget 26 Vatible Manufacturing Overhead per direct labor hour 27 Fixed Manufacturing Overhead per quarter 28 Deprecation per quarter 29 30 Selling and Admin Expense budget 31 Varible Selling and Admin expense per unit 32 Fixed Selling and Admin expense per quarter 33 Advertising 34 Executive Salaries 35 Insurance 36 Property Tax 37 Deprecation $3.00 150,000 $20,000 $1.25 $25,000 $64,000 $12,000 $8.000 $8,000 38 $30,000 39 Cash Budget 40 Min cash balance 41 Equipment purchases 42 Dividens 43. Simple interest rate per 15,000