The case provides key ratios, income statement and fixed and variable costs for Baylee Byrd Playsets. Mr. Byrd wants to do the following

1. Review the spreadsheet and examine all the formula in individual cells.

2. Examine the impact of various target income levels on the breakeven point, for example $45,000, $55,000, $65,000, $75,000, $85,000. $95,000. For this he wants you to use a one variable data table so that he can see all the results at the same time. Create an area below the ratios table to show the results of this task. Format your table.

3. Mr. Byrd wants to identify the breakeven point and breakeven point with target income at various price levels. Use a one variable data table to show this information. The price of the playsets ranges from $350 to $650 in $10 increments. Create an area to the left of the income statement. Format your table.

If Mr. Byrd does not want to sell more than 240 playsets, and wants to have $60,000 target income, what is range in which he should price his playsets. Highlight the rows that fulfill this criteria either manually or using conditional formatting.

4. Mr. Byrd wants to see how different scenarios impact the net income. In addition to the current scenario, he wants to investigate two additional scenarios. In the first scenario he wants to increase the number of units sold to 280, decrease revenue per unit to $450, and decrease variable costs per unit to $250. In the second scenario, he wants to decrease the number of units sold to 200, increase revenue to $550 per unit and increase labor by $25. Using scenario manager prepare the three scenarios and generate a scenario summary report.

Summary Section 240 Number of Units Sold $499.99 Revenue Per Unit $60,000.00 Desired Target Income sa 276.00 Total Fixed Costs Unit 70.60 Variable Costs S741 Marketing and Sales S75.00 Variable Labor S257.36 Variable Overhead S13.02 Variable Selling Variable Administrative 11.98 S364.17 Total Variable Expenses Fixed Costs s2326.06 Fixed Overhead S1,250.00 Selling Expenses $1,199.99 Administrative Expenses Income Statement For the Year Ended December 31, 2016 Sales $119,997.60 Variable Expenses $1,778.40 Marketing and Sales S12,900.00 Variable Labor 44,265.92 Variable overhead $3,124.80 Variable Selling $2,875.20 Total Variable Expenses $64,944.32 $55,053.28 Contribution Margin Fixed Expenses Fixed overhead $2,326.06 $1,250.00 Selling Expenses 31.199.99 Administrative Expenses Depreciation s 3500.00 Total Fixed Expenses S4677 1.23 Operating income Income Taxes $16,372.03 Net Income $30,405.20 Ratios BEP BEP With Target Income Contribution Margin Ratio 45.88% Operating Margin Ratio 38.98% 25.34% Net Margin Ratio