The Chicken or The Egg: Hatching a New and Innovative Product
Cheng74- 
TheChickenorTheEgg--StudentExcel.xlsx1.a. DATA SHEET
| Chicken Sensations | ||||
| Panel A: Sales and Marketing Data | ||||
| Case Configuration: | ||||
| Pounds per case | 15 | Expected pounds per case | ||
| Bags per case | 12 | Expected number of bags per case | ||
| Forecasted Sales Volume (Cases): | ||||
| Cases Sold | Annual Sales Cases | |||
| First month sales volume | $65,000 | Initial sales forecast for the first month | ||
| Monthly sales growth - Year 1 | $15,000 | Sales growth per month for the first year | ||
| Months per year in Year 1 | 12 | Number of months in a year | ||
| Sales forecast error percentage | 25% | Percent possible sales forecast error | ||
| December sales volume | $230,000 | |||
| First year sales volume | $1,770,000 | |||
| Annual sales volume after Year 1 | $2,760,000 | |||
| Sales Price/Coupon Costs/Commission: | ||||
| Bags/Case | Per Case | |||
| Sales price to retailers (price per bag) | $3.00 | 12 | $36.00 | Based on sales price per bag and number of bags per case |
| Coupon costs (price per bag) | $0.20 | 12 | $2.40 | Based on coupon per bag and number of bags per case |
| Commission (percentage) | 6% | $2.16 | Percentage of sales price paid to brokers | |
| Slotting/Package Design/Sales Salaries: | ||||
| Total Cost ($) | Useful Life | Annual | ||
| Slotting costs | $6,000,000 | 1 | $6,000,000 | Expected slotting costs to be expensed over n years |
| Package design costs | $2,000,000 | 1 | $2,000,000 | Expected packaging design costs paid in the first year |
| Sales salaries | $400,000 | $400,000 | Expected annual additional sales and marketing salaries to be paid | |
| Panel B: Production and Cost Data | ||||
| Raw Material Percents/Costs (per lb): | ||||
| Percent | Cost/LB | Per Case ($) | ||
| Chicken | 20% | $2.00 | $0.40 | Expected cost of chicken per pound |
| Vegetables | 65% | $0.50 | $0.33 | Expected cost of vegetables per pound |
| Spaetzels | 15% | $0.15 | $0.02 | Expected cost of spaetzels per pound |
| Total (Must be 100%) | 100% | Total must be 100% | ||
| Weighted average raw material cost/lb. | $0.75 | Weighted average cost per pound | ||
| Pounds per case | $15.00 | |||
| Total Raw Material Cost per Case | $11.21 | Chicken Sensations cost per case (15 pounds per case) | ||
| Packaging Costs: | ||||
| Cost per Bag/Box | Qty/Case | Per Case | ||
| Bags | $0.20 | 12 | $2.40 | Based on price per bag and number of bags per case |
| Box | $0.30 | 1 | $0.30 | Expected price per box with one box per case |
| Total Packaging Cost per Case | $2.70 | Total packaging costs of bags and boxes per case | ||
| Total Direct Material Costs per Case | $13.9125 | Total of Chicken Sensations and packaging costs | ||
| Conversion Costs: | ||||
| Cost Per Lb ($) | Lbs/Case | Per Case ($) | ||
| Direct labor | $0.30 | 15 | $ 4.5000 | Based on labor costs per pound and number of pounds per case |
| Variable manufacturing overhead | $0.40 | 15 | $ 6.0000 | Based on var. mfg. overhead per pound and number of pounds per case |
| Total Coversion Cost per Case | $ 10.5000 | Total conversion costs per case | ||
| Total Variable Product Cost per Case | $ 24.4125 | Total direct material and conversion costs per case | ||
| Retrofit Costs: | ||||
| Total Cost ($) | Useful Life | Annual Depreciation | ||
| Retrofit costs | $2,000,000 | 5 | $400,000 | Expected retrofit costs and depreciable life |
| Chicken Sensations Year of Projected Perfomance Analyzed | ||||
| Selected Year of Analysis: Year 1 |
1.b. Forecasted CM Income Stmt
| 1.b.: Contribution Margin Format Income Statement | |
| Parson Vegetable Company | |
| Contribution Margin Income Statement | |
| Chicken Sensations | |
| Sales Cases | |
| Gross Salesb | $ - 0 |
| Variable Costs | |
| Total Variable Cost | $ - 0 |
| Contribution Margin | |
| Fixed Costs | |
| Total Fixed Cost | $ - 0 |
| Projected Operating Income | $ - 0 |
| Contribution Margin Ratio | ERROR:#DIV/0! |
| Profit Margin | ERROR:#DIV/0! |
| 1. b. Calculation Notes: | |
| Sales | |
| b Gross Sales (in Dollars) = (Sales Cases * Sales Price per Case) | |
| Sales Cases | |
| * Sales Price | |
| Gross Sales | $ - 0 |
| Variable Costs | |
| c Coupons = (Sales Cases * Coupon Cost per Case) | |
| Sales Cases | |
| * Coupon Cost per Case | |
| Annual Coupons (Year 1) | $ - 0 |
| Annual Coupons (After Year 1) | $ - 0 |
| dCommission = (Sales Cases * Commission per Case) | |
| Sales Cases | |
| * Commision Cost per Case | |
| Annual Commissions | $ - 0 |
| eVariable Product Cost = (Sales Cases * Variable Product Cost per Case) | |
| Sales Cases | |
| * Variable Product Cost per Case | |
| Annual Variable Product Cost | $ - 0 |
| Fixed Costs | |
| fSlotting Expense = (Total Cost / Years to Amortize) | 1327500 |
| Total Slotting Costs | |
| Years to Amortize | |
| Annual Slotting Expense (Year 1) | ERROR:#DIV/0! |
| Annual Slotting Expense (After Year 1) | $ - 0 |
| gPackage Design Expense = (Total Cost / Years to Amortize) | |
| Total Package Design Costs | |
| Years to Amortize | |
| Annual Packaging Expense (Year 1) | ERROR:#DIV/0! |
| Annual Packaging Expense (After Year 1) | $ - 0 |
| hRetrofit Expense = (Total Cost / Useful Life) | |
| Total Retrofit Costs | |
| Uesful Life | |
| Annual Retrofit Expense (Years 1 to 5) | ERROR:#DIV/0! |
| Annual Retrofit Expense (After Year 5) | $ - 0 |
1. c. Breakeven
| 1.c. Breakeven Analysis | ||
| Breakeven point in Cases | ||
| a. Contribution Margin per Case = (Selling Price per Case - Variable Cost per Case) | ||
| Sales Price | ||
| Variable Cost per Case | ||
| Total Variable Cost | $ - 0 | |
| Contribution Margin per Case | $ - 0 | |
| b. Total Fixed Costs | ||
| $ - 0 | ||
| $ - 0 | ||
| $ - 0 | ||
| $ - 0 | ||
| Total Fixed Costs | $ - 0 | |
| c. Breakeven point in Cases = Fixed costs / Contribution Margin per Case | ||
| $ - 0 | ||
| $ - 0 | ||
| Breakeven Sales Cases (Rounded) | ERROR:#DIV/0! | Cases |
| d. Breakeven point is Sales Dollars = (Breakeven Cases * Selling Price per Case) | ||
| Breakeven Cases | ERROR:#DIV/0! | |
| * Selling Price per Case | $ - 0 | |
| Breakeven Sales Dollars | ERROR:#DIV/0! | |
| Breakeven point in Sales Dollars | ||
| a. Contribution Margin Ratio = (Contribution Margin / Sales ) | ||
| Per Case | Total | |
| Contribution Margin | $ - 0 | $ - 0 |
| / Selling Price | $ - 0 | $ - 0 |
| Contribution Margin Ratio | ERROR:#DIV/0! | ERROR:#DIV/0! |
| b. Breakeven Sales Dollars = (Fixed Costs/Contribution Margin Ratio) | ||
| Fixed Costs | $ - 0 | |
| / Contribution Margin Ratio | ERROR:#DIV/0! | |
| Breakeven Sales Dollars (Rounded) | ERROR:#DIV/0! | |
| c. Breakeven Sales Cases = (Breakeven Sales Dollars / Selling Price per Case) | ||
| Breakeven Sales Dollars | ERROR:#DIV/0! | |
| / Selling Price per Case | $ - 0 | |
| Breakeven Sales Cases (Rounded) | ERROR:#DIV/0! | Cases |
1. d. Margin of Safety
| 1. d. Margin of Safety | ||
| Margin of Safety = (Expected Sales - Breakeven Sales) | ||
| Cases | Dollars | |
| Expected Sales | - 0 | $ - 0 |
| - Breakeven Sales | ERROR:#DIV/0! | ERROR:#DIV/0! |
| Margin of Safety | ERROR:#DIV/0! | ERROR:#DIV/0! |
| Margin of Safety (Dollars) = (Margin of Safety Cases * Selling Price per Case) | ||
| Margin of Safety in Cases | ERROR:#DIV/0! | |
| *Selling Price per Case | $ 36.00 | |
| Margin of Safety in Dollars | ERROR:#DIV/0! | |
| Margin of Safety Ratio = (Margin of Safety / Expected Sales) | ||
| Cases | Dollars | |
| Margin of Safety | ERROR:#DIV/0! | ERROR:#DIV/0! |
| / Expected Sales | - 0 | $ - 0 |
| Margin of Safety Ratio | ERROR:#DIV/0! | ERROR:#DIV/0! |
1e Sensitivity Analysis Results
| 1.e.: Sensitivity Analysis Results | |||
| Parson Vegetable Company | Parson Vegetable Company | ||
| Sensitivity Analysis Results | Sensitivity Analysis Results | ||
| Chicken Sensations | Chicken Sensations | ||
| Pessimistic | Original | Optimistic | |
| Sales Price per Case | Sales Price per Case | ||
| Total Variable Cost per Casea | Total Variable Cost per Case | ||
| Sales in Cases | Sales in Cases | ||
| Total Fixed Costs | Total Fixed Costs | ||
| Operating Incomeb | Operating Income | ||
| Contribution Margin Ratioc | Contribution Margin Ratio | ||
| Profit Margind | Profit Margin | ||
| Breakeven Sales (in Cases)e | Breakeven Sales (in Cases) | ||
| Margin of Safety (in Cases)f | Margin of Safety (in Cases) | ||
| Breakeven Sales (in Dolllars)e | Breakeven Sales (in Dolllars) | ||
| Margin of Safety (in Dollars)f | Margin of Safety (in Dollars) | ||
| Margin of Safety Ratiog | Margin of Safety Ratio | ||
| 1. e. Calculation Notes: | |||
| a Total Variable Costs per Case | |||
| Coupon Cost per Case | |||
| Commission Cost per Case | |||
| Variable Product Cost per Case | |||
| Total Variable Costs per Case | $ - 0 | ||
| bOperating Income | |||
| Sales Price per Case | $ - 0 | ||
| Less: Total Variable Cost per Casea | $ - 0 | ||
| Contribution Margin per Case | $ - 0 | ||
| * Sales in Cases | - 0 | ||
| Contribution Margin | $ - 0 | ||
| Less: Fixed Costs | $ - 0 | ||
| Operating Income | $ - 0 | ||
| cContribution Margin Ratio = Contribution Margin / Sales | |||
| Per Case | Cases | Total | |
| Contribution Margin | $ - 0 | - 0 | - 0 |
| / Sales | $ - 0 | - 0 | - 0 |
| = Contribution Margin Ratio | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| dProfit Margin = Operating Income / Sales | |||
| Per Case | Cases | Total | |
| Operating Income | ERROR:#DIV/0! | - 0 | - 0 |
| / Sales | $ - 0 | - 0 | - 0 |
| = Profit Margin | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| eBreakeven Sales = Total Fixed Costs / Contribution Margin (Per Case or Ratio) | |||
| Cases | Sales Price / Case | Sales Dollars | |
| Total Fixed Costs | $ - 0 | - 0 | |
| / Contribution Margin (Per Case, Ratio) | $ - 0 | ERROR:#DIV/0! | |
| = Breakeven Sales (in Cases, in Dollars) | ERROR:#DIV/0! | $ - 0 | ERROR:#DIV/0! |
| fMargin of Safety = Current Sales - Breakeven Sales | |||
| Cases | Sales Price / Case | Sales Dollars | |
| Current Sales | - 0 | $ - 0 | $ - 0 |
| - Breakeven Sales | ERROR:#DIV/0! | $ - 0 | ERROR:#DIV/0! |
| = Margin of Safety | ERROR:#DIV/0! | $ - 0 | ERROR:#DIV/0! |
| gMargin of Safety Ratio = Margin of Safety / Sales | |||
| Cases | Sales Price / Case | Sales Dollars | |
| Margin of Safety | ERROR:#DIV/0! | $ - 0 | ERROR:#DIV/0! |
| / Sales | - 0 | $ - 0 | $ - 0 |
| = Margin of Safety Ratio | ERROR:#DIV/0! | ERROR:#DIV/0! | |
