Hi. May I please get some guidance/direction on how to do "Sensitivity Analyses" for this case study homework? I've attached the images of the Excel worksheets containing the data and other work I've already completed for reference. Thank you for your help.

Actvity:

Examine how robust year 1 results are to changes in projections for

(1) the sales volume of cases,

(2) the sales price per bag, and

(3) the cost per pound of chicken.

Assume that these amounts can change for three different projection levels as reported in Table 2:

(1) a pessimistic level, (2) the original level, and (3) an optimistic level.

6 Contribution Margin Income Statement Chicken Sensations 8 9 SALES (GROSS S) $ 63,720,000.00 10 SALES (CASES 1,770,000 CASES 11 12 LESS: VARIABLE COSTS 13 COUPON 4.248,000.00 $2.40/CASE - FIRST YEAR 14 COMMISSION $ 3,823,200.00 6% BROKERAGE COMMISSION 15 RAW MATERIALS 16 -CHICKEN 10,620,000.00 $6.00/CASE 17 VEGETABLES 8,628,750.00 $4.875/CASE 18 SPAETZELS 597,375.00 $0.3375/CASE 19 PACKAGING 20 -BAGS 4,248,000.00 $2.40/CASE 21 -BOX 531,000.00 $0.30/CASE 22 LABOR 23 --DIRECT LABOR 7,965,000.00 $4.50/CASE 24 --VAR. MFG OVERHEAD 10,620,000.00 $6.00/CASE 25 26 27 TOTAL VARIABLE COST $ 51.281.325.00 28 29 VARIABLE COST: CONTRIBUTION MARGIN $ 12,438,675.00 30 CONTRIBUTION MARGIN RATIO 19.52% 31 32 33 LESS: FIXED COSTS 34 SLOTTING EXP 6,000,000.00 VIKKI - FIRST YEAR 35 PACKAGE DESIGN EXP 2,000,000.00 FIRST YEAR 36 SALES SALARIES (ADDITIONAL) 400,000.00 ADDT'L BODIES FOR CHICK SENS 37 RETROFIT COST EXP 400,000.00 #$2,000,000 /5 YRS (NO SALVAGE) 38 39 TOTAL FIXED COST S 8.800,000.00 40 41 42 NET INCOME S 3.638.675.00 43\"I | 6' HI CO l9 1.1:. Breakeven Analysis SALES (GROSS 3) SALES (CASES) VARIABLE COST: CONTRIBUTION MARGIN 63,720,00000 1,??0,000 (1435!; 12,438,6?5_00 \"3 FIXED COST: 8,800,000.00 11 12 13 CONTRIBUTION PER CASE 7.03 14 15 MEEVENGQSESALES 1.252324 (1.1323 SALES (GROSS 0) 63,720,00000 21 SALES (CASES) 1,770,000 0432.; 22 23 VARIABLE COST: CONTRIBUTION MARGIN 12,433,6?5_00 24 CONTRIBUTION RATIO 19.52% 25 FIXED COST: 0,000,000.00 26 2? 28 CONTRIBUTION PER CASE 7.03 29 3.1:. CASE SALES DOLLARS 45 000 042.69 3 1. d. Margin of Safety 4 5 a. Margin of Safety (Expected - BreakEven) 6 CASES 7 EXPECTED 1,770,000 8 BREAKEVEN 1,252,224 9 10 MARGIN OF SAFETY (CASES) 517,776 11 12 b. Margin of Safety (Dollars) = (Margin of Safety Cases * Selling Price per Case) 13 SALES $ 14 EXPECTED $63.720,000.00 15 B.E. CASE SALES DOLLARS $45,080,042.69 16 17 MARGIN OF SAFETY $18,639.957.31 18 19 c. Margin of Safety Ratio (Expected - BreakEven)/Expected 20 21 MARGIN OF SAFETY RATIO 29.25% 22 23Table 2: Sensitivity Analysis Levels Parson Vegetable Company Sensitivity Analysis Levels (Percent of Original Projections) Chicken Sensations Pessimistic Original Optimistic Sales Volume Case Forecast 75.00% 100.00% 125.00% Sales Price per Bag 90.00% 100.00% 110.00% Cost per Pound of Chicken 112.50% 100.00% 87.50%Table 3: Reported Sensitivity Analysis Results Parson Vegetable Company Sensitivity Analysis Results Chicken Sensations Pessimistic Original Optimistic Operating Income Contribution Margin Ratio Profit Margin Breakeven Sales (in Cases) Margin of Safety (in Cases) Margin of Safety Ratio6 Chicken Sensations Panel A: Sales and Marketing Data 8 Case Configuration: 9 Pounds per Case 15 Expected pounds per case 10 Bags per Case 12 Expected number of bags per case $36.00 11 Forecasted Sales Volume (Cases): Month YR 1 Cases Sold $ SALE YR 1 POST 12 Cases Sold Annual Sales 65,000 2,340,000 230,000 13 First Month Sales Volume 55,000 Initial sales forecast for the first month 80,000 2,880,000 230,000 14 Monthly Sales Growth Year 1 15,000 Sales growth per month for the first year 95,000 3,420,000 230,000 15 Months per year in Year 1 12 Number of months in a year 110,000 3,960,000 230,000 16 Sales Forecast Error Percentage 25% Percent possible sales forecast error 125,000 4,500,000 230,000 17 December Sales Volume $8,280,000.00 230,000 140,000 5,040,000 230,000 18 First Year Sales Volume $55,648,800.00 1,770,000 155,000 5,580,000 230,000 19 Annual Sales Volume After Year 1 $93,398,400.00 2,760,000 170,000 6,120,000 230,000 20 Sales Price/Coupon Costs/Commissions: 185,000 6,660,000 230,000 21 Bags/Case Per Case 10 200,000 7,200,000 230,000 22 Sales Price to Retailers ($ per bag) $3.00 12 S 36.00 Based on sales price per bag and number of bags per case 11 215,000 7,740,000 230,000 23 Coupon Costs ($ per bag) $0.20 12 S 2.40 Based on coupon per bag and number of bags per case 230,000 8,280,000 230,000 24 Commission (percentage 6.00% 2.16 Percentage of sales price paid to brokers 1,770,000 $ 63,720,000.00 2,760,000 25 Slotting/Package Design/Sales Salaries: 26 Total Cost ($) Useful Life Annual 6% COMMISSION $ (3,823,200.00) 27 Slotting Costs $6,000,000 1 $ 6,000,000 Expected slotting costs to be expensed over n years $0.20 COUPON/BAG $ (4,248,000.00) 28 Package Design Costs $2,000,000 $ 2,000,000 Expected packaging design costs paid in the first year SALES AFTER COMMISSION/COUPON $ 55,648,800.00 29 Sales Salaries $400,000 400.000 Expected annual additional sales and marketing salaries to be paid 30 31 Panel B: Production and Cost Data 32 Raw Material Percents/Costs (per lb): 33 Percent Cost/Pound Dollars/Case 34 Chicken 20% S 2.0000 S 6.00000 Expected cost of chicken per Chicken Sensations pound 35 Vegetables 65% S 0.5000 $ 4.87500 Expected cost of vegetables Chicken Sensations per pound 36 Spaetzels 15% S 0.1500 0.33750 Expected cost of spaetzels per Chicken Sensations pound 37 Total (Must be 100%) 100% Total must be 100% 38 Weighted Average Raw Material Cost $ 0.74750 Weighted average cost per pound of Chicken Sensations 39 Pounds per Case 15 40 Total Raw Material Cost per Case S 11.2125 Chicken Sensations cost per case (15 pounds per case)A B C D E 38 Weighted Average Raw Material Cost $ 0.74750 Weighted average cost per pound of Chicken Sensations 39 Pounds per Case 15 40 Total Raw Material Cost per Case 11.2125 Chicken Sensations cost per case (15 pounds per case) 41 Packaging Costs: 42 Cost per Bag Box (S) Otv/Case Dollars/Case 43 Bags $0.20 S 2.40 Based on price per bag and number of bags per case 44 Box $0.30 0.30 Expected price per box with one box per case 45 Total Packaging Cost per Case S 2.70 Total packaging costs of bags and boxes per case 46 Total Direct Material Costs per Case $ 13.9125 Total Chicken Sensations materials and packaging costs 47 48 Conversion Costs: 49 Cost/Pound Pounds/Case Dollars/Case 50 Direct Labor $0.30 S 4.5000 Based on labor costs per pound and number of pounds per case 51 Variable Manufacturing Overhead $0.40 6.0000 Based on var. mfge overhead per pound and number of pounds per case 52 Total Coversion Cost per Case S 10.5000 Total conversion costs per case 53 Total Variable Product Cost per Case $ 24.4125 Total direct material and conversion costs per case 54 55 Retrofit Costs: 56 Total Cost (S) Useful Life Annual 57 Retrofit Costs $2,000,000 5 $ 400.000 Expected retrofit costs and depreciable life 58 59 Chicken Sensations Year of Projected Perfomance Analyzed