Green Light Trading is headquartered in the U.S. and its manufacturing facility is based in Bulgaria (a foreign subsidiary, or foreign sub). Green Light Trading sells toaster ovens at $18 per unit to customers in the U.S. and incurs unit selling expenses at $2.5 per unit. Green Light Trading sells 2 million units (Quantity) of toaster ovens per year. Its R&D expense is about $4.8 million per year and the amount of corporate expenses is $2.5 million per year. Green Light Trading, Co. currently only has manufacturing functions based in foreign sub while its sales, R&D, and headquarter functions are all based in the U.S. The foreign subsidiarys total manufacturing cost per unit (cost of goods sold, or COGS) is $3.5 and the cost of shipping (Shipping) the products from the foreign country to the U.S. is $1.0 per unit (recorded as the selling expenses of the foreign subsidiary). At the moment the U.S. parent company purchases finished products from the foreign subsidiary at a mark-up of $3.0 per unit. The shipping cost from the foreign country to the U.S. is added to the purchase price paid by the U.S. and the price paid by the U.S. (the transfer price) is $7.5 ($3.5 + $1.0 + $3.0 = $7.5). The foreign subsidiarys local general and administrative (Local Admin) expenses are $0.8 million per year. The corporate tax rate in the U.S. is 25% and the corporate tax rate in the foreign subsidiary is lower than the U.S. corporate tax rate.

Assume that you are an employee of Green Light Trading, Co. and you work for the tax director. Determine the transfer price, effective tax rates, gross margin, net profit margin, and pretax return on sales of the U.S. parent, the foreign subsidiary, and the consolidated level in the following scenarios: [Complete the Excel worksheet below by filling out the unknowns.]

1. Base case The U.S. parent pays a $3.0 mark-up
2. Change the transfer price to the foreign subsidiarys COGS (exclude shipping) plus 120% COGS, i.e., transfer price (TP) is (1+1.2)*COGS per unit + shipping cost per unit
3. Change the transfer price to allow the foreign subsidiary to have 50% pre-tax return on sales ( Hint: the new transfer price (TP) can be determined by solving the following equation:
   • [ (TP-COGS- Shipping)Quantity Local Admin ]/(TP*Quantity) = 50%, solve for TP
4. The foreign subsidiary does half of the R&D work and incurs R&D expenses of $2.4 million ($4.80 million*50%). As a result, the foreign subsidiary is able to charge a $8 mark-up per unit. Although U.S. parent's R&D expense is reduced by $2.4 million initially, the parent company incurs additional $100,000 R&D expense due to the overseeing activities needed (U.S. R&D is $4.8 million - $2.4 million + $100,000 = $2.5 million.)
5. U.S. parent manufactures domestically. In this scenario, the foreign subsidiary is dissolved. The U.S. cost of goods sold is estimated to be $9 per unit. In addition, the U.S. parent is able to reduce its headquarter expenses by $500,000. Selling expense and R&D expense are the same as in the base case.

Please help thank you!

Base case Scenario 1: Base case Scenario 2: Mark up by 120% COGS \begin{tabular}{|l|l|l|l|} \hline Cost of goods sold (foreign sub) & ? & (a) & \\ \hline Mark-up (120\% COGS) & ? & (b) & \\ \hline Shipping & ? & (c) \\ \hline TP & ? & (a)+(b)+(c) & \\ \hline \end{tabular} Base case Scenario 1: Base case Scenario 2: Mark up by 120% COGS \begin{tabular}{|l|l|l|l|} \hline Cost of goods sold (foreign sub) & ? & (a) & \\ \hline Mark-up (120\% COGS) & ? & (b) & \\ \hline Shipping & ? & (c) \\ \hline TP & ? & (a)+(b)+(c) & \\ \hline \end{tabular}