A c On the tab labeled SOLUTION 1, use all available data (Assumptions Row 4-Row 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: Create a SALES BUDGET for the year ended December 31, 2017 Create a SCHEDULE OF EXPECTED CASH COLLECTIONS for the year ended December 31, 2017 On the tab labeled SOLUTION 2, use all available data (Assumptions Row 4-Row 52, Balance sheet for 2016, results from other SOLUTION tabs) to: Create a PRODUCTION BUDGET for the year ended December 31, 2017 On the tab labeled SOLUTION 3, use all available data (Assumptions Row 4- Row 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: D Create a DIRECT MATERIALS BUDGET for the year ended December 31, 2017 E Create a SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR PURCHASES OF MATERIALS for the year ended December 31, 2017 On the tab labeled SOLUTION 4, use all available data (Assumptions Row 4-Row 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: F Create a DIRECT LABOR BUDGET for the year ended December 31, 2017 On the tab labeled SOLUTION 5, use all available data (Assumptions Row 4-Row 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: Create a MANUFACTURING OVERHEAD BUDGET for the year ended December 31, 2017 on the tab labeled SOLUTION 6, use all available data (Assumptions Row 4. How 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: Create a ENDING FINISHED GOODS INVENTORY BUDGET (absorption costing basis) for the year ended December 31, 2017 On the tab labeled SOLUTION 7, use all available data (Assumptions Row 4 Row 12, Balance Sheet for 2016, results from other SOLUTION tabs) to: Crente a SELLING AND ADMINISTRATIVE EXPENSE BUDGET for the year ended December 31, 2017 On the tab labeled SOLUTION 8, use all available data (Assumptions Row 4-Row 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: Create a CASH BUDGET for the year ended December 31, 2017 On the tab labeled SOLUTION 9, use all available data (Assumptions Row Row S2, Balance Sheet for 2016, results from other SOLUTION tabs) to: K Create a BUDGETED INCOME STATEMENT (absorption costing basis for the year ended December 31, 2017 On the tab labeled SOLUTION 10, use all available data (Assumptions Row 4-Row 52, Balance Sheet for 2016, results from other SOLUTION tabs) to: Create a BUDGETED BALANCE SHEET as of December 31, 2017 1 1 L Assumptions Endless Mountain Company manufactures a single product that is popular with outdor recreation enthusiasts. The company sells its product to retailers throuchout the northeastern quadrant of the United States. It is in the process of creating a master budet for 2017. The company's CFO in consultation with various managers across the company is providing the latest Balance Sheet and a set of assumptions to help create the 2017 bolets. Budgeted Unitsales: Q1 2017 02 2017 03 2017 04 2017 01 2018 12.000 17.000 15,000 25,000 1000 305 1214 5 32 Budgeted Selling Price per unit All sales are credit sales Ignore Uncollectible Accounts. They are minimal Percentage of Sales collected in the quarter of the sale Percentage of Sales collected in the subsequent quarter 759 23 5 Quarter ending finished goods inventory as a percent of next quarter's unit sales Yards of raw material required for each unit Cost per sed of raw material Quarter ending raw materials inventory as a percent of net unter's production needs Estimated ending raw materials inventory on December 31, 2017 in yards Percent of purchases paid for in the quarter of the purchase Percent of purchases paid for in the quarter following the purchase 15% 3.5 100 10% 5.000 TON SON $ 11.00 25 Direct labor hourly rate Directibor hours required to finish each unit of finished goods Dived into bouts are paid in the quarter incurred 5 SOLUTION 100 SOLUTION Budited variable manufacturing oves per director hour DIRECTIONS Assumptions Row 4 Row 52 SOLUTION SOLUTION 1 SOLUTIONS alance Sheet for 2016 3.00 10% 5,000 70% 30% s 18.00 0.25 S S S 3.00 150,000 20,000 1.25 Cost per yard of raw material S Quarter ending raw materials inventory as a percent of next quarter's production needs Estimated ending raw materials inventory on December 31, 2017 (in yards) Percent of purchases paid for in the quarter of the purchase Percent of purchases paid for in the quarter following the purchase Direct labor hourly rate Direct labor hours required to finish each unit of finished goods Direct labor hours are paid in the quarter incurred Budgeted variable manufacturing overhead per direct labor hour Quarterly fixed manufacturing overhead (including depreciation expense on equipment) Quarterly 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 quarts incurred The budgeted variable selling and administrative expense per unit sold S Fixed selling and administrative expenses: Advertising Executive Salaries Insurance Property tax Depreciation expense (selling and administrative) All selling and administrative expenses (excluding depreciation) are paid in the quarter incurred Minimum cash balance at the end of each quarter S Any borrowing takes place on the first day of the quarter To the extent possible, the company will repay principal and interest on any borrowings on the last day of the fourth quarter The company's lender imposes simple interest per quarter on any borrowing at a rate of Dividends declared and paid in each quarter 5 The company uses a LIFO inventory flow 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. DIRECTIONS Assumptions Row 4 - Row 52 Balance Sheet for 2016 SOLUTION 1 SOLUTION 2 dy Scroll Lock USU 25,000 64,000 12,000 8,000 8,000 30,000 396 15,000 SOLUTI Type here to search o Balance Sheet December 31, 2016 Assets $ 46,200 260,000 11,250 32,250 S Current Assets: Cash Accounts Receivable Raw Materials Inventory (4,500 yards) Finished Goods Inventory (1,500 Units) Total Current Assets Plant and Equipment: Buildings and Equipment Accumulated Depreciation Plant and Equipment, net Total Assets 349,700 900,000 (292,000) 608,000 957,700.00 $ S 158,000 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable Stockholders's Equity. Common Stock Retained Earnings Total Stockholders Equity Total Liabilities and Stockholders' Equity 419,800 379,900 799,700 957,700.00 2 2 3 4 5 6 7 8 29 30 31 32 33 34 35 DIRECTIONS Assumptions Row 4Row 52 Balance Sheet for 2016 SOLUTIO