# Question

Toddler Toy Company sells baby dolls, teddy bears, and toy cars. The managers established a preliminary budget using the following assumptions. They would now like to evaluate the sensitivity of budgeted results to different sets of assumptions.

REQUIRED

A. 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 may wish to add cell references for percentage changes in prices, volumes, and costs.

B. 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.)

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

D. 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?

E. Return to the original assumptions. Now assume that due to competition, Toddler Toys must cut prices on each of its three products by 20%. 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?

F. Return to the original assumptions. What would be the pretax income if Toddler Toys increases the price of all three products by 10% and the volume of each product line decreases by 5%?

G. Given the same assumptions as in part (F), 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%?

H. Spreadsheets for financial modeling allow sensitivity analysis of revenues, costs, and quantities such as estimated product volumes.

1. Explain why it is not possible to perfectly estimate revenues, costs, and quantities.

2. Explain how sensitivity analysis can help managers evaluate the pros and cons of alternatives.

3. Explain how manager bias might influence estimates of revenues, costs, andquantities.

REQUIRED

A. 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 may wish to add cell references for percentage changes in prices, volumes, and costs.

B. 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.)

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

D. 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?

E. Return to the original assumptions. Now assume that due to competition, Toddler Toys must cut prices on each of its three products by 20%. 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?

F. Return to the original assumptions. What would be the pretax income if Toddler Toys increases the price of all three products by 10% and the volume of each product line decreases by 5%?

G. Given the same assumptions as in part (F), 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%?

H. Spreadsheets for financial modeling allow sensitivity analysis of revenues, costs, and quantities such as estimated product volumes.

1. Explain why it is not possible to perfectly estimate revenues, costs, and quantities.

2. Explain how sensitivity analysis can help managers evaluate the pros and cons of alternatives.

3. Explain how manager bias might influence estimates of revenues, costs, andquantities.

## Answer to relevant Questions

The following information for Pet Palace, a large retail store that sells pet-related merchandise, was recorded for the first quarter. The store tracks merchandise according to product type. The category “Other” includes ...The Internet provides many resources to help small-business owners successfully manage their businesses. Resources include information about common techniques used for planning and managing operations.REQUIREDA. Why are ...Explain how managers decide which products in a sales mix to emphasize in the short term.The Feed Barn packages and distributes three grades of animal feed. The material cost per ton and estimated annual sales for each of the products as follows.The fixed cost of operating the machinery used to package all ...Waldon Company is considering dropping a product. The firm currently carries inventory valued at $4,500,000. Dropping the product would reduce inventory value to $1,600,000. Accounting records reveal that past warehousing ...Post your question

0