You run the production department of a company and your boss wants you to prepare an activity-based budget for the coming year to determine of a new product launch will be profitable. Make sales budget, production budget, direct materials budget, labor budget, and then a final P&L to determine if the product will be profitable. Sales Budget Sales by quarter: ? Q1:?1,000 units ? Q2:?10% increase over Q1 ? Q3:?15% increase over Q2 ? Q4:?20% increase over Q3 ? Unit Price:?$65/per unit Production Budget Sales:?These numbers should come from the Sales Budget Desired Ending Inventory ? Q1:?25% of sales from Q1 ? Q2:?25% of sales from Q2 ? Q3:?25% of sales from Q3 ? Q4:?25% of sales from Q4 New Total for Production = Sales + Desired Ending Inventory Beginning Inventory ? Q1:?200 units ? Q2:?Desired ending inventory from Q1 ? Q3:?Desired ending inventory from Q2 ? Q4:?Desired ending inventory from Q3 New Total for Production = Results from Sales + Desired Ending Inventory - Desired ending inventory for each quarter Direct Materials Budget Production Needs - comes from the Production Budget (final # once you have factored in desired and beginning inventories). Pounds of material required to produce one unit = 0.50 Pounds of total material required to meet Production Budget = Production Needs x Pound of Material per unit Desired Ending inventory of material ? Q1:?Production Needs for Q1 * 25% ? Q2:?Production Needs for Q2 * 25% ? Q3:?Production Needs for Q3 * 25% ? Q4:?Production Needs for Q4 * 25% Pounds of Material Required once desired ending inventory has been included: Production Needs for Q1 + Desired Ending Inventory Beginning Inventory ? Q1:?200 units ? Q2:?Desired ending inventory from Q1 ? Q3:?Desired ending inventory from Q2 ? Q4:?Desired ending inventory from Q3 New Total for Materials = Results from Sales + Desired Ending Inventory - Desired ending inventory for each quarter Direct Labor Budget Production Needs - comes from the Production Budget (final # once you have factored in desired and beginning inventories). Labor Hours Required to produce one unit: ? Q1:?1 hour ? Q2:?1 hour ? Q3:?1 hour ? Q4:?1 hour Number of hours to meet production needs: Production Needs x Labor Hours per quarter Cost of Labor per Hour - $20 each quarter Cost to meet production needs: Production Needs x Cost of Labor per Hour Final P&L Revenue - taken from sales budget Cost of Goods Sold ? Material Costs - taken from Direct Material Budget ? Labor Costs - taken from Direct Labor Budget ? Total COGS Revenue - Cost of Goods Sold = profit/loss for each quarter Calculate for gross profit margin Please provide a screenshot of your analysis or attach the Excel spreadsheet showing your work.

Activity Based Budget Prepare a sales, production, direct materials, and direct labor budget based on the following assumptions SALES BUDGET Sales Projections Q] Q2 Q4 Q4 1000 Growth 10% 15% 20% Sales Price per Unit $65.00 PRODUCTION BUDGET Sales - taken from Sales Budget Production - Desired Ending Inventory Q1 Q2 Q4 Q4 25% 25% of Sales 250 Q2: 25% of Q1 Sales Q3: 25% of Q2 Sales Q4: 25% on3 Sales Beginning Inventory Q1: 200 02: Desired Ending Inventory of Q1 Q3: Desired Ending Inventory of Q2 Q4: Desired Ending Inventory of Q3 DIRECT MATERIAL BUDGET Planned production needs: Figures from Production Budget Materials neededfunit: 0.5 lbs. Desired Ending Inventory: Q1: 262.5 Q2: 25% of Q1 Production Needs Q3: 25% of Q2 Production Needs Q4: 25% of 3 Production Needs Beginning Inventory Q1: 200 Q2: Desired Ending Inventory of Q1 Production Needs Q3: Desired Ending Inventory of Q2 Production Needs Q4: Desired Ending Inventory of Q3 Production Needs Cost to Material to Produce 1 unit 1.25 per pound of material DIRECT LABOR BUDGET Required Units Figures taken from Production Budget Direct Labor Hourst'Unit 1.4 hours Cost of Labora'I-Iour $20 hour SALES BUDGET Q 1 Sales Pricernit Sales PRODUCTION BUDGET Q1 Sales Desired Enc Total Beginning Inventory Total Production Needs DIRECT MATERIAL BUDGET Q1 Planned Production Needs (in units} Materials needed to produce a unit (assumption) Production Needs (lbs) Desired Ending Invcntoryr (in units} Total Needs (lbs) Less: Beginning Inventory of materials Needed Materials (lbs) Cost of materials to produce one unit DIRECT LABOR BUDGET Required Units to be Produced (taken from Production Budget} Direct Labor Hours required to produce one unit Production Needs Cost of Labor per hour Final P&L Projected Sales (Revenue) Cost of Goods Sold ~ Material Costs Labor Costs _ Gross Prot Calculation Projected Revenue - Projected Cost of Goods Sold l