The focus for this problem are presented on the Fucts tab of this workbook. 3 Instructions - Your solutions should be dearly labeled on the Solutions tab of this workbook. 9 0 For the first quarter of 2020, do the following 1 (a) Prepare a sales budget. This is similar to Illustration 22-3 of your textbook 3 (b) Prepare a production budget. This is similar to Wlustration 22-5 of your textbook Prepare a direct materials budget. (Round to nearest dollar) This is similar to Illustration 22-9 of your textbook. (a) Prepare a direct labor budget. (For calculations, round to the nearest hour.) This is similar to Mustration 22-11 of your textbook (e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar. This is similar to Austration 22-12 of your textbook Rules: Use Excel's functionality to your benefit. Points are lost for lack of formula Use proper formats for schedules, following the referenced textbook examples Use dollar signs and underscores where appropriate. Double-check your work! Verify your formula and logic! YOU MUST PREPARE BUDGET DETAIL FOR EACH MONTH IN THE FIRST QUARTER": Grading Guidelines: Effective Use of Excel Facts, Logic Completeness Spelling, Punctuation, Value For 40% 20% 30% 10% D7 X =IF(Instructions!Bs="""Enter your User ID in the field on the instruc B D 3 YOU MUST PREPARE BUDGET DETAIL FOR EACH MONTH IN THE FIRST QUARTER 4 5 Sales Information 6 Period Units 17 November 113,000 Actual 8 December 101,000 Actual 9 January 111,000 Planned 10 February 112,000 Planned Grading guidelin 11 March 114,000 Planned 12 April 124,000 Planned 13 May 136,000 Planned 14 Unit selling price $ 12.00 15 16 Finished Goods Inventory Planning 17 The company likes to keep 10% of the next month's unit sales in finished goods ending inventory 18 19 Accounts Receivable & Collections 20 Sales on Account 100% 21 Collections Activity 22 Month of Sale 85% 23 Month after Sale 1596 24 Balance at 12/31/19 $ 185,000.00 05 Instructions Facts Student's Solution 26 Materials Inventory Costs & Planning $ 36 27 Direct Materials Amount Used per Unit Cost 28 Metal 2 lb 1.00 lb The company likes to keep 5% of the material needed for the next month's production in raw materials ending inventory. 29 30 31 Accounts Payable & Disbursements 32 Purchases on Account 100% 33 Payment Activity 34 Month of Purchase 50% 35 Month after Purchase 50% Balance at 12/31/19 $ 120,000 37 38 Direct Labor & Costs 39. Time per Unit Production 9 minutes 40 Pay Rate/Hour $ 7.00 41 42 Manufacturing Overhead Costs 43 Variable costs per direct labor hour 44 Indirect materials S 0.30 45 Indirect labor 0.45 46 Utilities 0.45 Shilinin 0.25 $ 42,000 16,800 2,675 1,200 1,300 $ 1.45 47 Maintenance 48 Fixed costs per month 49 Salaries 50 Depreciation 51 Property taxes 52 Insurance 53 Janitorial 54 55 Selling and Administrative Costs 56 Variable costs per unit sold 57 Fixed costs per month 58 Advertising Insurance 60 Salaries 61 Depreciation 62 Other fixed costs 63 64 Income Taxes 65 Accrued on Monthly Net Income 66 Amounts Accrued Q4 2019, paid January 2020 S 15,000 14,000 72,000 25,000 3,000 35% rounded to nearest dollar 200,000 S 67 68 Cash and Financing Matters KO Cash Aslanro 17/21/anna annon -9 Cash Balance, 12/31/2019 $ 90,000 O 2020 Minimum Balance Required 715,000 1 2 Monthly Dividends $ 2.25 per share 3 Outstanding Shares 5,000 4 5 Line of Credit 76 Limit None 7 Borrowing Increment Required $ 1,000 8 Interest Rate 9% 19 Draws First of Month 30 Repayments Last of Month 31 Interest accumulates to the loan balance and is paid in full with each repayment. 82 33 Additional Item 34 Fixed Asset Purchase 445,000 B5 Month February 86 $