5 8 9 14 15 1 Instructions 2 3 1. Use the given information to create a Given Data Worksheet in the provided Master Budget Excel file. 4 2. For the first quarter of 2024 prepare the following budgets or schedules using the appropriate supplied worksheet. 6 a. Prepare a sales budget. 7 b. Prepare a schedule of cash collections from customers (round totals to nearest dollar), c. Prepare a production budget. d. Prepare a direct materials budget (round to nearest dollar). 10 e. Prepare a schedule of cash disbursement for inventory purchases. (round totals to nearest dollar). 11 1. Prepare a direct labor budget (For calculations, round to the nearest hour.) 12 & Prepare a manufacturing overhead budget. (round amounts to the nearest dollar. 13 h. Prepare a selling and administrative budget. 1. Prepare a cash budget. 1. Prepare a budgeted income statement. 16 ki Prepare a budgeted balance sheet. 18 3. Each worksheet should only contain cell references or formulas for ANY number except ZEROES. 19 Students are allowed to manually input zeroes (0)s and time for interest. 20 21 4. Where appropriate, schedules or budgets should reference the budget or schedule that "feeds" 22 the information to it. For example, the Production Budpet should get the required Sales Units from the Sales Budget instead of getting it from the Given Data Worksheet. 25 5. You need to round your calculations of raw materials per unit, direct labor per unit and 26 manufacturing overhead per unit to two (2) decimal places. (hint: your total product cost per unit equals $3.96) 27 28 6. Check Figures: 29 Ending Cash as of 3/31/2024 . 51,455,744 Ending Raw Materials as of 3/31/2024 -59,469 frounded to 0 decimal places) Net Income - $1,915,802 33 Total Assets as of 3/31/2024 - $3,365,613 17 + 23 24 30 31 32 - 34 35 36 Note Ignore $1 difference with check figures except for unit costs (rounded to two decimals for unit costs) Instructions Given Data Sales Budget Sah of Cash Collections Production Budget 1 2 Brooks Corporation is preparing its budget for the coming year, 2024. The 3 first step is to plan for the first quarter of that coming year. Brooks gathered the following 4 information from the managers: 5 6 1) Sales and Production 7 8 Unit sales for November (2023): 112,500 9 Unit sales for December (2023): 102,100 10 Brooks Corporation is preparing its budget for the coming year, 2024. The 11 first step is to plan for the first quarter of that coming year. Brooks gathered the following 12 information from the managers: 13 14 1) Sales and Production 15 16 Unit sales for November (2023): 91,700 17 Unit sales for December (2023): 81,300 18 19 Brooks likes to keep 10% of the next month's unit sales in ending inventory. All 20 sales are on account. 85% of the Accounts Receivable are collected in the month of sale, 21 and 15% of the Accounts Receivable are collected in the month after sale. Accounts receivable 22 on December 31, 2023, totaled $183,780. 23 24 Month of Sale Collection 85% 25 Month after Sale Collection 15% 26 AR, 12/31/2023 balance 183,780 27 28 Desired Ending Inventory 10% 29 30 2) Direct Materials 31 32 Brooks likes to keep 5% of the materials needed for the next month in its ending inventory 33 In 2024, 60% is paid in the month of purchase, and 40% ispaid in the month after purchase. 34 Accounts Payable on December 31, 2023, totaled $120,595 and will all be paid in January 35 Raw Materials on December 31, 2023, totaled 11,295 pounds. 36 Cost of materials is $0.75 per pound and each unit requires 2 pounds. 37 Instructions Given Data Sales Budget Sch of Cash Colle C D E 38 Desired Ending Inventory 5% 39 Payment for RM in month of Purchase 60% 40 Payment for RM in month following 40% 41 Cost of material per pound $0.75 42 Pounds per unit 2 43 AP, 12/31/2023 Balance 120,595 44 RM, 12/31/2023 Balance 11,295 45 46 3) Direct Labor (needs to be converted to decimal of a hour) 47 48 Labor requires 12 minutes per unit for completion and is paid at a rate of $8 per hour. 49 50 Number of Minutes per Unit 12 51 Hours per Unit 0.20 52 Cost per Direct Labor Hour $8.00 53 54 4) Manufacturing Overhead 55 56 Indirect materials: 0.30 cents per labor hour 57 Indirect labor: 0.50 cents per labor hour 58 Utilities: 0.45 cents per labor hour 59 Maintenance: 0.25 cents per labor hour 60 Salaries: 42,000 dollars per month 61 Depreciation: 16,800 dollars per month 62 Property taxes: 2,675 dollars per month 63 Insurance: 1,200 dollars per month 64 Janitorial: 1,300 dollars per month 65 66 5) Selling and Administrative 67 68 Variable selling and administrative cost per unit $1.60 69 70 Fixed Expenses 71 Number of Months in a Quarter 3 72 Advertising $15,000 a month 73 Insurances $140 lamonth Instructions Given Data Sales Budget Sch of Cash Collections + B D E 70 Fixed Expenses 71 Number of Months in a Quarter 72 Advertising: $15,000 a month 73 Insurance: $1,400 a month 74 Salaries: $72,000 a month 75 Depreciation: $2,500 a month 76 Other fixed costs: $3,000 a month 77 78 6) Other Information 79 80 The Cash balance on December 31, 2023, totaled $100,500, but management has decided 81 it would like to maintain a cash balance of at least $800,000 beginning on January 31, 82 2024. Dividends are paid each month at the rate of $2.50 per share for 5,000 shares 83 outstanding. The company has an open line of credit with Romney's Bank. The terms of 84 the agreement requires borrowing to at 8% interest. Brooks borrows on the first day 85 of the month and repays on the last day of the month. A $500,000 equipment purchase 86 is planned for February. Beginning Building and Equipment, net is $1,200,000 87 Beginning Retained Earnings is $ 1,316,891. Beginning Capital Stock is $100,000. 88 89 Cash, 12/31/2023 Balance 100,500 90 Minimum Cash Balance needed for 1/31/2024 800,000 91 Dividends per month 2.50 per outstanding share 92 Number of Months for Loan 2 93 Number of Months per Year 12 94 Annual Interest Rate 8% 95 Number of Outstanding Shares 5,000 96 Beginning Building & Equipment, net 1,200,000 97 February Equipment Purchase 500,000 98 Beginning Retained Earnings 1,316,891 99 Beginning Capital Stock 100,0001 100 101 102 103 104 105 Instructions Given Data Sales Budget Sch of Cash Collection 4,500 21 Excel File Edit View Insert Format Tools Data Window Help AutoSave Starla o. Master Budget Home Insert Draw Page Layout Formulas Data Review View Tell me X Cabbri (Body) 11 v AA EE ab General lill Paste B I U EE IN $ % 9 B17 BROOKS CORPORATION Schedule of Cash Collections For the First Quarter of 2024 Credit Sales January February March Total Accounts Receivable, 12/31/2023 7 January 3 February 9 March 20 Total Cash Collections 11 12 3/31/2024 Bal Accounts Receivable 15 16 17 20 21 22 23 24 25 26 27 28 29 30 31 32 Instructions Girvon Data Salon Budget Sch of Cas Collections Production Budget Durect Mall 3 4.500 O 21 2 3 BROOKS CORPORATION Schedule of Cash Disbursements for Inventory For the First Quarter of 2024 Credit Purchases January February March Total 4 5 6 Accounts Payable, 12/31/2023 7 January 8 February 9 March 10 Total Cash Disburements 11 12 13 14 15 16 17 3/31/2024 Bal-AP 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Instructions Given Data Sales Budget Sch of Cash Collections Production Budget Direct A B C D E F 2 3 4 BROOKS CORPORATION Direct Labor Budget For the First Quarter of 2024 January February March Quarter 5 6 Units to be produced 7 Direct labor time (hours per unit) 8 Total required direct labor-hours 9 Direct labor cost per hour 10 Total direct labor cost 11 12 Direct Labor Cost per unit 13 2 BROOKS CORPORATION Income Statement For the First Quarter of 2024 3 4 5 6 Sales 7 Cost of goods sold 8 Gross profit 9 Selling and administrative expenses: 10 Variable Expenses 11 Salaries and wages 12 Advertising 13 Insurance 14 Depreciation 15 Other expenses 16 Total expenses 17 Operating Income 18 Interest Expense 19 Net income 20 21 22 23 24 25 26 27 28 29 30 31 32 33 1 2 BROOKS CORPORATION Budgeted Balance Sheet As of 3-31-2024 3 Assets Liabilities and Equity 4 5 6 Current assets: 7 Cash 8 Accounts receivable 9 Inventory 10 Finished Goods 11 Raw Material 12 Total current assets 13 14 Buildings and equipment, net 15 Total assets 16 17 18 Current liabilities: 19 Accounts payable 20 Stockholders' equity: 21 Capital stock 22 Retained earnings 23 Total liabilities and equity 24 25 "Retained earnings, beginning 26 Add net income 27 Total 28 Deduct cash dividends 29 Retained earnings, ending 30 31 32 Direct Labor Budget Manufacturing Overhead Budget Soll, and Admin Expanse Budget 5 8 9 14 15 1 Instructions 2 3 1. Use the given information to create a Given Data Worksheet in the provided Master Budget Excel file. 4 2. For the first quarter of 2024 prepare the following budgets or schedules using the appropriate supplied worksheet. 6 a. Prepare a sales budget. 7 b. Prepare a schedule of cash collections from customers (round totals to nearest dollar), c. Prepare a production budget. d. Prepare a direct materials budget (round to nearest dollar). 10 e. Prepare a schedule of cash disbursement for inventory purchases. (round totals to nearest dollar). 11 1. Prepare a direct labor budget (For calculations, round to the nearest hour.) 12 & Prepare a manufacturing overhead budget. (round amounts to the nearest dollar. 13 h. Prepare a selling and administrative budget. 1. Prepare a cash budget. 1. Prepare a budgeted income statement. 16 ki Prepare a budgeted balance sheet. 18 3. Each worksheet should only contain cell references or formulas for ANY number except ZEROES. 19 Students are allowed to manually input zeroes (0)s and time for interest. 20 21 4. Where appropriate, schedules or budgets should reference the budget or schedule that "feeds" 22 the information to it. For example, the Production Budpet should get the required Sales Units from the Sales Budget instead of getting it from the Given Data Worksheet. 25 5. You need to round your calculations of raw materials per unit, direct labor per unit and 26 manufacturing overhead per unit to two (2) decimal places. (hint: your total product cost per unit equals $3.96) 27 28 6. Check Figures: 29 Ending Cash as of 3/31/2024 . 51,455,744 Ending Raw Materials as of 3/31/2024 -59,469 frounded to 0 decimal places) Net Income - $1,915,802 33 Total Assets as of 3/31/2024 - $3,365,613 17 + 23 24 30 31 32 - 34 35 36 Note Ignore $1 difference with check figures except for unit costs (rounded to two decimals for unit costs) Instructions Given Data Sales Budget Sah of Cash Collections Production Budget 1 2 Brooks Corporation is preparing its budget for the coming year, 2024. The 3 first step is to plan for the first quarter of that coming year. Brooks gathered the following 4 information from the managers: 5 6 1) Sales and Production 7 8 Unit sales for November (2023): 112,500 9 Unit sales for December (2023): 102,100 10 Brooks Corporation is preparing its budget for the coming year, 2024. The 11 first step is to plan for the first quarter of that coming year. Brooks gathered the following 12 information from the managers: 13 14 1) Sales and Production 15 16 Unit sales for November (2023): 91,700 17 Unit sales for December (2023): 81,300 18 19 Brooks likes to keep 10% of the next month's unit sales in ending inventory. All 20 sales are on account. 85% of the Accounts Receivable are collected in the month of sale, 21 and 15% of the Accounts Receivable are collected in the month after sale. Accounts receivable 22 on December 31, 2023, totaled $183,780. 23 24 Month of Sale Collection 85% 25 Month after Sale Collection 15% 26 AR, 12/31/2023 balance 183,780 27 28 Desired Ending Inventory 10% 29 30 2) Direct Materials 31 32 Brooks likes to keep 5% of the materials needed for the next month in its ending inventory 33 In 2024, 60% is paid in the month of purchase, and 40% ispaid in the month after purchase. 34 Accounts Payable on December 31, 2023, totaled $120,595 and will all be paid in January 35 Raw Materials on December 31, 2023, totaled 11,295 pounds. 36 Cost of materials is $0.75 per pound and each unit requires 2 pounds. 37 Instructions Given Data Sales Budget Sch of Cash Colle C D E 38 Desired Ending Inventory 5% 39 Payment for RM in month of Purchase 60% 40 Payment for RM in month following 40% 41 Cost of material per pound $0.75 42 Pounds per unit 2 43 AP, 12/31/2023 Balance 120,595 44 RM, 12/31/2023 Balance 11,295 45 46 3) Direct Labor (needs to be converted to decimal of a hour) 47 48 Labor requires 12 minutes per unit for completion and is paid at a rate of $8 per hour. 49 50 Number of Minutes per Unit 12 51 Hours per Unit 0.20 52 Cost per Direct Labor Hour $8.00 53 54 4) Manufacturing Overhead 55 56 Indirect materials: 0.30 cents per labor hour 57 Indirect labor: 0.50 cents per labor hour 58 Utilities: 0.45 cents per labor hour 59 Maintenance: 0.25 cents per labor hour 60 Salaries: 42,000 dollars per month 61 Depreciation: 16,800 dollars per month 62 Property taxes: 2,675 dollars per month 63 Insurance: 1,200 dollars per month 64 Janitorial: 1,300 dollars per month 65 66 5) Selling and Administrative 67 68 Variable selling and administrative cost per unit $1.60 69 70 Fixed Expenses 71 Number of Months in a Quarter 3 72 Advertising $15,000 a month 73 Insurances $140 lamonth Instructions Given Data Sales Budget Sch of Cash Collections + B D E 70 Fixed Expenses 71 Number of Months in a Quarter 72 Advertising: $15,000 a month 73 Insurance: $1,400 a month 74 Salaries: $72,000 a month 75 Depreciation: $2,500 a month 76 Other fixed costs: $3,000 a month 77 78 6) Other Information 79 80 The Cash balance on December 31, 2023, totaled $100,500, but management has decided 81 it would like to maintain a cash balance of at least $800,000 beginning on January 31, 82 2024. Dividends are paid each month at the rate of $2.50 per share for 5,000 shares 83 outstanding. The company has an open line of credit with Romney's Bank. The terms of 84 the agreement requires borrowing to at 8% interest. Brooks borrows on the first day 85 of the month and repays on the last day of the month. A $500,000 equipment purchase 86 is planned for February. Beginning Building and Equipment, net is $1,200,000 87 Beginning Retained Earnings is $ 1,316,891. Beginning Capital Stock is $100,000. 88 89 Cash, 12/31/2023 Balance 100,500 90 Minimum Cash Balance needed for 1/31/2024 800,000 91 Dividends per month 2.50 per outstanding share 92 Number of Months for Loan 2 93 Number of Months per Year 12 94 Annual Interest Rate 8% 95 Number of Outstanding Shares 5,000 96 Beginning Building & Equipment, net 1,200,000 97 February Equipment Purchase 500,000 98 Beginning Retained Earnings 1,316,891 99 Beginning Capital Stock 100,0001 100 101 102 103 104 105 Instructions Given Data Sales Budget Sch of Cash Collection 4,500 21 Excel File Edit View Insert Format Tools Data Window Help AutoSave Starla o. Master Budget Home Insert Draw Page Layout Formulas Data Review View Tell me X Cabbri (Body) 11 v AA EE ab General lill Paste B I U EE IN $ % 9 B17 BROOKS CORPORATION Schedule of Cash Collections For the First Quarter of 2024 Credit Sales January February March Total Accounts Receivable, 12/31/2023 7 January 3 February 9 March 20 Total Cash Collections 11 12 3/31/2024 Bal Accounts Receivable 15 16 17 20 21 22 23 24 25 26 27 28 29 30 31 32 Instructions Girvon Data Salon Budget Sch of Cas Collections Production Budget Durect Mall 3 4.500 O 21 2 3 BROOKS CORPORATION Schedule of Cash Disbursements for Inventory For the First Quarter of 2024 Credit Purchases January February March Total 4 5 6 Accounts Payable, 12/31/2023 7 January 8 February 9 March 10 Total Cash Disburements 11 12 13 14 15 16 17 3/31/2024 Bal-AP 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Instructions Given Data Sales Budget Sch of Cash Collections Production Budget Direct A B C D E F 2 3 4 BROOKS CORPORATION Direct Labor Budget For the First Quarter of 2024 January February March Quarter 5 6 Units to be produced 7 Direct labor time (hours per unit) 8 Total required direct labor-hours 9 Direct labor cost per hour 10 Total direct labor cost 11 12 Direct Labor Cost per unit 13 2 BROOKS CORPORATION Income Statement For the First Quarter of 2024 3 4 5 6 Sales 7 Cost of goods sold 8 Gross profit 9 Selling and administrative expenses: 10 Variable Expenses 11 Salaries and wages 12 Advertising 13 Insurance 14 Depreciation 15 Other expenses 16 Total expenses 17 Operating Income 18 Interest Expense 19 Net income 20 21 22 23 24 25 26 27 28 29 30 31 32 33 1 2 BROOKS CORPORATION Budgeted Balance Sheet As of 3-31-2024 3 Assets Liabilities and Equity 4 5 6 Current assets: 7 Cash 8 Accounts receivable 9 Inventory 10 Finished Goods 11 Raw Material 12 Total current assets 13 14 Buildings and equipment, net 15 Total assets 16 17 18 Current liabilities: 19 Accounts payable 20 Stockholders' equity: 21 Capital stock 22 Retained earnings 23 Total liabilities and equity 24 25 "Retained earnings, beginning 26 Add net income 27 Total 28 Deduct cash dividends 29 Retained earnings, ending 30 31 32 Direct Labor Budget Manufacturing Overhead Budget Soll, and Admin Expanse Budget