Excel Homework

profileAbdullah99
ITEC_Abdullah_Lab2.xlsx
Home>Information Systems homework help>Excel Homework

BudgetMain

Eagle Corporation
Pro-forma Budget
2019 2020 2021 2022 2023
Revenues
Universities $ 772,190.00 $ 849,409.00 $ 934,349.90 $ 1,027,784.89 $ 1,130,563.38
Government $ 1,598,050.00 $ 1,757,855.00 $ 1,933,640.50 $ 2,127,004.55 $ 2,339,705.01
Institutions $ 1,651,080.00 $ 1,816,188.00 $ 1,997,806.80 $ 2,197,587.48 $ 2,417,346.23
Businesses $ 15,708,570.00 $ 17,279,427.00 $ 19,007,369.70 $ 20,908,106.67 $ 22,998,917.34
Total Revenues $ 19,729,890.00 $ 21,702,879.00 $ 23,873,166.90 $ 26,260,483.59 $ 28,886,531.95
Direct Costs
Universities
Government
Institutions
Businesses
Cost of Goods Sold
Gross Profit
Administrative Expenses
Human Resources
Travel
Office Equipment
Office Supplies
General & Administrative
Subtotal Admin Expenses
Depreciation
Total indirect Expenses
Profit (Loss) Before Taxes
Minus Taxes
Profit (Loss) After Taxes
Ratios
Profit (after tax) to Revenues
Profit (after tax) Annual Increase/Decrease

Revenues

ClientID Client Category Client Quantity Blue Products Sold Quantity Red Products Sold Revenue Cost of Goods Sold
AU Universities American University 400 700 $ 199,400.00000 $ 59,485.00000
COMP Businesses Best Buy 1000 0 $ 110,000.00000 $ 34,210.00000
DAT Businesses Salesforce.com 3075 25 $ 343,800.00000 $ 106,831.50000
DOM Businesses District Taco 700 9,000 $ 2,075,000.00000 $ 612,817.00000
FED Businesses FedEx 1677 4,500 $ 1,183,470.00000 $ 351,805.17000
GMU Universities George Mason University 424 1,800 $ 446,240.00000 $ 132,279.04000
IMF Institutions IMF 2788 700 $ 462,080.00000 $ 141,178.48000
IRS Government Internal Revenue Service 2455 0 $ 270,050.00000 $ 83,985.55000
KI Businesses Lyft 4390 2,550 $ 1,049,000.00000 $ 317,028.40000
LOL Businesses Samsung 1950 19,000 $ 4,432,500.00000 $ 1,309,879.50000
REM Businesses Remax Realtors 5500 13,300 $ 3,557,600.00000 $ 1,058,374.00000
SMD Government State of Maryland 4000 4,000 $ 1,328,000.00000 $ 398,560.00000
SWA Businesses Southwest Airlines 2300 9,400 $ 2,339,800.00000 $ 693,725.00000
TIC Businesses StubHub 1700 700 $ 342,400.00000 $ 103,958.00000
TRS Businesses Travelocity 2500 0 $ 275,000.00000 $ 85,525.00000
UM Universities University of Maryland 1100 25 $ 126,550.00000 $ 39,266.75000
WB Institutions The World Bank 9,800 500 $ 1,189,000.00000 $ 367,973.00000

Expenses

ItemID ItemCategory BudgetItem Year 1 Notes
1.1 Human Resources Salaries $ 7,322,175.00 Calculated
1.2 Human Resources Benefits $ 2,050,209.00 % of Salaries
1.3 Human Resources Bonuses $ 658,995.75 % of Salaries
2.1 Travel Business Trips $ 560,000.00 Fixed amount/employee
2.2 Travel Local Travel $ 300,000.00 Fixed amount/employee
3.1 Office Equipment Computing devices $ 120,000.00 Fixed amount/employee
3.2 Office Equipment Furniture $ 200,000 Given
3.3 Office Equipment Networking $ 100,000 Given
4.1 Office Supplies Paper clips, etc. $ 80,000 Given
4.2 Office Supplies Coffee, water $ 40,000 Given

Employees

EmployeeID Emp Name DeptName City State Salary SalaryIncrease NewSalary
11630 Andrew House FIN Redmond WA $150,000 $7,500 $157,500
12279 Ashok Vemuri HR Pittsburgh PA $95,000 $4,750 $99,750
16142 Abigail Johnson ACCT Folsom CA $175,000 $8,750 $183,750
17189 Andrew Wilson FIN New York NY $215,000 $10,750 $225,750
18272 Hans Vestberg PROD Washington DC $180,000 $9,000 $189,000
22099 Abidali Neemuchwala ACCT Pittsburgh PA $210,000 $10,500 $220,500
23806 C Vijayakumar PROD Ashburn VA $150,000 $7,500 $157,500
30463 Hiroshi Okuda InfoTech Ashburn VA $200,000 $10,000 $210,000
31112 Brian L. Roberts MKT Armonk NY $150,000 $7,500 $157,500
33244 Andrew Rosen FIN New York NY $121,000 $6,050 $127,050
34939 Brian Moynihan MKT Armonk NY $235,000 $11,750 $246,750
35896 Al-Waleed bin Talal ACCT Folsom CA $155,000 $7,750 $162,750
38122 Carlos Ghosn PROD Washington DC $45,500 $2,275 $47,775
39398 Brian Krzanich MKT Folsom CA $210,000 $10,500 $220,500
43652 Amir Farmanesh ACCT Washington DC $191,000 $9,550 $200,550
45721 André Bier Gerdau Johannpeter FIN Folsom CA $77,000 $3,850 $80,850
47159 Anil Ambani FIN Folsom CA $185,000 $9,250 $194,250
47853 Joel Klein PROD Folsom CA $84,000 $4,200 $88,200
48154 Muriel Cohen FIN New York NY $62,000 $3,100 $65,100
52267 Henry McKinnell InfoTech Armonk NY $130,000 $6,500 $136,500
55342 Carlos Slim Helú PROD Redmont WA $230,000 $11,500 $241,500
55502 Bob Dudley MKT Ashburn VA $175,000 $8,750 $183,750
58282 Bill McDermott MKT Pittsburgh PA $220,000 $11,000 $231,000
65499 Bernie Ecclestone MKT Ashburn VA $190,000 $9,500 $199,500
67529 Ho Ching InfoTech New York NY $140,000 $7,000 $147,000
73351 Alan Joyce ACCT Armonk NY $210,000 $10,500 $220,500
80599 Ben van Beurden MKT Ashburn VA $190,000 $9,500 $199,500
83464 Herbert Fisk Johnson III InfoTech Ashburn VA $200,000 $10,000 $210,000
85172 Andrew Witty FIN Redmont WA $180,000 $9,000 $189,000
88722 Andrew Mackenzie FIN Armonk NY $180,000 $9,000 $189,000
88994 Barry Meyer HR Armonk NY $198,000 $9,900 $207,900
89027 Howard Schultz InfoTech New York NY $255,000 $12,750 $267,750
92093 Antony Jenkins HR Washington DC $310,000 $15,500 $325,500
92595 Indra Nooyi PROD Folsom CA $170,000 $8,500 $178,500
93200 Helge Lund PROD New York NY $200,000 $10,000 $210,000
94921 Andrew Liveris FIN New York NY $140,000 $7,000 $147,000
96467 André Lacroix FIN New York NY $165,000 $8,250 $173,250
97432 Carol Meyrowitz PROD New York NY $200,000 $10,000 $210,000
98317 A M Naik ACCT Armonk NY $215,000 $10,750 $225,750
99560 Bharat Desai MKT Folsom CA $185,000 $9,250 $194,250

DeptSalaries

Dept Deptnewsalaries
ACCT $ 1,213,800
FIN $ 1,548,750
HR $ 633,150
MKT $ 1,632,750
PROD $ 1,322,475
InfoTech $ 971,250
Total $ 7,322,175

Depreciation

Depreciation -- Double Declining
Cost
Salvage
Life
Year 2019 2020 2021 2022 2023
Period 1 2 3 4 5
Depreciation Value

Parameters

Description Value Base
Annual Revenue Increase, Universities 10.0% Prior Year Revenue
Annual Revenue Increase, Government 15.0% Prior Year Revenue
Annual Revenue Increase, Institutions 8.0% Prior Year Revenue
Annual Revenue Increase, Businesses 12.0% Prior Year Revenue
Price of Blue Product $ 110.00 per unit
Price of Red Product $ 222.00 per unit
Cost of Blue Product (COGS) $ 34.21 per unit
Cost of Red Product (COGS) $ 65.43 per unit
Salary Increase, Year 1 5% Old Salary
Annual Expense Increase, Years 2-3 6% Prior Year Expense
Indirect Expenses growth rate 5%
Benefit Rate 28% Salaries
Bonus Rate 9% Salaries
Business Trip Expenses $14,000 Per Employee
Local Travel $7,500 Per Employee
Computers $3,000 Per Employee
Support Staff 30% Salaries
Misc Admin Expenses 10% All Other Expenses
Tax Rate 22% Profit
Number of Employees 40 COUNT from Employees
Annual COGS Increase, Universities 15.0% Prior Year COGS
Annual COGS Increase, Government 19.0% Prior Year COGS
Annual COGS Increase, Institutions 8.0% Prior Year COGS
Annual COGS Increase, Businesses 14.0% Prior Year COGS

&A

Charts

Revenue By Year

2019 2020 2021 2022 2023 19729890 21702879 23873166.900000006 26260483.590000007 28886531.949000012