Question

1 Approved Answer

Posted on Sep 08, 2024

please provide formula, need help D E A 7 The facts for this problem are presented on the Facts tab of this workbook 8 Instructions

please provide formula, need help image text in transcribed

D E A 7 The facts for this problem are presented on the Facts tab of this workbook 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 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 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 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% 26 Facts, Logic 20% 27 Completeness 30% 28 Spelling, Punctuation, Value Forma 10% 30 A B D E F G 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 November 113,000 Actual December 101,000 Actual January 111,000 Planned 10 February 112,000 planned 11 Grading guidelines are on the instructions March 114,000 planned 12 April 124,000 Planned tab. 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 Balancent 12/31/19 185,000.00 Number D7 > & =IF(Instructions!B5","Enter your User ID in the field on the Instructions tab to obtain values.", IF(Instrue H $ B 42,000 16,800 2,675 1,200 1,300 $ 1.45 $ A 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 64 Income Taxes 65 Accrued on Monthly Net Income 66 Amounts Accrued Q4 2019, paid January 2020 67 68 Cash and Financing Matters 69 Cash Balance, 12/31/2019 70 2020 Minimum Balance Required 71 72 Monthly Dividends Outstanding Shares 15,000 14,000 72,000 25,000 3,000 35% rounded to nearest dollar 200,000 $ $ 90,000 715,000 $ 2.25 per share 5,000 B 72 Monthly Dividends $ 2.25 per share 73 Outstanding Shares 5,000 74 75 Line of Credit 76 Limit None 77 Borrowing increment Required $ 1,000 78 Interest Rate 9% 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 S 445,000 85 Month February 86 87 88 89 90 91 92 93 94 95 D E A 7 The facts for this problem are presented on the Facts tab of this workbook 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 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 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 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% 26 Facts, Logic 20% 27 Completeness 30% 28 Spelling, Punctuation, Value Forma 10% 30 A B D E F G 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 November 113,000 Actual December 101,000 Actual January 111,000 Planned 10 February 112,000 planned 11 Grading guidelines are on the instructions March 114,000 planned 12 April 124,000 Planned tab. 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 Balancent 12/31/19 185,000.00 Number D7 > & =IF(Instructions!B5","Enter your User ID in the field on the Instructions tab to obtain values.", IF(Instrue H $ B 42,000 16,800 2,675 1,200 1,300 $ 1.45 $ A 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 64 Income Taxes 65 Accrued on Monthly Net Income 66 Amounts Accrued Q4 2019, paid January 2020 67 68 Cash and Financing Matters 69 Cash Balance, 12/31/2019 70 2020 Minimum Balance Required 71 72 Monthly Dividends Outstanding Shares 15,000 14,000 72,000 25,000 3,000 35% rounded to nearest dollar 200,000 $ $ 90,000 715,000 $ 2.25 per share 5,000 B 72 Monthly Dividends $ 2.25 per share 73 Outstanding Shares 5,000 74 75 Line of Credit 76 Limit None 77 Borrowing increment Required $ 1,000 78 Interest Rate 9% 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 S 445,000 85 Month February 86 87 88 89 90 91 92 93 94 95