Baby Dolls	Teddy Bears	Toy Cars
Volume	200,000	125,000	225,000
Sales price	$3.50	$2.75	$3.15
Variable Costs	$2.05	$1.75	$2.45
Fixed Costs	$65,000	$125,000	$35,000

Target pretax income=0

Investment=$2 million

Capacity=1 million units

1.Create a spreadsheet that the managers can use for sensitivity analysis. Modify input data in the spreadsheet to answer the following parts of this problem. You might want to add cell references for percentage changes in prices, volumes, and costs.

2.Assume that the volume of dolls sold increases to 225,000 units, with no change in fixed or variable costs. What is the new pretax income? Does the number produced by your financial model appear to be reasonable? (Manually estimate the increase in pretax income if volume increases and fixed costs remain constant. Compare this figure to your spreadsheet result.)

3.Based on the original assumptions, what is the effect on pretax income of variable costs increase by 5 percent for each of the three product lines? Assume that nothing else changes.

4.Return to the original assumptions. Assume that a sales manager proposed a new advertising campaign to boost sales volume. The campaign would cost $30,000 and is estimated to increase the volume of each product as follows:

Baby doll sales increase by 20,000 units. Teddy bear sales increase by 7,500 units. Toy car sales increase by 30,000 units.

What would be the effect on pretax income if this plan were adopted?

5.Return to the original assumptions. Now assume that, due to competition, Toddler Toys must cut prices on each of its three products by 20 percent. In addition, a new advertising campaign costing $45,000 must be instituted to counteract bad publicity. Given these assumptions, what is the new breakeven point?

6.Return to the original assumptions. What would be the pretax income if Toddler Toys increased the price of all three products by 10 percent and the volume of each product line decreased by 5 percent?

7. Given the same assumptions as in part 6, how many units must toddler toys sell to earn a target pretax income of $100,000? a target pretax income of $150,000? a pretax return on investment (ROI) of 10 percent? (Hint: to determine the target pretax income, multiply 10 percent by the amount invested.)

li spreadsheet!--Saved to my Mac Search Sheet Home Insert Page Layout Data Review View Share ^ Trace Procedents Trece Cependents Remove Arrows . Define Name Calculate Now Insert AutoSurm Recenty Financiel Logic e Date & Lockup & Mt& More Show Error Farmulas Checking ptians Creatc trom Seloction CalculatioCalcuate Sheet lime Reference Trig Functicns A40xf Fxed costs 2 Input section Baby Doll: Teddy Bears Toy Cars 4 Price per unit Variable cost per urit 7 Fixed coas Desired arter-tax profit enter zeroor brekeven Bsby Dolls Teddy Bears Tay Cars Total Toys 12 Contribution Margin 13 Urit 14 Revenue B3 84 -C3 CA SUMIB14:014) ariable costs 16 Contribucion Margin 18 Cantrib. margin per unit 1S Contrib. maranratio -D16/01 16/B14 21 Eapected sles mi in unit 2 Epected ales mix in revenues R3/SE13 B14/5E1 -C3/5F13 C14 SE14 -3/SF13 D14/5E14 SUMIR21:021 21 Expected Income 25 Contribution margin 26 Fixed oss 27 Pr in me 22 Incometaxs s Aftr tax income -F15-F26 1 Preliminary CVP Calcultions 33 Fixed ossplusarget pretax profit Tatal Tav 35 CVPanalylsin units 3 CVP calculetion in urits 7 Revenue 3 ariable costs 35 Contribucion margin 40 Fixed cos.s 11 Pretieme 42 Incometaxs Baby Dolls Teddy Ecars Toy Cars C36 C4 -E39-E0 E41 B9 Sheet1 Sheet3 + 100% i spreadsheet1-Saved to my Mac Search Sheet Home Insert Page Layout Data Review View Share ^ Trace Procedents Trece Cependents Remove Arrows . Define Name Calculate Now Insert AutoSurm Recenty Financiel Logic e Date & Lockup & Mt& More Show Error Farmulas Check ng Creatc trom Seloction CalculatioCalcuate Sheet lime Reference Trig Functicns ptians A40 :| X x Fixed costs 3 Preliminary CVP Caloulations 32 Tareprea pralit for CVP analysis 33 Flxed osplustarct pretax protit 5 CUP analysis in units Baby Dalls Teddy Pears Tay Cars Total Tays ROUND e21 ES36,0) -036 04 ROUND 021 E$36,0) 036 04 CVP calculzation In urit 3 Revenue ariable costs 3 Contribution margin Flxed oss 41 Pretx income 42 Incmet L39440 43 Atter txincome eddy Bear 5 CVP analysisin reverses 46 CVP cale.lation in 47 Varlablc costs 48 Contributionmargin 4 Fied eoss sby Dolls lay Lars ROUNDISE546 22, ROUNDIB46 85/84,01 -ACLINDF33/F 19.01 SLIMIB47:D47J L46414? ROUND46 "CS/C4.0) Incometx 52 Atertaxincome Sheet1 Sheet3 + 100%