DIA Marpe Center General $ % -48 Conditional Format Cell Formatting as Table Styles 1 Lab #7 Template for Chapter 20 2 Master Budget for a Manufacturer EF 522 4 Budget Assumptions 5 1. Projected January unit sales: 11,000 6 - Assumed month-over-month increase in unit sales: 7 - Selling price per unit: 8 2. Monthly sales will be 25% cash sales and 75% credit sales (collected in the month following the sales) 93. Ending finished goods inventory should equal 80% of the next month's expected unit sales. 10 4. Ending raw materials inventory should equal 50% of the next month's expected raw material needs. 11 5. The product's manufacturing cost is $12.65 per unit, including: 12 - $7.35 per unit for materials (0.5 lbs. @ $14.70 per lb.) All purchases are on credit, and paid the following month. 13 - $3.75 per unit for direct labor (0.25 hour $15 per hour rate) 14 - $1.10 per unit for variable overhead 15 - $0.45 per unit for fixed overhead (consisting entirely of $5,000 per month for depreciation expense) 16 6. Sales commissions are 10% of sales, and are paid in the month of the sales. 17 7. The sales manager's monthly salary is $4,000 in January, and $5,000 each month thereafter. 18 8. Monthly general & administrative expenses include $12,000 in salaries and 0.8% monthly interest on a long-term $200,000 note (due next year). 19 9. Minimum monthly ending cash balance should be $50,000. If necessary, the company borrows enough cash to reach the 20 minimum. Any borrowed short-term notes require a 1% interest payment at month-end (before any repayments borrowings). Ifending cash exceeds 21 the minimum of $50,000, the excess is applied to repay any outstanding short-term note. There are currently no outstanding short-term notes 22 10. Dividends of $120.000 are declared and paid in February. 23 11. Equipment purchases of $75,000 are scheduled for March 25 Required - Prepare the following budget schedules: 26 28 1. Sales Budget January February March 1st Quarter 30 Projected Monthly Unit Sales 31 Selling Price Per Unit 32 Total Budgeted Sales 35 2. Production Budget Ch. 20 Template Date Window Help Excel Fields View Insert Format Tools Autofon BE . Home Insert Draw Page Layout Formulas Data Lab 7 for Chapter 20 Template - ACC 214 (2) Review View TV LOA e rger % Cundin Form C D E F G H 17 7. The sales manager's monthly salary is $4.000 in January, and $5,000 each month thereafter. 18 8. Monthly general & administrative expenses include $12,000 in salaries and 0 % monthly interest on a long-term $200,000 note (due next year) 10 Minimum monthly ending cash balance should be $50.000. If necessary, the company borrows enough cash to reach the 20 minimum. Any borrowed short-term notes require a 1% interest payment at month-end before any repayments borrowings). Wending cash exceeds 21 the minimum of $50,000, the excess is applied to repay any outstanding short-term mot There are currently no outstanding short-term notes 22 10. Dividends of $120.000 are declared and paid in February 23 11. Equipment purchases of $75.000 are scheduled for March Required. Prepare the following budget schedules 28 1. Sales Budget January February March 1st Quarter 30 Projected Monthly Unit Sales 3 Seting Price Per Unit 32 Total Budgeted Sales 3 2. Production Budget January February March 1st Quarter 37 Projected Next Monty's Unit Sales 3 Targeted Ending Inventory 3 Desired Ending Inventory units) 40 Current Period's Unit Sales 41 Estimated Units of Production 42 Less: Beginning inventory (units) 43 Targeted Units to be Produced 43. Direct Materials Budget January February March - Targeted us to Be Produced 4) Materials Requirement per una Malers Needed for productions 1 At Targeted Ending inventory Template + ) 2.100 BI O A Mero A Centar $ 99882 Conditional Format Formatting as Table Cell Styles Pato | 12 x fxl. 17 7. The sales manager's monthly salary is $4,000 in January, and $5,000 each month thereafter. 18 8. Monthly general & administrative expenses include $12,000 in salaries and 0.8% monthly interest on a long-term $200,000 note (due next year). 19 9. Minimum monthly ending cash balance should be $50,000. If necessary, the company borrows enough cash to reach the 20 minimum. Any borrowed short-term notes require a 1% interest payment at month-end (before any repayments/borrowings). If ending cash exceeds 21 the minimum of $50,000, the excess is applied to repay any outstanding short-term note. There are currently no outstanding short-term notes 22 10. Dividends of $120,000 are declared and paid in February. 23 11. Equipment purchases of $75,000 are scheduled for March 25 Required - Prepare the following budget schedules: 28 1. Sales Budget January February March 1st Quarter 30 Projected Monthly Unit Sales 31 Seling Price Per Unit 32 Total Budgeted Sales nuary February March 1st Quarter 35 2. Production Budget 37 Projected Next Month's Unit Sales 38 Targeted Ending Inventory % 39 Desired Ending Inventory (units) 40 Current Period's Unit Sales 41 Estimated Units of Production 42 Less: Beginning Inventory (units) 43 Targeted Units to be produced 8.400 46 3. Direct Materials Budget January February March 48 Targeted Units to Be Produced 49 Materials Requirement per Unit (lbs) 50 Materials Needed for Production (lbs) 51 Add: Targeted Ending Inventory (lbs) 4 Ch. 20 Template + 2,100 25 Required - Prepare the following budget schedules: 26 28 1. Sales Budget January February March 1st Quarter 30 Projected Monthly Unit Sales 31 Selling Price Per Unit 32 Total Budgeted Sales 34 35 2. Production Budget January February March 1st Quarter 37 Projected Next Month's Unit Sales 38 Targeted Ending Inventory % 39 Desired Ending Inventory (units) 40 Current Period's Unit Sales 41 Estimated Units of Production 42 Less: Beginning Inventory (units) 43 Targeted Units to be produced 8,400 46 3. Direct Materials Budget January February March 48 Targeted Units to Be Produced 49 Materials Requirement per Unit (lbs) 50 Materials Needed for Production (lbs) 51 Add: Targeted Ending Inventory (lbs) 52 Total Materials Required (lbs) 53 Less: Beginning Inventory (lbs) 54 Materials to Be Purchased (lbs) 55 56 Materials Price per Pound 57 Estimated Cost of Direct Materials Purchases 2,100 2,425 Ch. 20 Template + Entet 45 46 3. Direct Materials Budget 48 Targeted Units to Be Produced 49 Materials Requirement per Unit (lbs) 50 Materials Needed for Production (lbs) 51 Add: Targeted Ending Inventory (lbs) 52 Total Materials Required (lbs) 53 Less: Beginning Inventory (lbs) 54 Materials to Be Purchased (lbs) 56 Materials Price per Pound 57 Estimated Cost of Direct Materials Purchases 60 4. Direct Labor Budget 62 Targeted Units to Be Produced 63 Labor Requirement per Unit (hours) 64 Total Labor Hours Needed 66 Labor Rate per Hour 67 Estimated Cost of Direct Labor 70 5. Factory Overhead Budget 71 72 Targeted Units to Be Produced 73 Variable Factory Overhead Rate 74 Budgeted Variable Overhead 75 Budgeted Fixed Overhead 76 Budgeted Total Overhead 79 6. Sellina. General & Administrative Expense Budaet Ch. 20 Template Enter 12 = = = General thx Paste 3 112 A Arial BIU for AA A Wrap Text Merge & Center $ % 1 % C D E 69 70 5. Factory Overhead Budget January February March 72 Targeted Units to Be Produced 73 Variable Factory Overhead Rate 74 Budgeted Variable Overhead 75 Budgeted Fixed Overhead 76 Budgeted Total Overhead 79 6. Selling, General & Administrative Expense Budget January February March 1st Quarter 81 Total Budgeted Sales 82 Commission % 83 Sales Commissions 84 Manager's Salary 85 Budgeted Selling Expenses 86 87 Administrative Salaries 88 Interest on Long-Term Note Payable 89 Budgeted General & Administrative Expenses 90 Total Budgeted Selling, General & Admin. Expense Budget 93 7. Schedule of Cash Receipts January February March 1st Quarter 95 Total Budgeted Sales 96 Estimated Ending Accounts Receivable (credit sales) 97 Cash Receipts: 98 - Cash Sales 99 - Collection of Prior Month's Receivables 100 Estimated Cash to Be Collected 101 $175,000 Ch. 20 Template - Enter Insert Draw Page Layout Formulas Data Review View th = = = Arial BIU 12 A A T OA 25 wrap Text Merge & Center A A 96 Estimated Ending Accounts Receivable (credit sales) 97 Cash Receipts: 98 - Cash Sales 99 - Collection of Prior Month's Receivables 100 Estimated Cash to Be Collected 101 $175,000 102 January $63,818 February January $50,000 February 103 8. Schedule of Cash Payments for Materials 104 105 Cash Payments for Prior Month's Materials' Purchases 106 107 108 9. Cash Budget 109 110 Beginning Cash Balance 111 Add: Cash Receipts from Customers 112 Total Cash Available 113 Less: Cash Payments for 114 - Direct Materials 115 - Direct Labor 116 - Variable Overhead 117 - Sales Commissions 118 - Sales Salaries 119 - Administrative Salaries 120 - Dividends 121 - Interest on Long-Term Note Payable 122 - Interest on Short-Term Note Payable 123 - Purchase of Equipment 124 Total Cash Payments 125 126 Preliminary Cash Balance 127 Borrowing/repayments required to maintain minimum cash balance 128 Ending Cash Balance 129 Short-term note balance, End of Month 130 Ch. 20 Template Enter