Topclass writer only
roebob7l6g
acc_550_hampshire_company_spreadsheet.xlsxPart I
| Requirement 1 | |||
| Units | Price | Totals | |
| Sales | X | $ | $ |
| Variable Costs | X | $ | $ |
| Fixed Costs | $ | ||
| Net Income | $ | ||
| Requirement 2 | |||
| Contribution Margin per Unit in Dollars = Selling Price – Variable Costs | |||
| Selling Price | Variable Costs | Contribution Margin per Unit | |
| Contribution Margin Ratio = Contribution Margin/Selling Price | |||
| Contribution Margin | Selling Price | Contribution Margin Ratio | |
| Requirement 3 | |||
| Break-Even Point = Fixed Costs / Contribution Margin | |||
| Fixed Costs | Contribution Margin | Break-Even Point in Units (Rounded) | |
| Break-Even Point in Units X Selling Price per Unit = Break-Even Point Sales | |||
| Break-Even Point in Units | Selling Price per Unit | Break-Even Point in Sales (Rounded) | |
| Requirement 4A | |||
| Margin of Safety in Units = Current Unit Sales – Break-Even Point in Unit Sales | |||
| Current Unit Sales | Break-Even Point in Sales | Margin of Safety in Units | |
| Requirement 4B | |||
| Margin of Safety in Dollars = Current Sales in Dollars – Break-Even Point Sales in Dollars | |||
| Current Sales in Dollars | Break-Even Point in Dollars | Margin of Safety in Dollars | |
| Requirement 4C | |||
| Margin of Safety as a Percentage = Margin of Sales in Units / Current Unit Sales | |||
| Margin of Safety in Units | Current Unit Sales | Margin of Safety Percentage | |
| Requirement 5 | |||
| Degree of Operating Leverage = Contribution Margin / Operating Income | |||
| Contribution Margin | Operating Income | Operating Leverage | |
| Requirement 6 | |||
| Units | $ Per Unit | Totals | |
| Sales | X | $ | $ |
| Variable Costs | X | $ | $ |
| Fixed Costs | $ | ||
| Net Income | $ | ||
| Operating Leverage | Times % Increase | Increase would be XX% | |
| Prior Income | $ | From Part 1 | |
| Increase | $ | Prior Income X XX% Above | |
| Total | $ | ||
| Requirement 7 | |||
| Targeted Income = (Fixed Costs + Target Income) / Contribution Margin | |||
| Fixed Costs + Target Income | Divided by Contribution Margin | # of Units (Rounded) | |
| Fixed Costs | $ | ||
| Target Income | $ | ||
| Total | $ | $ | X |
| # of Units Above X $ Per Unit | |||
| Proof | Revenue | XX,XXX X $XX.XX | $ |
| Variable Costs | XX,XXX X $X.XX | $ | |
| Contribution Margin | $ | ||
| Fixed Costs | $ | ||
| Net Income | $ | ||
| Requirement 8 | |||
| Sales Mix | |||
| Current | Specialty | Total | |
| Expected Sales Units | X | X | |
| Revenue = Sales X Price | $ | $ | $ |
| Variable Costs X Units | $ | $ | $ |
| Contribution Margin | $ | $ | $ |
| Fixed Costs | $ | $ | $ |
| Operating Income | $ | ||
| Prior Net Income From Requirement 1 | $ | ||
| Additional Operating Income | (Operating Income Above Less Prior Income) | $ | |
| Decision With Explanation |
Part II
| Requirement 1 | ||||||
| Hampshire Company | ||||||
| Variable Costing Income Statement | ||||||
| Units | $ | |||||
| Sales | X | $ | $ | |||
| Variable Cost of Goods Sold: | ||||||
| Beginning Inventory | $ | |||||
| Direct Materials | X | $ | $ | |||
| Direct Labor | X | $ | $ | |||
| Manufacturing Overhead | X | $ | $ | |||
| Total Variable Costs | $ | |||||
| Cost of Good Available for Sale | $ | |||||
| Deduct Ending Inventory | X | $ | $ | |||
| Variable Costs of Goods Sold | $ | |||||
| Variable Selling Costs | X | $ | $ | $ | ||
| Contribution Margin | $ | |||||
| Fixed Costs: | ||||||
| Fixed Manufacturing Costs | $ | |||||
| Fixed Administrative Costs | $ | |||||
| Operating Income | $ | |||||
| Requirement 2 | ||||||
| Hampshire Company | ||||||
| Absorption Costing Income Statement | ||||||
| Units | $ | |||||
| Sales | X | $ | $ | |||
| Variable Cost of Goods Sold: | ||||||
| Beginning Inventory | $ | |||||
| Direct Materials | X | $ | $ | |||
| Direct Labor | X | $ | $ | |||
| Manufacturing Overhead | X | $ | $ | |||
| Total Variable Costs | $ | |||||
| Allocated Fixed Manufacturing Costs | X | $ | $ | |||
| Cost of Good Available for Sale | $ | |||||
| Deduct Ending Inventory | X | $ | $ | |||
| Costs of Goods Sold | $ | |||||
| Gross Margin | $ | |||||
| Fixed Costs: | ||||||
| Variable Selling Costs | X | $ | $ | |||
| Fixed Administrative Costs | $ | |||||
| Operating Income | $ | |||||
Part III
| Requirement 1 | |||||
| Price Variances: | |||||
| (Actual Price – Standard Price) X Actual Quantity | |||||
| Actual | Standard | Actual Quantity | Variance | Favorable or Unfavorable | |
| Cloth | $ | $ | X | $ | |
| Handle Assembly | $ | $ | X | $ | |
| Labor Price Variance | $ | $ | X | $ | |
| Requirement 2 | |||||
| Efficiency Variances: | |||||
| (Actual Quantity of Input Used – Standard Quantity of Input Allowed for Actual Output) X Budgeted Price of Input | |||||
| Actual | Standard | Standard Price | Variance | Favorable or Unfavorable | |
| Cloth | X | X | $ | $ | |
| (1.5 Yards per Unit) | |||||
| Handle Assembly | X | X | $ | $ | |
| (1 per Unit) | |||||
| Labor | X | X | $ | $ | |
| (.20 per Unit) |
Part IV
| Cost Information From Instructions | |||
| Stick | Collapsible | ||
| Units Sold | 60,000 | 3,000 | |
| Selling Price | $12.50 | $14.00 | |
| Direct Material Cost Per Unit | $3.00 | $3.10 | |
| Direct Labor Cost Per Hour | $7.50 | $8.00 | |
| Variable MO | $0.40 | $0.40 | |
| Variable Selling Costs | $1.10 | $1.10 | |
| Labor Hours Per Unit | 0.2 | 0.2 | |
| Sales Orders | 120 | 1 | |
| Purchase Orders | 50 | 3 | |
| Production Runs | 45 | 6 | |
| Material Moves | 86 | 10 | |
| Machine Setups | 130 | 6 | |
| Machine Hours | 525 | 32 | |
| Inspections | 200 | 10 | |
| Shipments | 60 | 3 | |
| Activity Information from Instructions | |||
| Activity | Activity Cost | Activity Cost Driver | |
| Order Processing | $35,000 | Number of Sales Orders | |
| Purchasing | $36,000 | Number of Purchase Orders | |
| Material Handing | $28,000 | Material Moves | |
| Machine Setup | $14,000 | Machine Setups | |
| Production | $99,000 | Production Runs | |
| Assembly | $80,000 | Machine Hours | |
| Inspecting | $11,000 | Number of Inspections | |
| Shipping | $7,500 | Number of Shipments | |
| Requirement 1 | |||
| Activity | Total Costs | Quantity of Cost Allocation Base | Overhead Allocation Rate |
| Order Processing | $ | X | $ |
| Purchasing | $ | X | $ |
| Material Handing | $ | X | $ |
| Machine Setup | $ | X | $ |
| Production | $ | X | $ |
| Assembly | $ | X | $ |
| Inspecting | $ | X | $ |
| Shipping | $ | X | $ |
| Requirement 2 | |||
| Traditional Costing | |||
| Stick Umbrella | Collapsible Umbrella | Total | |
| Revenues | $ | $ | $ |
| Direct Materials | $ | $ | $ |
| Direct Labor | $ | $ | $ |
| Variable Overhead | $ | $ | $ |
| Variable Selling Costs | $ | $ | $ |
| Allocated Fixed Overhead | $ | $ | $ |
| Total Costs | $ | $ | $ |
| Operating Income | $ | $ | $ |
| Operating Income % | % | % | |
| Per Unit Operating Income | $ | $ | |
| Requirement 3 | |||
| Activity-Based Costing | |||
| Stick Umbrella | Collapsible Umbrella | Total | |
| Revenues | $ | $ | $ |
| Direct Materials | $ | $ | $ |
| Direct Labor | $ | $ | $ |
| Variable Overhead | $ | $ | $ |
| Variable Selling Costs | $ | $ | $ |
| Order Processing Costs | $ | $ | $ |
| Purchasing Costs | $ | $ | $ |
| Material Handing Costs | $ | $ | $ |
| Machine Setup Costs | $ | $ | $ |
| Production Costs | $ | $ | $ |
| Assembly Costs | $ | $ | $ |
| Inspecting Costs | $ | $ | $ |
| Shipping Costs | $ | $ | $ |
| Total Costs | $ | $ | $ |
| Operating Income | $ | $ | $ |
| Operating Income % | % | % | |
| Per Unit Operating Income | $ | $ | |
| Requirement 4 | |||
| Costs per Unit | Stick Umbrella | Collapsible Umbrella | |
| Traditional | $ | $ | |
| ABC | $ | $ | |
| Difference | $ | $ | |
| Requirement 5 | |||
