Prepare a selling and administrative budget.

H A | D E F G 1 Serious Business, Inc. The company is preparing its budget for the coming year, 2021. The first step is to plan for the first quarter. The following 2 information has been gathered from their managers. 3 8 9 May S 4 Sales Information 5 Period Units 61 November 108,000 Actual 7 December 97,000 Actual January 106,000 Planned February 107,000 Planned Grading guidelines are on the instructions March 109,000 Planned April 119,000 Planned tab. 12 130,000 Planned 13 Unit selling price 12.00 14 15 Finished Goods Inventory Planning 16 The company likes to keep 10% of the next month's unit sales in finished goods ending inventory. 17 18 Accounts Receivable & Collections 19 Sales on Account 100% 20 Collections Activity 21 Month of Sale 75% 22 Month after Sale 25% Instructions Facts Student's Solution JL -1111SLI ULLIULISHUJ- LIILEI YUUIUSCI ID II LICHICIU UIT LIITTISLI ULLIUIS LAU LUUDLall Value! Il 11SLI ULLIUMISHI F G H C D 15 Finished Goods Inventory Planning 16 The company likes to keep 10% of the next month's unit sales in finished goods ending inventory. 17 18 Accounts Receivable & Collections 19 Sales on Account 100% 20 Collections Activity 21 Month of Sale 75% 22 Month after Sale 25% 23 Balance at 12/31/20 $ 185,000.00 24 25 Materials Inventory Costs & Planning 26 Direct Materials Amount Used per Unit Cost 27 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 28 inventory. 29 30 Accounts Payable & Disbursements 31 Purchases on Account 100% 32 Payment Activity 33 Month of Purchase 45% 34 Month after Purchase 55% 35 Balance at 12/31/20 $ 120,000 Instructions Facts Student's Solution D6 IF(Instructions!B5="","Enter your User ID in the field on the Instructions tab to obtain values!", IF(Instructions!B4= C D E F G H I J $ 120,000 15 minutes 6.00 0.20 0.40 0.45 0.25 35 Balance at 12/31/20 36 37 Direct Labor & Costs 38 Time per Unit Production 39 Pay Rate/Hour 40 41 Manufacturing Overhead Costs 42 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 50 Property taxes 51 Insurance 52 Janitorial 53 54 Selling and Administrative Costs 55 Variable costs per unit sold 56 Fixed costs per month 57 Advertising Instructions Facts Ready 42,000 16,800 2,675 1,200 1,300 $ 1.30 15.000 Student's Solution A F G H 57 Advertising 58 Insurance 59 Salaries 60 Depreciation 61 Other fixed costs 15,000 14,000 72,000 25,000 3,000 62 63 Income Taxes 64 Accrued on Monthly Net Income 45% rounded to nearest dollar 65 Amounts Accrued Q4 2020 paid January 2021 $ 200,000 66 67 Cash and Financing Matters 68 Cash Balance, 12/31/2020 82,000 69 2021 Minimum Balance Required 640,000 70 71 Monthly Dividends 1.90 per share 72 Outstanding Shares 5,000 73 74 Line of Credit 75 Limit None 76 Borrowing Increment Required $ 1,000 77 Interest Rate 9% 78 Draws First of Month 79 Repayments Last of Month Instructions Facts Student's Solution DE X foc =IF(Instructions!B5="","Enter your User ID in the field on the Instructions tab to obtain values!",IF(Instr E F G H I A B C D 67 Cash and Financing Matters 68 Cash Balance, 12/31/2020 82,000 69 2021 Minimum Balance Required 640,000 70 71 Monthly Dividends 1.90 per share 72 Outstanding Shares 5,000 73 74 Line of Credit 75 Limit None 76 Borrowing Increment Required $ 1,000 77 Interest Rate 9% 78 Draws First of Month 79 Repayments Last of Month 80 Interest accumulates to the loan balance and is paid in full with each repayment. 81 82 Additional Item 83 Fixed Asset Purchase $ 400,000 84 Month February