Assume that you are a financial analyst of Green Light Trading, Co. and you work for the CEOs office. Green Light Trading, Co.s headquarters is located in New York City and it has sales offices located in the following cities: New York, Philadelphia, Washington D.C., Charlotte, and Boston. These sales offices main functions are maximizing the sales revenues and have basic local office administration functions. Sales revenues and cost of goods sold are booked under each sales office, and the main expenses incurred by these offices are the selling expenses. The main back-office functions such as accounting, treasury, headquarters, HR, legal, etc. are booked under the headquarters (or Corporate expenses). Currently Green Light Trading, Co.s Corporate expenses are allocated to each sales office based on their sales revenues.

The CEO of Green Light Trading, Co. has long been concerned about the low profitability and occasional losses of the company. After several cost reduction actions in Corporate, Green Light Trading, Co. is looking to reorganize its sales offices, hoping to increase its overall net profit margin. Particularly, the CEO has some concerns about the profitability of the Boston office, and she asks her staff to come up with plans of the reorganization. The basic financial information is listed in the Excel file below.

The proposals are as follows:

1. Plan A: Terminate the Boston office. Under this plan, all expenses incurred by the Boston office can then be eliminated. The projected result is that the New York office will be able to pick up 60% of Boston offices sales revenue as well as cost of goods sold, but Green Light Trading, Co. will lose the remaining Boston market. In addition, the New York offices selling expenses will increase by 40% from its original number, and its own local G&A expenses will increase by 40% from its original number. Green Light Trading, Co. does not expect that the businesses of other sales offices (except the New York office) will be affected by the closure of the Boston office. Lastly, the headquarter expense (or Corporate expenses) can be reduced by 10% (for all Corporate divisions) if the Boston office is closed.

2. Plan B: Open a new satellite sales office in Portland, NH. This proposal is suggested by Peter Griffin, the general manager of the Boston Office. Peter has been frustrated by Plan A because he thinks that that it is the Corporate expenses that resulted in the low overall profitability. He believes that to increase the profitability, Green Light Trading, Co. should increase its market coverage by opening another sales office and raising the amount of gross profits. He provides his projection of a new Portland office as a satellite office of the Boston office. According to his plan, the Portland office will bring in approximately $3.5 million gross profit, and will have selling expense of less than $1.8 million. However, Peter expects that the sales revenue and cost of goods sold of the Boston office will reduce by 15% as a result of the unavoidable migration of some existing customers to the Portland satellite office. Consequently, selling expenses of the Boston office will decrease by 15%. Peters plan is that the Boston office will provide most of the local G&A support to the new Portland office, but he commits to cut 25% of local G&A expense in the Boston office. In addition, Peter expects the Portland office will have local G&A expenses of below $150,000. Lastly, Peter expects that the Corporate expenses (specifically, salaries and wages of the Accounting department) will only increase by 10% with the establishment of the new Portland office.

You are tasked to simulate the net profits under both plans to help the CEO evaluate the reorganization proposals. Please first conduct the quantitative analysis using the information included in the Excel file, then write a report (two to three pages, using Microsoft Word) to be presented to the CEO. Include the following items in the report:

a. Based on the current data:

1. Prepare an overall income statement of Green Light Trading, Co. (assume the income tax rate is 25%) (5%)
2. Allocate the headquarter (or Corporate) expenses to each sales office. Explain the allocation key that you have chosen. For Corporate expenses that you believe that cannot or should not be allocated to the sale offices, please leave them under "Corporate". (10%)
3. Calculate the divisional profit and the net profit margin for each sales office based on your answer in 1.b above (10%). Which office is the weakest office in your calculation?

b. Based on Plan A:

1. Prepare the divisional profit report for the sales offices (ignore income tax) under Plan A. For example, the Boston offices numbers will be eliminated, the New York offices numbers and Corporate expenses will change according to the proposal, and the remaining sales offices numbers will remain the same. (5%)
2. Allocate the headquarter (or Corporate) expenses to each sales office. Explain the allocation key that you have chosen. For Corporate expenses that you believe that cannot or should not be allocated to the sale offices, please leave them under "Corporate". (10%)
3. Calculate the net profit margin of each sales office before and after the allocation of the headquarter (or Corporate) expenses. (5%) Also prepare an overall income statement of Green Light Trading, Co. based on Plan A. (5%) Which office is the weakest office in your calculation?

c. Based on Plan B:

1. Prepare the divisional profit report for each sales office (ignore income tax) under Plan B. For example, the numbers of Boston office, the New York office, and Corporate expenses will change according to the proposal, and the remaining sales offices' numbers will remain the same. (5%)
2. Allocate the headquarter (or Corporate) expenses to each sales office. Explain the allocation key that you have chosen. For Corporate expenses that you believe that cannot and should not be allocated to the sale offices, please leave them under "Corporate". (10%)
3. Calculate the net profit margin of each sales office before and after the allocation of the headquarter (or Corporate) expenses. (5%) Also prepare an overall income statement of Green Light Trading, Co. based on Plan B. (5%) Which office is the weakest office in your calculation?