CASE 12: Baylee Byrd Playsets, Inc. Figure 2: Income Statement Baylee Byrd Playsets, Inc. Income Statement (Current Date) Sales Variable Expenses Marketing and Sales Labor Variable Overhead Variable Selling Variable Administrative Total Variable Expenses Contribution Margin Fixed Expenses Fixed Overhead Selling Expenses Administrative Expenses Depreciation Total Fixed Expenses Operating Income Income Taxes Net Income Figure 3: Ratios Ratios BEP BEP with Target Income Contribution Margin Ratio Operating Margin Ratio Net Margin Ratio Information Specifications The Income Analysis worksheet provides Mr. Byrd with information about his business's income, calculates several financial ratios, performs breakeven analysis, and displays one- variable data tables. Therefore, the Results section of the Income Analysis worksheet will have income statement, ratio, and data table result areas. As Figure 2 shows, the income statement section of the worksheet summarizes the business's revenues and expenses, allowing Mr. Byrd to examine the company's overall operating performance. As you study the income statement outline, you realize that many of your calculations will reference the data contained in the Input section of the worksheet, requiring Mr. Byrd to input the data only once. Mr. Byrd provides you with the formulas shown in Figure 4. 000 requires 117 CASE 12: Baylee Byrd Although he Caylee Byrd Playsets, Inc. me levels, you data table, the you pre remair three associated help feature Mr. Anal Mr. Byrd wants to examine the impact that va breakeven point. For instance, Mr. Byrd knows playsets in order to break even. He would like $30,000, $35,000, and $40,000 target income can change the target income cell value for ea recommend that he use a one-variable da target income values and their associated enabling Mr. Byrd to view and compare allur breakeven points at the same time. (You ma to review one-variable data tables at this point. that various target income levels have on the knows that a target income of $15,000 re vould like to see what impact $20,000, $25.000 incomes have on the breakeven point. Altho e for each of the desired target income leva able data table. By creating a one-variable data ta Ssociated breakeven points are arranged in a table mpare all the target income values and their associa ne. (You may wish to use your system s online help f 1. Figure 4: Required Formulas Income Analysis Worksheet Formulas Fixed Costs Revenue Per Unit - Variable Cost Per Unit Breakeven Point Fixed Costs + Target Income Revenue Per Unit - Variable Cost Per Unit Breakeven Point with Target Income Sales - Total Variable Expenses Contribution Margin Sales - Variable Cost Sales Contribution Margin Ratio Operating Income * Income Tax Rate Income Taxes (Assume a 35 percent tax rate) Operating Income - Income Taxes Net Income Net Income Net Sales Net Margin Operating Income Contribution Margin - Total Fixed Expenses Operating Margins Operating Income Net Sales Variable Cost Per Unit Total Variable Costs Number of Units Sold Byrd wants to see how different scenarios impact the business's net income. In addition current scenario, Mr. Byrd wants to evaluate two other possible scenarios. In the first wants to increase the number of units sold to 150, decrease revenue per unit to rease variable costs per unit by $20. (You can choose which variable cost to $950, and decrease variable costs per unit hy d scenario, he wants to increase the number of units sold to 100, reduce.) In the second scenario, he wants to increa increase revenue to $1,650 per unit, and increase 650 per unit, and increase labor by $50. Using Scenario Manager, 76 CASE 12: Baylee Byrd Playsets, Inc. you prepare the three scenarios. The first scenario uses the original values, and the remaining two scenarios use the data that Mr. Byrd has just given you. After you create the three scenarios, you generate a scenario summary report based on the three scenarios. Mr. Byrd needs answers to the following questions. Using your newly designed Income Analysis worksheet, provide Mr. Byrd with answers to his questions. 1. Mr. Byrd wants a net margin ratio of 15 percent. Using Solver, adjust the values for the revenue and number of units sold. Revenue per unit cannot exceed $1,100, the number of units sold cannot exceed 250, and total variable expenses cannot exceed $110,000. In order to have a net margin of 15 percent, how many playsets will Mr. Byrd need to sell? What price should he charge? Generate an answer report. (As a starting point for this answer, reset your worksheet's values back to the original values in Table 1, and then make the changes requested in this question.) 2. Assume that fixed overhead costs are $7,500, variable overhead is $375, labor is $200, and depreciation is $8,500. If Mr. Byrd wants a net income of $30,000, what price should Mr. Byrd charge for his playsets? How many playsets should Mr. Byrd sell? (As a starting point for this answer, reset your worksheet's values back to the original values in Table 1, and then make the changes requested in this question.) 3. Mr. Byrd wants a net income of $55,000. How many playsets should Mr. Byrd sell? What price should he charge? (As a starting point for this answer, reset your worksheet's values back to the original values in Table 1, and then make the changes requested in this question.) 4. Mr. Byrd needs a 3-D pie chart that compares the business's fixed costs. Implementation Concerns For this case, you will design a worksheet to facilitate Mr. Byrd's analysis of his business. When designing the worksheet, you will apply basic cell and worksheet formatting principles, create formulas, perform what-if analysis by using Goal Seek and Solver, create several scenarios, generate a chart, and create two one-variable data tables. Based on your what- if analysis, you will prepare several reports, including an answer report and a scenario summary report. al reports one-varcoal See Although you are free to work with the design of your worksheet, the worksheet should have a consistent, professional appearance. You should use proper formatting for the cells. For instance, dollar values should display with a dollar sign and be formatted to two decimal places. In several locations, the case references target income. Keep in mind that the target income does not reflect income taxes. Therefore, as it is used in this case, the target income is a better reflection of operating income, as opposed to net income. CASE 12: Baylee Byrd Playsets, Inc. Figure 2: Income Statement Baylee Byrd Playsets, Inc. Income Statement (Current Date) Sales Variable Expenses Marketing and Sales Labor Variable Overhead Variable Selling Variable Administrative Total Variable Expenses Contribution Margin Fixed Expenses Fixed Overhead Selling Expenses Administrative Expenses Depreciation Total Fixed Expenses Operating Income Income Taxes Net Income Figure 3: Ratios Ratios BEP BEP with Target Income Contribution Margin Ratio Operating Margin Ratio Net Margin Ratio Information Specifications The Income Analysis worksheet provides Mr. Byrd with information about his business's income, calculates several financial ratios, performs breakeven analysis, and displays one- variable data tables. Therefore, the Results section of the Income Analysis worksheet will have income statement, ratio, and data table result areas. As Figure 2 shows, the income statement section of the worksheet summarizes the business's revenues and expenses, allowing Mr. Byrd to examine the company's overall operating performance. As you study the income statement outline, you realize that many of your calculations will reference the data contained in the Input section of the worksheet, requiring Mr. Byrd to input the data only once. Mr. Byrd provides you with the formulas shown in Figure 4. 000 requires 117 CASE 12: Baylee Byrd Although he Caylee Byrd Playsets, Inc. me levels, you data table, the you pre remair three associated help feature Mr. Anal Mr. Byrd wants to examine the impact that va breakeven point. For instance, Mr. Byrd knows playsets in order to break even. He would like $30,000, $35,000, and $40,000 target income can change the target income cell value for ea recommend that he use a one-variable da target income values and their associated enabling Mr. Byrd to view and compare allur breakeven points at the same time. (You ma to review one-variable data tables at this point. that various target income levels have on the knows that a target income of $15,000 re vould like to see what impact $20,000, $25.000 incomes have on the breakeven point. Altho e for each of the desired target income leva able data table. By creating a one-variable data ta Ssociated breakeven points are arranged in a table mpare all the target income values and their associa ne. (You may wish to use your system s online help f 1. Figure 4: Required Formulas Income Analysis Worksheet Formulas Fixed Costs Revenue Per Unit - Variable Cost Per Unit Breakeven Point Fixed Costs + Target Income Revenue Per Unit - Variable Cost Per Unit Breakeven Point with Target Income Sales - Total Variable Expenses Contribution Margin Sales - Variable Cost Sales Contribution Margin Ratio Operating Income * Income Tax Rate Income Taxes (Assume a 35 percent tax rate) Operating Income - Income Taxes Net Income Net Income Net Sales Net Margin Operating Income Contribution Margin - Total Fixed Expenses Operating Margins Operating Income Net Sales Variable Cost Per Unit Total Variable Costs Number of Units Sold Byrd wants to see how different scenarios impact the business's net income. In addition current scenario, Mr. Byrd wants to evaluate two other possible scenarios. In the first wants to increase the number of units sold to 150, decrease revenue per unit to rease variable costs per unit by $20. (You can choose which variable cost to $950, and decrease variable costs per unit hy d scenario, he wants to increase the number of units sold to 100, reduce.) In the second scenario, he wants to increa increase revenue to $1,650 per unit, and increase 650 per unit, and increase labor by $50. Using Scenario Manager, 76 CASE 12: Baylee Byrd Playsets, Inc. you prepare the three scenarios. The first scenario uses the original values, and the remaining two scenarios use the data that Mr. Byrd has just given you. After you create the three scenarios, you generate a scenario summary report based on the three scenarios. Mr. Byrd needs answers to the following questions. Using your newly designed Income Analysis worksheet, provide Mr. Byrd with answers to his questions. 1. Mr. Byrd wants a net margin ratio of 15 percent. Using Solver, adjust the values for the revenue and number of units sold. Revenue per unit cannot exceed $1,100, the number of units sold cannot exceed 250, and total variable expenses cannot exceed $110,000. In order to have a net margin of 15 percent, how many playsets will Mr. Byrd need to sell? What price should he charge? Generate an answer report. (As a starting point for this answer, reset your worksheet's values back to the original values in Table 1, and then make the changes requested in this question.) 2. Assume that fixed overhead costs are $7,500, variable overhead is $375, labor is $200, and depreciation is $8,500. If Mr. Byrd wants a net income of $30,000, what price should Mr. Byrd charge for his playsets? How many playsets should Mr. Byrd sell? (As a starting point for this answer, reset your worksheet's values back to the original values in Table 1, and then make the changes requested in this question.) 3. Mr. Byrd wants a net income of $55,000. How many playsets should Mr. Byrd sell? What price should he charge? (As a starting point for this answer, reset your worksheet's values back to the original values in Table 1, and then make the changes requested in this question.) 4. Mr. Byrd needs a 3-D pie chart that compares the business's fixed costs. Implementation Concerns For this case, you will design a worksheet to facilitate Mr. Byrd's analysis of his business. When designing the worksheet, you will apply basic cell and worksheet formatting principles, create formulas, perform what-if analysis by using Goal Seek and Solver, create several scenarios, generate a chart, and create two one-variable data tables. Based on your what- if analysis, you will prepare several reports, including an answer report and a scenario summary report. al reports one-varcoal See Although you are free to work with the design of your worksheet, the worksheet should have a consistent, professional appearance. You should use proper formatting for the cells. For instance, dollar values should display with a dollar sign and be formatted to two decimal places. In several locations, the case references target income. Keep in mind that the target income does not reflect income taxes. Therefore, as it is used in this case, the target income is a better reflection of operating income, as opposed to net income