Excel Assignment:On January 1, Intergen, Inc., invests $200,000 for a 40 percent interest in Ryan, a new joint venture with two other partners, each investing $150,000 for 30 percent interests. Intergen plans to sell all of its production to Ryan, which will resell the inventory to retail outlets. The equity partners agree that Ryan will buy inventory only from Intergen. Also, Intergen plans to use the equity method for financial reporting.

During the year, Intergen expects to incur costs of $850,000 to produce goods with a final retail market value of $1,200,000. Ryan projects that, during this year, it will resell three-fourths of these goods for $900,000. It should sell the remainder in the following year.

The equity partners plan a meeting to set the price Intergen will charge Ryan for its production. One partner suggests a transfer price of $1,025,000 but is unsure whether it will result in an equitable return across the equity holders. Importantly, Intergen agrees that its total rate of return (including its own operations and its investment in Ryan) should be equal to that of the other investors' return on their investments in Ryan. All agree that Intergen's value including its investment in Ryan is $1,000,000.

Required

1.

Create an Excel spreadsheet analysis showing the following: (15 points) Projected income statements for Intergen and Ryan. Formulate the statements to do the following: Link Ryan's cost of goods sold to Intergen's sales (use a starting value of $1,025,000 for Intergen's sales). Link Intergen's equity in Ryan's earnings to Ryan's net income (adjusted for Intergen's gross profit rate Ryan's ending inventory 40 percent ownership percentage). Be able to change Intergen's sales and see the effects throughout the income statements of Ryan and Intergen. Note that the cost of goods sold for Intergen is fixed. The rate of return for the two 30 percent equity partners on their investment in Ryan. The total rate of return for Intergen based on its $1,000,000 value.

2.

What transfer price will provide an equal rate of return for each of the investors in the first year of operation? (Hint: Under Excel's Tools menu, use the Goal Seek or Solver capability to produce a zero difference in rates of return across the equity partners by changing the cell that contains Intergen's sales.) [5 points]

Create one Excel spreadsheet with two tabs. The first tab will answer all the parts of Question #1 and the second tab will answer Question # 2.

************************************************************************** On January 1, Intergen, Inc., invests $200,000 for a 40 percent interest in Ryan, a new joint venture with two other partners, each investing $150,000 for 30 percent interests. Intergen plans to sell all of its production to Ryan, which will resell the inventory to retail outlets. The equity partners agree that Ryan will buy inventory only from Intergen. Also, Intergen plans to use the equity method for financial reporting. During the year, Intergen expects to incur costs of $850,000 to produce goods with a final retail market value of $1,200,000. Ryan projects that, during this year, it will resell three-fourths of these goods for $900,000. It should sell the remainder in the following year. The equity partners plan a meeting to set the price Intergen will charge Ryan for its production. One partner suggests a transfer price of $1,025,000 but is unsure whether it will result in an equitable return across the equity holders. Importantly, Intergen agrees that its total rate of return (including its own operations and its investment in Ryan) should be equal to that of the other investors' return on their investments in Ryan. All agree that Intergen's value including its investment in Ryan is $1,000,000. Required 1. Create an Excel spreadsheet analysis showing the following: (15 points) Projected income statements for Intergen and Ryan. Formulate the statements to do the following: o Link Ryan's cost of goods sold to Intergen's sales (use a starting value of $1,025,000 for Intergen's sales). o Link Intergen's equity in Ryan's earnings to Ryan's net income (adjusted for Intergen's gross profit rate Ryan's ending inventory 40 percent ownership percentage). o Be able to change Intergen's sales and see the effects throughout the income statements of Ryan and Intergen. Note that the cost of goods sold for Intergen is fixed. The rate of return for the two 30 percent equity partners on their investment in Ryan. The total rate of return for Intergen based on its $1,000,000 value. 2. What transfer price will provide an equal rate of return for each of the investors in the first year of operation? (Hint: Under Excel's Tools menu, use the Goal Seek or Solver capability to produce a zero difference in rates of return across the equity partners by changing the cell that contains Intergen's sales.) [5 points] Create one Excel spreadsheet with two tabs. The first tab will answer all the parts of Question #1 and the second tab will answer Question # 2. Intergen's ownership percentage of Ryan Ryan's Income statement Sales Beginning inventory Purchases from Intergen Inventory remaining Ending inventory Cost of goods sold Net Income 40% $0 $1,025,000 25% $256,250 Income to Intergen-40% Income to two equity partners-60% Intra-entity transfer price Intergen's Income Statement $900,000 Sales Cost of goods sold Gross Profit Equity in Ryan's earnings Net income $768,750 $131,250 * (52500-(40%*256250*175000/1025000)) $1,025,000 $1,025,000 $850,000 $175,000 $35000* $210,000 $52,500 $78,750 Rate of Return Analysis Intergen Two outside equity partners Difference Intergen's ownership percentage of Ryan Ryan's Income statement Sales Beginning inventory Purchases from Intergen Inventory remaining Ending inventory Cost of goods sold Net Income Income to Intergen-40% Income to two equity partners-60% Investment base rate of return $1,000,000 21.00% $300,000 40% $0 $1,050,000 25% $262,500 Use goal Sleek or Solver under the Tools command to set CellD20 to zero by changing 26.25% cell f4 -5.25% Intra-entity transfer price Intergen's Income Statement $900,000 Sales Cost of goods sold Gross Profit Equity in Ryan's earnings Net income $787,500 $112,500 * (45000-(40%*262500*200000/1050000)) $45,000 $67,500 Rate of Return analysis Intergen Two outside equity partners Investment base Rate of Reurn $1,000,000 22.50% $300,000 22.50% 1050000 $1,050,000 $850,000 $200,000 25000* $225,000 Cell F4