Applying Excel - Doto Tobles: Excel Worksheet (Por +of 2) Downioed the Applying Licel form below. Follow the tutorial on the first iab and then complete the qutitions ving Eucel tools tind formulas en the second tab: For 5cenariot or ate a two vanibie dia table to find the number of Uniss and Opelating incone foss for exch product a Scenatis 1 for as the tepiesouts Scenaro 2 relerence the Prosict b opeating income flews Jsing the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 50,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysis using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. Product B can vary between 3,000 units and a maximum of 8,000 units. Both Products A and B are manufactured in 1,000 -unit increments. The production level of Product C is the same each month at 2,000 units, Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table to determine the number of units for each product that should be produced (to the nearest thousand) to make each product line profitable. Based on your calculations above, which scenario creates the highest overall Operating income for the company? Tables Step-by-Step Tutorial: Data Tables are used to see the ellect of one or two varables on a formula. A Data Table allows you to see the outcomes of changing one of two vanables in one place so you can cxarnine a range of possible outcomes at a glance: To use the Data Toble What-It Analysis iool in Excel, we wil use the data and basic income Statement example starting in cell F5. Since data tables are based on the effect of changing varablets in a formula, the Income Statement should be sot up using formulas Complote the Incormo Statement using baxic Excel fommulas to roplace the question marks. Be sure to leave the formulas lor Contribution Margin and Operating Income (Loss). The incial Operating Inceme (Loss) should be $57,$00 bosed on the given intormation of 43,000 Units Sold, $3.00 Price per Unit, and $050 Variable Expense per Unal By seting up a Ono Vanatle Data Table, you will seo how changing thio Proe por Und will afsect the Operating lncome (Loss) across a rampe of prices. Use the tablo starting in cel 117 . When you woik weth a Ono Varable Data Table, you shoeld input all of your data in the first column, however, be sure to loave the first col blank in the first colume - Begin by entering $100 in cell F 18 (so you are leaving cell F17 btank) Contirue down the colume in increments of 50 so und you reach $3.50 in cell F23 - Neat, in cell G17 type =G14, Thes cell references the fornuta tesull that will chango based en the yarsbles you entered down column F Now that you have the shea of the data table created, you wili use the Data Tacle Aralysas fool - Highight from cot 117 through cet 023 - Then chick on the Data Tab, choove the What it Analyss button, and select Data Table A pop up wil open that askes for the "Pow ingut ceer and "Colurne input ceet". These input cels should reforence bock to the onginal given information whore the varables are goene lo change. In the One Vanable Data Table example yove set up, the Columin mput cetl should be entored Click on the ingut box for the "Column input celr, then clok oe coll od for the Price per und (t-xcel. will audomaticntly format your input as scto? - Once you click the OK butlos or press E Mer, all of the osicomes for Operatrug income (Losa) will display lor each Per Uni vanable it yourve done the corectey, the $1 op data wput will resuit it a loss of ($20,500) and the $350 will result in mosene of $70,000 The rwo Varabte Data Tatbe is set up samdar to the One Varabien Data Inble, With fwo minor Changes. The placensent of twe socond sut of data varables wat be placed across the log row and. the loemula ieference wit be ponifioned in the grast celt of ihe Dota Tarle Hello Company makes three different products. Due to the constraints of their manufacturing equipment and warehouse facility, the company is only able to produce, store, and sell a total of 50,000 units each month. The production of Products A and B varies each month; however, Product C is a special order for one customer who purchases the same number of units every month. Pete Davila, the CEO, has provided the Using the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product b on the following scenarios. (Hint. Don't forget that the warehouse can only hold up to 50,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysi using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. Product B can vary between 3,000 units and a maximum of 8,000 units. Both Produets A and B are manufactured in 1,000-unit increments. The production level of Product C is the same each month at 2,000 units. Using the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint. Don't forget that the warehouse can only hold up to 50,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating income, perform this analysis using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. Product B can vary between 3,000 units and a maximum of 8,000 units. Both Products A and B are manufactured in 1,000 -unit increments. The production level of Product C is the same each month at 2,000 units. Scenario 2. Pete wants each product line in the mix to be profitable. Use a One Variable Data Table to determine the number of units for each product that should be produced (to the nearest thousand) to make each product line profitable. Based on your calcuiations above, which scenario creates the highest overall Operating Income for the company? Applying Excel - Dotn Tables: Excet Worksheet (Port 1 of 2) for al vive probucte. Applying Excel - Doto Tobles: Excel Worksheet (Por +of 2) Downioed the Applying Licel form below. Follow the tutorial on the first iab and then complete the qutitions ving Eucel tools tind formulas en the second tab: For 5cenariot or ate a two vanibie dia table to find the number of Uniss and Opelating incone foss for exch product a Scenatis 1 for as the tepiesouts Scenaro 2 relerence the Prosict b opeating income flews Jsing the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint: Don't forget that the warehouse can only hold up to 50,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysis using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. Product B can vary between 3,000 units and a maximum of 8,000 units. Both Products A and B are manufactured in 1,000 -unit increments. The production level of Product C is the same each month at 2,000 units, Scenario 2: Pete wants each product line in the mix to be profitable. Use a One Variable Data Table to determine the number of units for each product that should be produced (to the nearest thousand) to make each product line profitable. Based on your calculations above, which scenario creates the highest overall Operating income for the company? Tables Step-by-Step Tutorial: Data Tables are used to see the ellect of one or two varables on a formula. A Data Table allows you to see the outcomes of changing one of two vanables in one place so you can cxarnine a range of possible outcomes at a glance: To use the Data Toble What-It Analysis iool in Excel, we wil use the data and basic income Statement example starting in cell F5. Since data tables are based on the effect of changing varablets in a formula, the Income Statement should be sot up using formulas Complote the Incormo Statement using baxic Excel fommulas to roplace the question marks. Be sure to leave the formulas lor Contribution Margin and Operating Income (Loss). The incial Operating Inceme (Loss) should be $57,$00 bosed on the given intormation of 43,000 Units Sold, $3.00 Price per Unit, and $050 Variable Expense per Unal By seting up a Ono Vanatle Data Table, you will seo how changing thio Proe por Und will afsect the Operating lncome (Loss) across a rampe of prices. Use the tablo starting in cel 117 . When you woik weth a Ono Varable Data Table, you shoeld input all of your data in the first column, however, be sure to loave the first col blank in the first colume - Begin by entering $100 in cell F 18 (so you are leaving cell F17 btank) Contirue down the colume in increments of 50 so und you reach $3.50 in cell F23 - Neat, in cell G17 type =G14, Thes cell references the fornuta tesull that will chango based en the yarsbles you entered down column F Now that you have the shea of the data table created, you wili use the Data Tacle Aralysas fool - Highight from cot 117 through cet 023 - Then chick on the Data Tab, choove the What it Analyss button, and select Data Table A pop up wil open that askes for the "Pow ingut ceer and "Colurne input ceet". These input cels should reforence bock to the onginal given information whore the varables are goene lo change. In the One Vanable Data Table example yove set up, the Columin mput cetl should be entored Click on the ingut box for the "Column input celr, then clok oe coll od for the Price per und (t-xcel. will audomaticntly format your input as scto? - Once you click the OK butlos or press E Mer, all of the osicomes for Operatrug income (Losa) will display lor each Per Uni vanable it yourve done the corectey, the $1 op data wput will resuit it a loss of ($20,500) and the $350 will result in mosene of $70,000 The rwo Varabte Data Tatbe is set up samdar to the One Varabien Data Inble, With fwo minor Changes. The placensent of twe socond sut of data varables wat be placed across the log row and. the loemula ieference wit be ponifioned in the grast celt of ihe Dota Tarle Hello Company makes three different products. Due to the constraints of their manufacturing equipment and warehouse facility, the company is only able to produce, store, and sell a total of 50,000 units each month. The production of Products A and B varies each month; however, Product C is a special order for one customer who purchases the same number of units every month. Pete Davila, the CEO, has provided the Using the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product b on the following scenarios. (Hint. Don't forget that the warehouse can only hold up to 50,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating Income, perform this analysi using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. Product B can vary between 3,000 units and a maximum of 8,000 units. Both Produets A and B are manufactured in 1,000-unit increments. The production level of Product C is the same each month at 2,000 units. Using the Data Table What-If Analysis tool in Excel, determine the Units and Operating Income (Loss) for each product based on the following scenarios. (Hint. Don't forget that the warehouse can only hold up to 50,000 units.) Scenario 1: Pete wants to find the mix of units that will result in the highest overall Operating income, perform this analysis using a two-variable data table. Product A can vary between 40,000 units and a maximum of 45,000 units. Product B can vary between 3,000 units and a maximum of 8,000 units. Both Products A and B are manufactured in 1,000 -unit increments. The production level of Product C is the same each month at 2,000 units. Scenario 2. Pete wants each product line in the mix to be profitable. Use a One Variable Data Table to determine the number of units for each product that should be produced (to the nearest thousand) to make each product line profitable. Based on your calcuiations above, which scenario creates the highest overall Operating Income for the company? Applying Excel - Dotn Tables: Excet Worksheet (Port 1 of 2) for al vive probucte
