Assuming that you are a financial analyst of Green Light Trading, Co. and you work for the CFO. Green Light Trading, Co. has sales offices located in several major cities in the northeast region and the headquarters is located in New York City. The headquarters has the following divisions: Oaccounting, treasury, OHR, Oadministrative, Ogeneral counsel's office, OCEO's office, and OCFO's office. This week your supervisor asks you to allocate the total expenses incurred to each corporation division. The basic information is listed in the Excel file below. With the information provided to you, your job is to please allocate the total headquarter expenses to the seven divisions in a way that you believe is the most reasonable. Please explain the allocation key used for each expense. Your deliverable should be the Excel file with proper remarks and explanations for the allocation keys. You are free to reorganize the Excel file and add rows or columns for your work. The following items must be included in your analysis: 1. Each expense must be allocated to one or more than one divisions. For example, the allocation may be based on head count, square footage of the office space occupied by the division, the amount of certain expenses incurred by the divisions, may be split evenly, or even absorbed by only one or two divisions. (20%) 2. The allocation percentage/method must be explained in the Excel file and supported by the information provided to you. Please justify the allocation basis that you choose. (40%) 3. There must be a summation of expenses allocated to the seven divisions and the total allocated expense must equal to the total expenses of the corporate headquarters. (20%) 4. Please organize your workbook in an understandable way with necessary notations. (20%) Wages Employee benefits Utilities Water Office supplies Office rent Printers rental Depreciation office equipment Depreciation office furniture Depreciation - company cars Charitable contribution) Courier Telephone Car expense Meals Entertainment Travel expenses Property insurance expense. Taxes and fees Property taxes CPA fees Legal fees Software licensing fees - HR Software licensing fees - Accounting Interest expense Income tax expense Total headquarter expenses Additioanl information: Number of employees Square footage of office space Accounting $ 1,341,131 $ 299,302 40,932 23,462 35,880 4,408 12,742 Accounting 23 2,370 Treasury 577,377 $ 132,311 11,767 20,665 5,760 2,425 4,362 Treasury 6 667 HR 1,117,233 $ 252,935 33,396 19,681 32,500 5,040 6,550 HR 10 1,896 General Admin. counsel's office CEO's office 856,883 $ 1,201,126 S 5,892,547 $ 194,724 273,659 1,304,785 26,125 18,954 13,000 1,849 6,320 Administrative 10 731 30,317 18,973 8,960 5,485 22,240 General counsel's office 8 1,016 49,332 27,611 55,800 214,718 158,040 CEO's office 6 1,097 CFO's office Total 2,524,394 $ 13,510,691 561,435 3,019,151 565,579 122,582 84,695 240,000 103,820 58,690 159,296 49,684 21,561 31,200 128,304 48,960 CFO's office 4 1,051 55,382 50,000 241,553 150,907 3,514 183,100 362,229 259,214 89,831 39,914 20,406 144,830 89,436 119,452 360,000 1,496,398 352,374 21,883,044 Total 67 8,828 There are two company cars. One IS used by the CEO's office and the other one is used by the CFO's office. There are five printers rented. One used by accounting. One used by general counsel's office. One by the CEO's office. One by the CFO's office. One shared by treasury, HR, and admin. Office equipment includes computers (and peripherals), routers, servers, and computer software such as Microsoft office, anti-virus software, and various applications. Property insurance expense is for the office building. Interest expense is for the various loans for the overall operations of the company. Income tax expense is based on the overall company pre-tax income. CPA fees are the fees paid to the auditor for financial auditing and for tax return preparation. The company's accounting and HR software are licensed from third-party vendors. The accounting software is used by the accounting department, and the HR software is used by the HR department. The company pays a law firm for regular legal services. The above expenses are limited to the expense of the headquarter divisions.