22% 65 Excel File Edit View Insert Format Tools Data Window Help 5.84 GB AutoSave Lab 7 for Chapter 20 Template - ACC 214 (2) Home Insert Draw Page Layout Formulas Data Review View 12A AT Conditional Formatting General Format as Table Paste a BTV $ % 9 3 Styles - Ax fx C D E F G 1 Lab #7 Template for Chapter 20 Name: 2 Master Budget for a Manufacturer GM Format X 51 H 4 Budget Assumptions 5 1. Projected January unit sales: 11,000 6 - Assumed month-over-month increase in unit sales: 5% 7 - Selling price per unit: $22 8 2. Monthly sales will be 25% cash sales and 75% credit sales (collected in the month following the sales) 3. 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). 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: Ch. 20 Template + Tools Excel File Edit View Insert Format AutoSave OFF s u Home Insert Draw Page Layout Formulas 12A A Paste & BIU-Bravar - H51 v fx Data = Data Window Help 5.85 GB Lab 7 for Chapter 20 Template - ACC 214 (2) Review View = = General Conditional Formatting Format as Table $ % 68 Cell Styles vd Arial 25 Required. Prepare the following budget schedules: 26 28 1. Sales Budget 29 January February March 1st Quarter 30 Projected Monthly Unit Sales 31 Selling Price Per Unit 32 Total Budgeted Sales 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 46 3. Direct Materials Budget 47 January February March 48 Targeted Units to Be Produced 49 Materials Requirement per Unit (lbs) 50 Materials Needed for Production (lbs) Ch. 20 Template + MacBook Excel File Edit View Insert . . AutoSave S u Home Insert Draw Page Layout X Arial 12 Paste BLUE H51 A fx 46 3. Direct Materials Budget Format = Formulas A A -A Tools Data Window Help 5.84 GB Lab 7 for Chapter 20 Template - ACC 214 (2) Data Review View = = = 19 General Conditional Formatting Format as Table % ) 2 Cell Styles U ID 47 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) 2,100 55 56 Materials Price per Pound 57 Estimated Cost of Direct Materials Purchases 60 4. Direct Labor Budget January February March 62 Targeted Units to Be Produced 63 Labor Requirement per Unit (hours) 64 Total Labor Hours Needed 65 66 Labor Rate per Hour 67 Estimated Cost of Direct Labor 70 5. Factory Overhead Budget January February March Ch. 20 Template + MacBook Excel File Edit View Insert Format Tools O AutoSave OFF A Svo- Home Insert Draw Page Layout Formulas Data Data Window Help 5.84 GB Lab 7 for Chapter 20 Template - ACC 214 (2) Review View Arial LO General $ % Conditional Formatting Format as Table Cell Styles Paste BIU ) H51 x fx A 70 5. Factory Overhead Budget E F G 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 77 78 79 6. Selling, General & Administrative Expense Budget 80 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 91 92 93 7. Schedule of Cash Receipts 94 95 Total Budgeted Sales Ch. 20 Template January February March 1st Quarter Data Window Help 5.86 GB E Lab 7 for Chapter 20 Template - ACC 214 (2) Review View Excel File Edit View Insert Format Tools AutoSaver svo Home Insert Draw Page Layout Formulas Data Arial 12 JAA = Paste BI U OA 7151 8x v fx A 93 7. Schedule of Cash Receipts IHI || !! = General $ % Conditional Formatting Format as Table Cell Styles , F G January February March 1st Quarter 95 Total Budgeted Sales 26 Estimated Ending Accounts Receivable (credit sales) e7 Cash Receipts: 38 - Cash Sales 29 - Collection of Prior Month's Receivables 00 Estimated Cash to Be Collected $175,000 02 3 8. Schedule of Cash Payments for Materials January $63,818 February March 1st Quarter 5 Cash Payments for Prior Month's Materials' Purchases 6 8 9. Cash Budget January $50,000 February March o Beginning Cash Balance 1 Add: Cash Receipts from Customers 2 Total Cash Available 3 Less: Cash Payments for 4 - Direct Materials 5 - Direct Labor 6 - Variable Overhead 7 - Sales Commissions 8 - Sales Salaries 9 - Administrative Salaries Ch. 20 Template Data Window Help 5. Lab 7 for Chapter 20 Template - ACC 214 (2) Review View Excel File Edit View Insert Format Tools AutoSaver svo: Home Insert Draw Page Layout Formulas Data .8 Arial ~ 12 A BIVA E H51 x fx EE General lillil 1999 Conditional For Format as Table Cell Styles F January $50,000 February March 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 133 Ch. 20 Template