You will be using Excel to calculate Transfer Prices and provide both a Management Income Statement and
Question:
You will be using Excel to calculate Transfer Prices and provide both a Management Income Statement and a Legal Income Statement for the company’s US affiliate. This is an individual assignment. You must build the spreadsheet yourself with no templates or files from any other source.
BFC (Bottles from China) is a manufacturer of double wall vacuum insulated water bottles. They have recently opened their first US affiliate in Hawaii. They will be importing their bottles into the US, and must treat the sales as if they are arm’s length transactions using Transfer Pricing. A transfer price is the price at which divisions of a company transact with each other. The "arm's length principle" of transfer pricing states that the amount charged by one related party to another for a given product must be the same as if the parties were not related. An arm's length price for a transaction is therefore what the price of that transaction would be on the open market.2022 Key financial details and projections for BFC’s US affiliate are the following:
Unit Sales. Unit ASP Unit Std. Cost Unit GP Unit GP%
12 oz sport bottle 15,000 18.00 5.40 12.60 70.0
18 oz Sport Bottle 15,000 20.00 6.00 14.00 70.0
24 oz Sport Bottle 20,000 22.00 6.60 15.40 70%
32 oz Sport Bottle 25,000 25.00 5.00 20.00 80.0%
40 oz Sport Bottle 25,000 27.00 5.40 21.60 80.0%
64 oz Sport Bottle 25,000 32.00 6.40 25.60 80.0
Total 120,000
BFC has a US legal income tax rate of 42% and a management tax rate of 11%. Variable Selling Expenses are 10% of sales and Variable Distribution Expenses are 12% of sales. Fixed General & Administrative expenses are $95,000 annually. Traditionally, unrelated distributors earn 2.5% margin on the sale of similar water bottles. Required: Complete an Excel file that contains each product’s transfer price, and both the Management Income Statement and Legal Income Statement for the US affiliate.
1) Total Company US Management Income Statement (one Income Statement that includes all products)
2) Total Company US Legal Income Statement (one Income Statement that includes all products)
3) The Transfer Price for each product.