Skip to content

Instantly share code, notes, and snippets.

@stephlocke

stephlocke/detaildata.csv

Created July 17, 2017 14:43
Show Gist options
  • Save stephlocke/71821830c8177c0737d0556d76249197 to your computer and use it in GitHub Desktop.
Save stephlocke/71821830c8177c0737d0556d76249197 to your computer and use it in GitHub Desktop.
Download ZIP
Analysing the PASS budget
Raw
detaildata.csv
LineItem B2016 B2017 TblChange Total Net Title Type Dept IgnoreRow
Gain/Loss on Revaluation of Euro 0 0 3 FALSE FALSE FALSE Revenue Corporate Administration FALSE
Interest - Miscellaneous 11e3 11e3 3 FALSE FALSE FALSE Revenue Corporate Administration FALSE
Misc FY Adjustments 0 0 3 FALSE FALSE FALSE Revenue Corporate Administration FALSE
Unrealized Gain/Loss on Investments 0 0 3 FALSE FALSE FALSE Revenue Corporate Administration FALSE
Accounting - General 3500 5e3 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Annual Report 37 37 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Audit 25e3 25e3 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Authorize.Net Fees 1e3 1e3 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Bank Charges 9e3 9e3 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Custom Broker Expense 250 0 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Insurance 6107 6500 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Legal 2e4 15e3 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Incentive 170472.9 163683.71 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management & Overhead 920043.4 1080627 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Misc. Expense - Contract Vendor 2400 2400 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Office Supplies 500 250 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Postage/Shipping 9500 12500 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Printing 500 500 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Site Inspection - BAC 5500 0 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Site Inspection - Summit 3e3 15030 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Administration 182001.72 404617.21 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Finance 108828.45 95500.9 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Governance 46954.92 16367.61 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Miscellaneous 35590.6 53372.39 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Reporting 122498.4 129249.29 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Management - Registration 0 0 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Teleconference 50 100 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Training/Educational/Networking 0 3e3 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Travel - Misc. (Air/Land/Hotel) 6e3 9680 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Travel - Misc. (Meals) 1500 2160 4 FALSE FALSE FALSE Expense Corporate Administration FALSE
Sponsorship - MS Technology Platform 74e3 96480 5 FALSE FALSE FALSE Revenue Information Technology FALSE
Sponsorship - MS IOT 0 1e4 5 FALSE FALSE FALSE Revenue Information Technology FALSE
Computer Software/Equipment 5e3 7e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - VC/VE Platform 40734 40734 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - AtTask 24270 28e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Domain Registration 12100 3e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - DNN Modules 1500 1500 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Marketing Tool 29533.4 43e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Guidebook 19668.75 23e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT- Office 365 52930 54480 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Hosting 20748 42e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Governance 5710 5e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Community Emails) 11e3 2400 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Third Party Tools 5e3 3e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Salesforce 8482.5 18534.4 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (General) 255183.41 152825.17 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (24 HOP) 7868.54 0 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Chapters) 15069.33 0 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Summit) 39196.17 19886.12 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (VC) 3829.59 0 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Infastructure) 4617.39 41625.42 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (SQLSaturday) 42164.17 30645.11 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (BAC) 17060.06 0 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Help Desk) 1782.68 68547.43 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Volunteers) 6073.35 0 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Launch Events) 1919.71 0 6 FALSE FALSE FALSE Expense Information Technology FALSE
Management - IT (Portfolio Projects) 104246.98 164576 6 FALSE FALSE FALSE Expense Information Technology FALSE
IT - Consultants 13e3 35e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
Teleconference 1e3 125 6 FALSE FALSE FALSE Expense Information Technology FALSE
Training/Educational/Networking 2e3 7e3 6 FALSE FALSE FALSE Expense Information Technology FALSE
-$ 0 NA 7 FALSE FALSE FALSE Revenue Board Support FALSE
Audio/Visual 7500 9013.48 8 FALSE FALSE FALSE Expense Board Support FALSE
Awards/Prizes - Misc. 2e3 2e3 8 FALSE FALSE FALSE Expense Board Support FALSE
Insights 0 0 8 FALSE FALSE FALSE Expense Board Support FALSE
Governance 1e3 1e3 8 FALSE FALSE FALSE Expense Board Support FALSE
President Discretionary 2e3 1e3 8 FALSE FALSE FALSE Expense Board Support FALSE
Shirts - Board 0 1824 8 FALSE FALSE FALSE Expense Board Support FALSE
Management - General 78751.74 99830.68 8 FALSE FALSE FALSE Expense Board Support FALSE
Telephone - PASS 2800 2e3 8 FALSE FALSE FALSE Expense Board Support FALSE
Teleconference 1100 500 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Summit - Leadership (Air/Land) 5e3 7535 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Summit - Leadership Global (Air/Land) 4400 1235 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Summit - Leadership (Hotel) 14850.56 14357.83 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Summit - Leadership (Meals) 1500 1550 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Board Meetings - (Air/Land) 19800 18500 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Board Meetings - Global (Air/Land) 8800 6200 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Board Meeting - (Hotels) 31178.32 33605.28 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Board Meetings - (Meals) 19703.76 23393.36 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel BAC -Leadership (Air/Land) 5e3 0 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel BAC -Leadership Global (Air/Land) 4400 0 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel BAC - Leadership (Hotel) 11833.2 0 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel BAC - Leadership (Meals) 1500 0 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Misc. - Leadership (Air/Land) 3600 5440 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Misc. - Leadership (Hotel) 4488.92 4800 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel Misc. - Leadership (Meals) 6e3 2160 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel - Misc. (Air/Land/Hotel) 6e3 4520 8 FALSE FALSE FALSE Expense Board Support FALSE
Travel - Misc. (Meals) 1500 1080 8 FALSE FALSE FALSE Expense Board Support FALSE
Job Target 6e3 2500 9 FALSE FALSE FALSE Revenue Member Services FALSE
Job Bank - Revenue 0 2e4 9 FALSE FALSE FALSE Revenue Member Services FALSE
Credit Card Fees 0 700 10 FALSE FALSE FALSE Expense Member Services FALSE
Management - General 0 2629.09 10 FALSE FALSE FALSE Expense Member Services FALSE
Marketing - Special Projects 0 0 10 FALSE FALSE FALSE Expense Member Services FALSE
Job Bank - Expenses 0 12e3 10 FALSE FALSE FALSE Expense Member Services FALSE
-$ 0 NA 11 FALSE FALSE FALSE Revenue General Marketing FALSE
Design/Graphic - BA Conference 2e4 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - BA Day 0 7e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - BA Marathon 1250 1e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - Chapters 500 500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - General 25e3 3e4 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - Board 0 5e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - Sales 1e4 7500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - SQLSaturday NA 1500 5e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - Summit 21e3 2e4 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - Virtual Chapters 1e3 2e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Design/Graphic - Virtual Events 1e3 1e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Advertising - 24 HOP 2500 1500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Advertising - BA Marath 2500 1500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing/Advertising - BA Community 0 2e4 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Advertising - BAC 4e4 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Advertising - General 6e3 25e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Advertising - Summit 1e4 25e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - BA Conference 3e4 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - BA Regional/Virtual Events 17500 5e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Community (VCs, Chapters) 1e3 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - General 3e4 16e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Sales 2e3 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Special Projects 3500 5e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - SQLRally Global 0 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - SQLSaturday 1e3 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Summit 2e4 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Marketing - Virtual Events 1500 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Miscellaneous Contractor -Sales 0 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Multi Media - Governance 1500 5e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Multi Media - Summit 15e3 17500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Multi-Media - BAC 3e3 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Promotional Incentives - BAC 1e4 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Promotional Inctives - BA Day 0 2500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Promotional Incentives - Chapter/VC 2e3 2e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Promotional Incentives - General 3e3 0 12 FALSE FALSE FALSE Expense General Marketing FALSE
Promotional Incentives - SQLSaturday NA 7500 7e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Promotional Incentives - Summit 15e3 4500 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management- Marketing (Administration) 24668.95 6572.72 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management- Marketing (BA Community) 17481.68 61629.43 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management - Marketing (BAC) 56508.53 3663.22 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management - Marketing (General) 101667.88 152561.28 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management - Marketing (Global) 37945.78 17656.08 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management - Marketing (Sales) 22973.56 5246.22 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management - Marketing (Summit) 60342.53 90575.6 12 FALSE FALSE FALSE Expense General Marketing FALSE
Management - Marketing (Virtual Chapters) 4090.11 3890.25 12 FALSE FALSE FALSE Expense General Marketing FALSE
Teleconference 1500 350 12 FALSE FALSE FALSE Expense General Marketing FALSE
Training/Education/Networking 0 5e3 12 FALSE FALSE FALSE Expense General Marketing FALSE
Travel - Misc. (Air/Land/Hotel) 3e3 3250 12 FALSE FALSE FALSE Expense General Marketing FALSE
Travel - Misc. (Meals) 800 400 12 FALSE FALSE FALSE Expense General Marketing FALSE
-$ 0 NA 13 FALSE FALSE FALSE Revenue Chapters FALSE
Awards/Prizes/Gifts 2250 500 14 FALSE FALSE FALSE Expense Chapters FALSE
Chapter leader Meeting - Summit 1597.69 1542.92 14 FALSE FALSE FALSE Expense Chapters FALSE
Community Evangelist - Contractor 9370.54 0 14 FALSE FALSE FALSE Expense Chapters FALSE
Incentives - Summit & BAC Registrations 15e3 15e3 14 FALSE FALSE FALSE Expense Chapters FALSE
Management - General 1097.34 311.71 14 FALSE FALSE FALSE Expense Chapters FALSE
Regional Mentor Meeting - Summit 45468.18 93376.06 14 FALSE FALSE FALSE Expense Chapters FALSE
Teleconference 200 0 14 FALSE FALSE FALSE Expense Chapters FALSE
Travel - CE Domestic A/L/H/M 2100 0 14 FALSE FALSE FALSE Expense Chapters FALSE
Travel - CE International A/L/H/M 2e3 0 14 FALSE FALSE FALSE Expense Chapters FALSE
-$ NA NA 15 FALSE FALSE FALSE Revenue Volunteer Programs & Engagement FALSE
Awards/Prizes - Misc. 350 350 16 FALSE FALSE FALSE Expense Volunteer Programs & Engagement FALSE
Volunteer Incentives 4393.11 1458.24 16 FALSE FALSE FALSE Expense Volunteer Programs & Engagement FALSE
Management - General 0 1850.37 16 FALSE FALSE FALSE Expense Volunteer Programs & Engagement FALSE
Volunteer Evening Event 8500 7751.46 16 FALSE FALSE FALSE Expense Volunteer Programs & Engagement FALSE
Volunteer Meeting Summit 1e3 0 16 FALSE FALSE FALSE Expense Volunteer Programs & Engagement FALSE
-$ 0 NA 17 FALSE FALSE FALSE Revenue Special Projects FALSE
Management- Contingency 161033.04 0 18 FALSE FALSE FALSE Expense Special Projects FALSE
Management - General 0 83697.98 18 FALSE FALSE FALSE Expense Special Projects FALSE
Management - General (Contingency) 540839.35 615313.01 18 FALSE FALSE FALSE Expense Special Projects FALSE
Management - Launch Events 0 0 18 FALSE FALSE FALSE Expense Special Projects FALSE
Training/Education/Networking 0 1500 18 FALSE FALSE FALSE Expense Special Projects FALSE
Advertising - Web General 0 33e3 19 FALSE FALSE FALSE Revenue SQLSaturday FALSE
Advertising - Web GAP 0 5400 19 FALSE FALSE FALSE Revenue SQLSaturday FALSE
Community Evangelist - Contractor 68149.41 77836.36 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Ad Serving Fees 0 11520 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Sales Commission - General 0 3300 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Sales Commission - GAP 0 135 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Regional Mentor -TE 3500 3500 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
SQLSaturday Sponsorship 28e3 19750 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
SQLSaturday Sponsorship - International 27e3 17750 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
SQLSaturday Round Table - Summit 1556.43 1542.92 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Management - General 29478.35 9555.03 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Management - SQLSaturday North America 769.42 3687.66 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Management - SQLSaturday Global 1693.85 6218.93 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Teleconference 600 100 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Travel - CE (1) Domestic A/L/H/M 12600 2e3 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Travel - CE (1) International A/L/H/M 36e3 7e3 20 FALSE FALSE FALSE Expense SQLSaturday FALSE
Sponsorship - MS 24 Hours of PASS 18e3 18e3 21 FALSE FALSE FALSE Revenue Virtual Events FALSE
Sponsorship - 24 Hours of PASS 19e3 22500 21 FALSE FALSE FALSE Revenue Virtual Events FALSE
Sponsorship - 24 Hours of PASS (GAP) 14662.5 17550 21 FALSE FALSE FALSE Revenue Virtual Events FALSE
Sponsorship - MS BA Marathon 0 5e3 21 FALSE FALSE FALSE Revenue Virtual Events FALSE
Sponsorship - BA Marathon 0 3500 21 FALSE FALSE FALSE Revenue Virtual Events FALSE
Sponsorship - BA Marathon (GAP) 0 2250 21 FALSE FALSE FALSE Revenue Virtual Events FALSE
Sales Commission - General 2850 2600 22 FALSE FALSE FALSE Expense Virtual Events FALSE
Sales Commission - GAP 0 495 22 FALSE FALSE FALSE Expense Virtual Events FALSE
Management - Contingency 0 0 22 FALSE FALSE FALSE Expense Virtual Events FALSE
Management - Virtual Events 18767.16 27433.67 22 FALSE FALSE FALSE Expense Virtual Events FALSE
Management - Virtual Events (Global) 1928.3 2530.86 22 FALSE FALSE FALSE Expense Virtual Events FALSE
-$ NA NA 23 FALSE FALSE FALSE Revenue Global Growth FALSE
Community Evangelist - Contractor 3407.47 4324.24 24 FALSE FALSE FALSE Expense Global Growth FALSE
Regional Board Advisor - Latam 6e3 0 24 FALSE FALSE FALSE Expense Global Growth FALSE
Legal Consultant 15e3 15e3 24 FALSE FALSE FALSE Expense Global Growth FALSE
Management - General 19987.06 14828.31 24 FALSE FALSE FALSE Expense Global Growth FALSE
Teleconference 1e3 550 24 FALSE FALSE FALSE Expense Global Growth FALSE
Travel - Misc. (Air/Land/Hotel) 6e3 9e3 24 FALSE FALSE FALSE Expense Global Growth FALSE
Travel - Misc. (Meals) 1200 1800 24 FALSE FALSE FALSE Expense Global Growth FALSE
Advertising - Web General 88e3 77e3 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Web GAP 18e3 18900 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Web MS 9e3 0 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Mailing General 35e3 67500 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Mailing GAP 31e3 67500 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Mailing MS 9e3 9e3 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Mailing BAI General 0 24e3 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Mailing BAI GAP 0 10800 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Mailing BAI MS 0 0 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Newsletter General 11700 7e3 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Newsletter GAP 11700 6300 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Newsletter MS 0 0 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Newsletter BAI Gen 0 1250 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Newsletter BAI GAP 0 900 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Newsletter BAI MS 0 0 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Remarketing - General 15e3 67500 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Remarketing - GAP 5e4 72450 25 FALSE FALSE FALSE Revenue Sales FALSE
Advertising - Remarketing - MS 0 0 25 FALSE FALSE FALSE Revenue Sales FALSE
Membership Dues - GAP 75e3 75e3 25 FALSE FALSE FALSE Revenue Sales FALSE
Ad Serving Fees 0 28770 26 FALSE FALSE FALSE Expense Sales FALSE
Advertising - Remarketing Fees 0 90967.5 26 FALSE FALSE FALSE Expense Sales FALSE
Incentive - Director of Sales 0 3e4 26 FALSE FALSE FALSE Expense Sales FALSE
Promotional Incentives - Sales 4771.31 2916.48 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Commission - General 21330 24425 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Commission - GAP 8e3 6296.25 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Tools 5820 4320 26 FALSE FALSE FALSE Expense Sales FALSE
Sponsor awards/F&B for meetings 3e3 4100 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Contractor - BAC 89220 0 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Contractor - General 16062 25868.28 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Contractor - Global Alliance 618 0 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Contractor - Summit 51810 46224.36 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Contractor - Virtual Chapters 4854 3892.68 26 FALSE FALSE FALSE Expense Sales FALSE
Sales Contractor - Virtual Events 4236 3892.68 26 FALSE FALSE FALSE Expense Sales FALSE
Management - General 78102.65 141480.4 26 FALSE FALSE FALSE Expense Sales FALSE
Management - Global Alliance 75669.57 45222.59 26 FALSE FALSE FALSE Expense Sales FALSE
Teleconference 1e3 350 26 FALSE FALSE FALSE Expense Sales FALSE
Training/Educational/Networking 9e3 6e3 26 FALSE FALSE FALSE Expense Sales FALSE
Travel - Misc. (Air/Land/Hotel) 5100 2500 26 FALSE FALSE FALSE Expense Sales FALSE
Travel - Misc. (Meals) 900 450 26 FALSE FALSE FALSE Expense Sales FALSE
BA - Days 0 7e4 27 FALSE FALSE FALSE Revenue Sales FALSE
Sponsorship - MS BA DAYS 0 3e3 27 FALSE FALSE FALSE Revenue Sales FALSE
Management - General 8884.31 52022.72 28 FALSE FALSE FALSE Expense Sales FALSE
Travel - Misc. (Air/Land/Hotel) 3e3 0 28 FALSE FALSE FALSE Expense Sales FALSE
Travel - Misc. (Meals) 800 0 28 FALSE FALSE FALSE Expense Sales FALSE
Teleconference 200 0 28 FALSE FALSE FALSE Expense Sales FALSE
Virtual Chapters Sponsorship 12e3 28e3 29 FALSE FALSE FALSE Revenue Virtual Chapters FALSE
Virtual Chapters Sponsorship - Global Alliance 14900 1e4 29 FALSE FALSE FALSE Revenue Virtual Chapters FALSE
Awards/Prizes/Gifts 1e3 1e3 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
VC Incentive Program 0 7610.79 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Community Evangelist - Contractor 0 0 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Sales Commission - General 0 0 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Sales Commission - GAP 0 0 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Shirts (Summit) 900 900 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Sponsorship - VC Share 6435.5 14060 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Management - General 11548.82 34457.56 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Management - VC (Sponsorship Maintenance) 2732.85 4351.62 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Teleconference 50 0 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
VC Meeting - Summit 1204.29 311.71 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
VC Sponsorship - Management 1800 3050 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
WIT Lunch 0 0 30 FALSE FALSE FALSE Expense Virtual Chapters FALSE
Commission - Summit Book Store 600 300 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Hotel Commission 109008.6 103967.4 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Hotel Rebate 80684.2 51357.7 31 FALSE FALSE FALSE Revenue Community Summit FALSE
PASS Store Royalty 0 6476 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Sponsorship - MS Appreciation Event 75e3 75e3 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Sponsorship - MS IOT 0 87737.15 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Registration - MS 129500 129500 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Registration - Summit 5453990.81 5716051.92 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Registration - One Day 0 83475 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Registration - Cancellation Fees 0 0 31 FALSE FALSE FALSE Revenue Community Summit FALSE
Sponsorship WIT Lunch 0 5850 31 FALSE FALSE FALSE Revenue Community Summit FALSE
ADA Requirements 5e3 13672.37 32 FALSE FALSE FALSE Expense Community Summit FALSE
Audio/Visual 125500 116067.14 32 FALSE FALSE FALSE Expense Community Summit FALSE
WIT LUNCH 1100 3447.04 32 FALSE FALSE FALSE Expense Community Summit FALSE
Bag Stuffing Labour & Supplies 4479.11 5093.6 32 FALSE FALSE FALSE Expense Community Summit FALSE
Computer Rental 16500 14261.69 32 FALSE FALSE FALSE Expense Community Summit FALSE
Community Evangelist - Contractor 4259.34 4324.24 32 FALSE FALSE FALSE Expense Community Summit FALSE
Credit Card Processing 190889.68 200061.82 32 FALSE FALSE FALSE Expense Community Summit FALSE
Community Zone and Info Desk 6258.07 7597.7 32 FALSE FALSE FALSE Expense Community Summit FALSE
Conv.Center Space Rental Cr F&B -95518.15 -85954.32 32 FALSE FALSE FALSE Expense Community Summit FALSE
Conv. Centre Internet Access 31600 27998.33 32 FALSE FALSE FALSE Expense Community Summit FALSE
Decorator - Show 46112.92 63435.5 32 FALSE FALSE FALSE Expense Community Summit FALSE
Delegate Gift 47710.5 50209.5 32 FALSE FALSE FALSE Expense Community Summit FALSE
Electrical Charges 11e3 13217.91 32 FALSE FALSE FALSE Expense Community Summit FALSE
Facility Rental 193867.03 185694.2 32 FALSE FALSE FALSE Expense Community Summit FALSE
Facility Rental: Discounted Rent -77875 -68395 32 FALSE FALSE FALSE Expense Community Summit FALSE
CVB Cash Concession -5e3 -5e3 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Board) 4762.76 3192.45 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Registration Staff) 3048.01 2813.25 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (PASS HQ) 18303.44 18388.22 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Paid Delegates) 1140684.22 1206063.28 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Drink Tickets) 25191.92 20135.05 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Comps Credit) -102427.05 -83274.43 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Water Bubblers) 7695.02 10303.5 32 FALSE FALSE FALSE Expense Community Summit FALSE
F&B (Bartender/Labour) 4817.16 3551.04 32 FALSE FALSE FALSE Expense Community Summit FALSE
First Timers Event 2924.15 802.77 32 FALSE FALSE FALSE Expense Community Summit FALSE
Insurance 17800 20650 32 FALSE FALSE FALSE Expense Community Summit FALSE
IOT 0 87737.15 32 FALSE FALSE FALSE Expense Community Summit FALSE
Production - Keynote 202529.52 207887.74 32 FALSE FALSE FALSE Expense Community Summit FALSE
Office Supplies 650 1500 32 FALSE FALSE FALSE Expense Community Summit FALSE
Onsite Labour 7e3 13086 32 FALSE FALSE FALSE Expense Community Summit FALSE
PASS Store 0 4455.45 32 FALSE FALSE FALSE Expense Community Summit FALSE
Pass TV 36330.62 30508.34 32 FALSE FALSE FALSE Expense Community Summit FALSE
Postage/Shipping 2500 2815 32 FALSE FALSE FALSE Expense Community Summit FALSE
Power Lounges 6994.76 2959.26 32 FALSE FALSE FALSE Expense Community Summit FALSE
Printing - General 500 1e3 32 FALSE FALSE FALSE Expense Community Summit FALSE
Printing - BOF Lunch 250 1722.52 32 FALSE FALSE FALSE Expense Community Summit FALSE
Printing - Program Guide 8047.26 10547.41 32 FALSE FALSE FALSE Expense Community Summit FALSE
Production - PRP 65e3 66975 32 FALSE FALSE FALSE Expense Community Summit FALSE
Regonline Fees 8176.5 8635.5 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Staff - Onsite 51385 53545.5 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Staff - Badges 14819 28785 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Fees 45425 47975 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Staff - Set up fee 1750 1750 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Fees - Housing 23100 19740 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Staff - Travel (Air/Land/Hotel) 5234.53 3150 32 FALSE FALSE FALSE Expense Community Summit FALSE
Registration Staff - Travel - Meals 2500 3e3 32 FALSE FALSE FALSE Expense Community Summit FALSE
Security 4750 6150 32 FALSE FALSE FALSE Expense Community Summit FALSE
Staff Shirt - Events 10255 8732 32 FALSE FALSE FALSE Expense Community Summit FALSE
Management - General 99548.06 173793.97 32 FALSE FALSE FALSE Expense Community Summit FALSE
Appreciation Event - FIXED COST 76118 75e3 32 FALSE FALSE FALSE Expense Community Summit FALSE
Appreciation Event - VARIABLE COST 139797.62 0 32 FALSE FALSE FALSE Expense Community Summit FALSE
Teleconference 250 60 32 FALSE FALSE FALSE Expense Community Summit FALSE
Telephone - PASS 1500 1e3 32 FALSE FALSE FALSE Expense Community Summit FALSE
Travel - Misc. (Air/Land/Hotel) 63165.32 45905.32 32 FALSE FALSE FALSE Expense Community Summit FALSE
Travel - Misc. (Meals) 8e3 8775 32 FALSE FALSE FALSE Expense Community Summit FALSE
Vests (Staff) 1500 500 32 FALSE FALSE FALSE Expense Community Summit FALSE
Sponsorship - Advertising 0 0 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - Advertising Global Alliance 0 0 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - AMO (Summit) 124400 146e3 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - AMO GAP (Summit) 26e3 48850 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - Cancellation Fees 0 0 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - Exhibitor (Summit) 294800 213700 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - Exhibitor GAP NA 0 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - MS Agreement Summit 155e3 17e4 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - Package (Summit) 182e3 232e3 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
Sponsorship - Package GAP (Summit) 232700 266400 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
WIT Luncheon Sponsorship 0 0 33 FALSE FALSE FALSE Revenue Community Summit Expo FALSE
AMO Expenses 64814.31 55339.86 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
AMO Expenses - GAP 10801 21771.31 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Audio Visual 2e3 1551.5 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Decorator - Show 21951.19 24474.29 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Electrical Charges 1500 2e3 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Sales - Commission 112518 68447.5 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Sales - Hotel Rooms 1445.88 0 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Sales - Travel 1100 0 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Sales - Meals 250 0 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
F&B (Bartender/Labour) 3878.11 6904.8 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
F&B (Drink Tickets) 26543.08 21329.54 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
F&B (Expo Reception) 122563.57 135556.89 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Lead Retrieval 536.39 1578.55 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
MS Sponsorship Expenses 146585.77 129214.96 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Registration Staff - Set up fee 1750 1750 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Regonline Fees 135 135 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Security 3e3 4260 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Sponsor/Exhibitor Expense 146930.99 142471.96 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Sponsor/Exhibitor Expense - Global Alliance 130999.72 101096.19 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Management - General 44749.42 117769.78 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Telephone - PASS 900 900 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
WIT Lunch 5500 0 34 FALSE FALSE FALSE Expense Community Summit Expo FALSE
Conference Proceedings (Summit) 198947.66 196292.58 35 FALSE FALSE FALSE Revenue Summit Programs FALSE
Conference Proceedings (Post Summit) 51362.3 84955.5 35 FALSE FALSE FALSE Revenue Summit Programs FALSE
Conference Proceedings NA 17827.44 35 FALSE FALSE FALSE Revenue Summit Programs FALSE
Registration - PreCon (Summit) 1024531.2 1000890 35 FALSE FALSE FALSE Revenue Summit Programs FALSE
EXPENSE - NA NA 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker Ready Room 4800 12594.78 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Awards/Prizes/Gifts 6e3 4e3 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Content Consultant 0 0 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Consultant - Program 0 1e4 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Conference Proceeding (Record Fee) 95e3 91568.14 36 FALSE FALSE FALSE Expense Summit Programs FALSE
CSS (Travel/Exp) 1e4 1e4 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Decorator - Show 4906.54 0 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker - Keynote 678.79 718.12 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Program Committee Volunteer Gifts/Awards 5e3 5e3 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Shirts - Speaker 6750 4920 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Labs 0 0 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Signage Production & Printing 5186.72 3662.83 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker Gifts (Summit) 10823.83 6600 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker Experience 500 500 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker Requests 500 438.74 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker Expense - Community 74756.76 53468.71 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Survey Prizes 1169.14 1200 36 FALSE FALSE FALSE Expense Summit Programs FALSE
USB Fulfillment Fee 51143.18 47269.73 36 FALSE FALSE FALSE Expense Summit Programs FALSE
USB Shipping Fee 0 17827.44 36 FALSE FALSE FALSE Expense Summit Programs FALSE
EXPENSE - NA NA 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Audio/Visual (PreCon) 72e3 64392.05 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Computer Rental 7e3 5694.44 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Convention Center Internet Access 16e3 13260.35 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Signage Production & Printing 1065.42 710.21 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Electrical Charges 4e4 30982.92 36 FALSE FALSE FALSE Expense Summit Programs FALSE
F&B (PreCon) 231463.35 230664.95 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Printing (PreCon) 17697.6 7661.83 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Security 4750 5760 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Labs 20232 21155.8 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Speaker Fees/Expense (PreCon) 153693.87 148826.52 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Teleconference 0 0 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Travel - Speaker (PreCon) 8e3 5500 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Community Evangelist - NA 0 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Management - General (Program) 53146.11 70916.33 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Management - General (PreCon Program) 11911.12 6975.99 36 FALSE FALSE FALSE Expense Summit Programs FALSE
Raw
wrangling.R
library(tabulizer)
library(tidyverse)
library(stringr)
library(zoo)
library(tidytext)
##---- Functions ----
moneycleaner <- function(x) {
x %>%
str_replace_all("[$,]|[[:space:]]", "") %>%
# Handle $0 amounts
str_replace_all("^-$", "0") %>%
# Handle negative amounts
ifelse(str_detect(., "\\("),
paste0("-", str_replace_all(., "[()]", "")),
.) %>%
as.numeric()
}
combineLHS <- function(x) {
n <- length(x)
if (x[n] == "Server")
return(c(paste(x, collapse = " "), "", ""))
if (n < 3)
return(c(x, rep("", 3 - n)))
lineItem <- paste(x[1:(n - 2)], collapse = " ")
b2016 <- x[n - 1]
b2017 <- x[n]
c(lineItem, b2016, b2017)
}
##--- Script ----
"http://www.pass.org/Portals/0/Governance%202016/Financials/pass-budget-2017.pdf?ver=2017-01-25-235556-197" %>%
tabulizer::extract_text() ->
rawtxt
rawtxt %>%
tokenize(tokenizer = tokenizer_line()) %>%
as_vector() %>%
str_replace_all("\\s+", " ") %>%
str_trim(side = "both") %>%
str_split(" ") %>%
map(combineLHS) %>%
map( ~ as_data_frame(t(.))) %>%
bind_rows() ->
rawdata
rawdata %>%
select(LineItem = V1,
B2016 = V2,
B2017 = V3) %>%
mutate(
TblChange = cumsum(LineItem %in%
c("REVENUE", "EXPENSE")),
Total = str_detect(LineItem, "^TOTAL"),
Net = str_detect(LineItem, "^NET"),
Title = B2016 == "Budget" | B2017 == "Budget" |
LineItem %in%
c("REVENUE", "EXPENSE", "Professional Association for SQL Server"),
Type = ifelse(TblChange %% 2 == 0, "Expense", "Revenue"),
Dept = ifelse(B2017 == "2017",
str_replace(str_extract(LineItem, "^.+-")," -",""),
NA),
IgnoreRow=Total|Net|Title
) %>%
mutate(Dept = zoo::na.locf(Dept, na.rm = FALSE),
B2016=moneycleaner(B2016),
B2017=moneycleaner(B2017)) %>%
filter(!IgnoreRow) ->
flaggeddata
flaggeddata %>%
filter(TblChange <= 2) ->
summarydata
flaggeddata %>%
filter(TblChange > 2) ->
detaildata
write_csv(detaildata,"detaildata.csv")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment