7 The facts for this problem are presented on the Facts tab of this workt Instructions - Your solutions should be clearly labeled on the Solutions tab of this workbook 10 For the first quarter of 2020, do the following 11 (a) Prepare a sales budget. This is similar to Wustration 223 of your textbook 12 (b) Prepare a production budget. This is similar to illustration 22-5 of your textbook. 13 (c) Prepare a direct materials budget. (Round to nearest dollar) This is similar to Mustration 22-9 of your textbook 14 (d) Prepare a direct labor budget. (For calculations, round to the nearest hour. This is similar to llustration 22.11 of your textbook. 15 (e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar.) This is similar to Mustration 22-12 of your textbook 16 () Prepare a selling and administrative budget. This is similar to Mustration 22.13 of your textbook 17 (8) Prepare a budgeted income statement (Round intermediate calculations to the nearest dollar) This is similar to lustration 22-15 of your textbook 1B (h) Prepare a cash budget. This is similar to Mustration 22- 19 of your textbook. 19 You will need to prepare schedules for expected collections from customers and expected payments to vendors first. See Mustrations 22-17 and 22-18 of your textbook for guidance.) 21 Rules: 22 Use Excel's functionalty to your benefit. Points are lost for lack of formula 23 *Use proper formats for schedules, following the referenced textbook examples 24 Use dollar signs and underscores where appropriate 25 Double-check your workl Verify your formula and logic OOOOO 105 Sales Noy 114000 125.000 100 000 120,000 4 103,000 5 113,000 6 98,000 7 108,000 93,000 9 102.000 Sales Dec 102.000 112.000 97.000 107 000 92,000 101,000 87,000 97,000 83.000 91.000 Sales Jan S ales Feb 112.000 113,000 123.000 124.000 100.000 107 000 118000 119.000 101.000 102.000 111,000 112.000 96.000 97,000 106,000 107,000 91,000 92.000 100.000 101.000 Sales Mar 116,000 128.000 110.000 1 22.000 104,000 114.000 99,000 109,000 94,000 103,000 Sales Aer 125.000 138.000 119.000 131000 113,000 124.000 107,000 119.000 102.000 112,000 10 11 12 13 14 15 Sales May Unit Sell Price AR Collects AR Collect DMU/UN OM Cost/AP Disbs 1 AP Disbs 2D 137.500 5 15% 200 $ 0.75 50% 151,000 $ 2.00 $ 1.00 131 000 $ 200 $ 100 144 000 $ 200 $ 100 1240005 200 $ 1.00 136.000 2.00 $ 100 118,000 $ 200 $ 100 45N 130.000 $ 25% 2.00 $ 1.00 112.000 $ 1056 2.00 $ 1.00 123,000 5 2.00 $ 1.00 ON 60% 20 5 GON ctions Facts Student's Solution 70 Serious Business, Inc. 71 The company is preparing its budget for the coming year, 2017. The first step is to plan for the first quarter of that coming Units 73 Sales Information 74 Period 75 November 76 December 77 January 78 February 79 March 80 April 81 May 82 Unit selling price 98000 Actual 87000 Actual 96000 Planned 97000 Planned 99000 Planned 107000 Planned 118000 Planned 10 84 Finished Goods Inventory Planning 85 The company likes to keep 10% of the next quarter's unit sales in finished goods ending inventory. 86 87 Accounts Receivable & Collections 88 Sales on Account 100% 89 Collections Activity 90 Month of Sale 0.95 91 Month after Sale 0.05 92 Balance at 12/31/16 $185,000.00 Instructions Facts Student's Solution 94 Materials Inventory Costs & Planning 95 Direct Materials Amount Used per Unit Cost 96 Metal 2 lb 1 lb 97 The company likes to keep 5% of the material needed for the next month's production in raw materials ending inventory. 99 Accounts Payable & Disbursements 100 Purchases on Account 100% 101 Payment Activity 102 Month of Purchase 0.55 103 Month after Purchase 0.45 104 Balance at 12/31/16 $120,000.00 105 106 Direct Labor & Costs 107 Time per Unit Production 12 minutes 108 Pay Rate/Hour 109 110 Manufacturing Overhead Costs 111 Variable costs per direct labor hour 112 Indirect materials 0.2 113 Indirect labor 0.35 114 Utilities 0.45 115 Maintenance 0.25 116 Fixed costs per month Instructions Facts Student's Solution B C 0.2 0.35 0.45 0.25 - A 110 Manufacturing Overhead Costs Variable costs per direct labor hour 112 Indirect materials 113 Indirect labor 114 Utilities 115 Maintenance 116 Fixed costs per month 117 Salaries 118 Depreciation 119 Property taxes 120 Insurance 121 Janitorial 122 Selling and Administrative Costs 124 Variable costs per unit sold 125 Fixed costs per month 126 Advertising 127 Insurance 128 Salaries 129 Depreciation 130 Other fixed costs 131 132 Income Taxes $ 42,000.00 16,800.00 2,675.00 1,200.00 1,300.00 123 1.2 $ 15,000.00 14,000.00 72,000.00 25,000.00 3,000.00 MAALIM Instructions Facts Student's Solution 132 Income Taxes 133 Accrued on Monthly Net Income 134 Amounts Accrued Q4 2016 paid Januar $ 0.4 rounded to nearest dollar 200,000 136 Cash and Financing Matters 137 Cash Balance, 12/31/2016 74000 138 2017 Minimum Balance Required 585000 139 140 Monthly Dividends 1.75 per share 141 Outstanding Shares 5,000 142 143 Line of Credit 144 Limit None 145 Borrowing Increment Required $ 1,000.00 146 Interest Rate 0.07 147 Draws First of Month 148 Repayments Last of Month 149 Interest accumulates to the loan balance and is paid in full with each repayment. 150 151 Additional Item 152 Fixed Asset Purchase 365000 153 Month February 154 Instructions Facts Student's Solution Serious Business, Inc. The company is preparing its budget for the coming year, 2020. The first step is to plan for the FIRST QUARTER of that coming year. The following information has been gathered from their managers. 2 4 Sales Information 5 Period 6 November 7 December 8 January 9 February 10 March 11 April 12 May 13 Unit selling price Units 98000 Actual 87000 Actual 96000 Planned 97000 Planned 99000 Planned 107000 Planned 118000 Planned 10 Grading guidelines are on the instructions tab. 15 Finished Goods Inventory Planning 16 The company likes to keep 10% of the next month's unit sales in finished goods ending inventory 100% 18 Accounts Receivable & Collections 19 Sales on Account 20 Collections Activity 21 Month of Sale 22 Month after Sale Ralanraat 17/11/19 Instructions Facts 0.95 0.05 106 Student's Solution 100% 18 Accounts Receivable & Collections 19 Sales on Account 20 Collections Activity 21 Month of Sale 22 Month after Sale 23 Balance at 12/31/19 0.95 0.05 185,000.00 $ Materials Inventory Costs & Planning 26 Direct Materials 27 Metal Amount Used per Unit 2 lb 1 lb The company likes to keep 5% of the material needed for the next month's production in raw materials ending Inventory. 100% 30 Accounts Payable & Disbursements 31 Purchases on Account 32 Payment Activity 33 Month of Purchase 34 Month after Purchase 35 Balance at 12/31/19 0.55 0.45 120,000 $ 37 Direct Labor & Costs 38 Time per Unit Production 12 minutes Instructions Facts Student's Soluti % Direct Labor & Costs Time per Unit Production 39 Pay Rate/Hour 12 minutes 41 Manufacturing Overhead Costs Variable costs per direct labor hour 43 Indirect materials 44 Indirect labor 45 Utilities 46 Maintenance 47 Fixed costs per month 48 Salaries 49 Depreciation Property taxes Insurance 52 Janitorial 0.35 0.45 0.25 42,000 16,800 2,675 1,200 1,300 54 Selling and Administrative Costs 55 Variable costs per unit sold 56 Fixed costs per month 57 Advertising 58 Insurance 59 Salaries Instructions 15,000 14,000 72,000 Student's Solution Facts 1.2 $ 54 Selling and Administrative Costs 55 Variable costs per unit sold 56 Fixed costs per month Advertising Insurance Salaries Depreciation Other fixed costs 15,000 14,000 72,000 25,000 3,000 Income Taxes Accrued on Monthly Net Income Amounts Accrued Q4 2019, paid January 2020 0.4 rounded to nearest dollar 200,000 $ 67 Cash and Financing Matters Cash Balance, 12/31/2019 69 2020 Minimum Balance Required 74000 585000 Monthly Dividends 72 Outstanding Shares 1.75 per share 5,000 74 Line of Credit 75 Limit 76 Borrowing Increment Required Instructions None $ 1,000 Student's Solution Facts 67 Cash and Financing Matters Cash Balance, 12/31/2019 2020 Minimum Balance Required 74000 585000 Monthly Dividends Outstanding Shares 1.75 per share 5,000 72 S 74 Line of Credit 75 Limit None 76 Borrowing Increment Required 1,000 Interest Rate 0.07 78 Draws First of Month Repayments Last of Month 80 Interest accumulates to the loan balance and is paid in full with each repayment. 82 Additional Item 83 Fixed Asset Purchase 84 Month 365000 February 7 The facts for this problem are presented on the Facts tab of this workt Instructions - Your solutions should be clearly labeled on the Solutions tab of this workbook. 10 For the first quarter of 2020, do the following 11(a) Prepare a sales budget. This is similar to Klustration 22-3 of your textbook 12 (b) Prepare a production budget. This is similar to ilustration 22-5 of your textbook. 13 (c) Prepare a direct materials budget. (Round to nearest dollar) This is similar to Nustration 22-9 of your textbook 14 (d) Prepare a direct labor budget. (For calculations, round to the nearest hour. This is similar to illustration 22-11 of your textbook 15 (e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar.) This is similar to Mustration 22-12 of your textbook. 16 (1) Prepare a selling and administrative budget. This is similar to Mustration 22-13 of your textbook. 17 (8) Prepare a budgeted income statement. (Round intermediate calculations to the nearest dollar.) This is similar to lustration 22-15 of your textbook 18 h) Prepare a cash budget. This is similar to illustration 22.19 of your textbook 19 You will need to prepare schedules for expected collections from customers and expected payments to vendors first. See lustrations 22-17 and 22-18 of your textbook for guidance) 21 Rules: 22 Use Excel's functionality to your benefit. Points are lost for lack of formula. 23 Use proper formats for schedules, following the referenced textbook examples 24 Use dollar signs and underscores where appropriate 25 "Double-check your worki Verify your formula and logic