may i have it step by step on excel

a 8 Instructions - Your solutions should be dearly labeled on the Solutions tab of this workbook. 9 10 For the first quarter of 2020, do the following. 11 (a) Prepare a sales budget. This is similar to Illustration 22-3 of your textbook. a 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 Illustration 22-9 of your textbook. 14 (d) Prepare a direct labor budget. (For calculations, round to the nearest hour.) This is similar to Mustration 22-11 of your textbook. 15 (e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar.) This is similar to Illustration 22-12 of your textbook. 16 17 Rules: 18 Use Excel's functionality to your benefit. Points are lost for lack of formula. 19 Use proper formats for schedules, following the referenced textbook examples. 20 Use dollar signs and underscores where appropriate. 21 Double-check your work! Verify your formula and logic! 22 **YOU MUST PREPARE BUDGET DETAIL FOR EACH MONTH IN THE FIRST QUARTER** 23 24 Grading Guidelines: 25 Effective Use of Excel 40% 0 The company is preparing its budget for the coming year, 2020. The first step is to plan for the FIRST QUARTER of that coming 2 year. The following information has been gathered from their managers. 3 *YOU MUST PREPARE BUDGET DETAIL FOR EACH MONTH IN THE FIRST QUARTER** 4 5 Sales Information 6 Period Units 7 November 93,000 Actual B December 83,000 Actual 9 January 91,000 Planned 10 February 92,000 Planned Grading guidelines are on the instructic 11 March 94,000 Planned 12 April tab. 102,000 Planned 13 May 112,000 lPlanned 14 Unit selling price S 10.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 Month of Sale 90% Instructions Facts Student's Solution Ready Tune here to search a D 1 B13 Hnstructions!B78 G B C D E 46 Finished Goods Inventory Planning 77 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 90% 3 Month after Sale 10% 24 Balance at 12/31/19 185,000.00 25 26 Materials Inventory Costs & Planning 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 600 B13 fo = instructions!B78 B C D 100% 60% 40% 120,000 $ A 31 Accounts Payable & Disbursements 32 Purchases on Account 33 Payment Activity 34 Month of Purchase 35 Month after Purchase 36 Balance at 12/31/19 37 38 Direct Labor & Costs 39 Time per Unit Production 40 Pay Rate/Hour 41 42 Manufacturing Overhead Costs 43 Variable costs per direct labor hour 44 Indirect materials 45 Indirect labor 46 Utilities 9 minutes 5.00 $ $ 0.20 0.30 0.45 $ 0.20 0.30 0.45 0.25 $ 42 Manufacturing Overhead Costs 43 Variable costs per direct labor hour 44 Indirect materials 45 Indirect labor 46 Utilities 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 59 Insurance 60 Salaries 61 Depreciation 62 Other fixed costs 63 42,000 16,800 2,675 1,200 1,300 $ 1.10 $ 15,000 14,000 72,000 25,000 3,000 64. Income Tavec Inictructions Facts Student's Solution BIS B C D E $ $ 64 Income Taxes 65 Accrued on Monthly Net Income 35% rounded to nearest dollar 66 Amounts Accrued 04 2019, paid January 2020 200,000 67 68 Cash and Financing Matters 69 Cash Balance, 12/31/2019 $ 67,000 70 2020 Minimum Balance Required 525,000 71 72 Monthly Dividends $ 1.60 per share 73 Outstanding Shares 5,000 74 75 Line of Credit 76 Limit None 77 Borrowing Increment Required $ 1,000 78 Interest Rate 7% 79 Draws First of Month 80 Repayments Last of Month 81 Interest accumulates to the loan balance and is paid in full with each repayment. 82 83 Additional Item 84 Fixed Asset Purchase $ 328,000 85 . Month February 86 AD Instructions Facts Student's Solution