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, administrative, general 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%) General Accounting Total Wages Employee benefits $ 1,341,131 $ 299,302 Treasury 577,377 $ 132,311 HR 1,117,233 $ 252,935 Admin. counsel's office CEO's office 856,883 $ 1,201,126 $ 5,892,547 194,724 273,659 1,304,785 CFO's office $ 2,524,394 $ 13,510,691 561,435 Utillities 3,019,151 565,579 122,582 84,695 Water Office supplies Office rent 240,000 Printers rental 103,820 Depreciation - office equipment 58,690 159,296 Depreciation - office furniture Depreciation - company cars Charitable contribution 55,382 50,000 Courier 40,932 11,767 33,396 26,125 30,317 49,332 49,684 241,553 Telephone 23,462 20,665 19,681 18,954 18,973 27,611 21,561 150,907 3,514 Car expense Meals 35,880 5,760 32,500 13,000 8,960 55,800 31,200 183,100 Entertainment 4,408 2,425 5,040 1,849 5,485 214,718 128,304 362,229 259,214 Travel expenses 12,742 4,362 6,550 6,320 22,240 158,040 48,960 Property insurance expense 89,831 Taxes and fees 39,914 Property taxes 20,406 CPA fees Legal fees 144,830 89,436 119,452 Software licensing fees - HR Software licensing fees - Accounting 360,000 1,496,398 Interest expense Income tax expense 352,374 21,883,044 Total headquarter expenses Additioanl information: Accounting Treasury Administrative General counsel's office 10 Total 67 Number of employees 8 23 2,370 10 1,896 Square footage of office space 667 731 1,016 1,097 1,051 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. 6 HR CEO's office 6 CFO's office 4 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, administrative, general 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%) General Accounting Total Wages Employee benefits $ 1,341,131 $ 299,302 Treasury 577,377 $ 132,311 HR 1,117,233 $ 252,935 Admin. counsel's office CEO's office 856,883 $ 1,201,126 $ 5,892,547 194,724 273,659 1,304,785 CFO's office $ 2,524,394 $ 13,510,691 561,435 Utillities 3,019,151 565,579 122,582 84,695 Water Office supplies Office rent 240,000 Printers rental 103,820 Depreciation - office equipment 58,690 159,296 Depreciation - office furniture Depreciation - company cars Charitable contribution 55,382 50,000 Courier 40,932 11,767 33,396 26,125 30,317 49,332 49,684 241,553 Telephone 23,462 20,665 19,681 18,954 18,973 27,611 21,561 150,907 3,514 Car expense Meals 35,880 5,760 32,500 13,000 8,960 55,800 31,200 183,100 Entertainment 4,408 2,425 5,040 1,849 5,485 214,718 128,304 362,229 259,214 Travel expenses 12,742 4,362 6,550 6,320 22,240 158,040 48,960 Property insurance expense 89,831 Taxes and fees 39,914 Property taxes 20,406 CPA fees Legal fees 144,830 89,436 119,452 Software licensing fees - HR Software licensing fees - Accounting 360,000 1,496,398 Interest expense Income tax expense 352,374 21,883,044 Total headquarter expenses Additioanl information: Accounting Treasury Administrative General counsel's office 10 Total 67 Number of employees 8 23 2,370 10 1,896 Square footage of office space 667 731 1,016 1,097 1,051 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. 6 HR CEO's office 6 CFO's office 4