leveraging winshuttle in sap for abc and scenario building · 2016. 8. 31. · university of...

Leveraging Winshuttle in SAP for ABC

and Scenario Building

Jake StoverAssistant Vice Chancellor for Finance and Treasurer

University of Arkansas for Medical Sciences

Winshuttle User Group Conference | Fort Worth 2014

Agenda

- Intro to UAMS

- UAMS’s story in “finding” Winshuttle

- Leveraging Winshuttle for Activity Based Costing

- Using Financial Data for Scenarios and Budgeting

- Utilizing Winshuttle for reporting



- Only academic medical center in Arkansas

- 10,000+ employees with many around the state

- Approximately 1,400 Faculty Members, many which are practicing physicians in the clinics and hospital

- Affiliations with Arkansas Children’s Hospital, the VA Medical Center, and many other hospitals in the state

- Colleges supporting education in health care related fields such as Medicine, Pharmacy, Nursing, etc.

- Large research component to the organization of over $100 million annually

- Annual budgeted revenue of approximately $1.3 Billion


Finding Winshuttle

2007 Cost Accounting

Need

Engaged Consultant

Built A Pilot ABC Model

PROBLEMSO MUCH

DATA!

What is Winshuttle?


Activity Based Costing

- Quantity driven model to allocate expenses- Differential from standard cost accounting is reciprocal allocations


System Flow

SAP PRODUCTION

• Master Data• Financial

Data• Statistics

from SAP

SAP SHADOW SYSTEM

• Master Data• Financial

Data• Statistics

DepartmentData

• Statistics

EXCEL DATA

Winshuttle


Example of Department


Reciprocated Allocations


Mass Amounts of Data

6 Major Data inputs from FY13:• KP06 Financials : 4,700+ lines of data• KP26 Activity Type Statistics: 5,300+ lines of data• CP26 Business Process Statistics: 150 lines of data• KP06 Activity Type Statistics: 4,500+ lines of data• CP06 Activity Type Statistics: 150 lines of data• KP06 Business Process Statistics: 64,500+ lines of data

Data used as needed:• KP46 Stat Key Figures, FMCIA Commitment Item MD, KA06 Secondary Cost Element MD


Information Usage

Financial Data

Statistics

Cost Reports

Hospital Budgeted Contribution/ Cost

Report

Costing Estimates For LCME

Accreditation

Pro-formasand Contract Evaluations


Results – Lots of Cost Data

Square Footage Metrics• Cost/Sq. Ft

• Environmental Services • Hazardous Waste• Police Dept. Coverage

• Cost/ Sq. Ft by building and space function type (i.e. Clinical, Classroom, Lab, Office, Storage)• Utilities• Building Depreciation

Headcount Metrics• Cost/Employee

• HR Comp/Benefits• Human Relations• Occupational Health and Safety• Mail• Payroll – Weekly vs. Biweekly

• Cost/ New Hire• Cost/ Non-Citizen• Cost/ Faculty Member

Other Metrics• IT, Development and Alumni Affairs, Communications and Marketing• Purchasing – Cost per PO Line Item• Travel – Cost per travel dollar• Telecommunications – Cost per phone line and cost per voicemail line


Primary Use – Administrative Cost of Hospital

HOSPITAL RCA RESULTS FY13

Division G/L Account G/L Description FY10 FY11 FY12 FY13 FY13 /FY12 Change

Buildings Total 10,854,600 11,591,124 11,138,695 11,234,574 95,879

Chancellor Div Total 2,217,456 2,384,710 1,572,608 1,166,540 (406,068)

IT Total 7,472,293 7,891,787 10,483,597 7,142,089 (3,341,508)

Development Total 270,804 357,830 324,090 248,373 (75,716)

INA Central Admin Total 3,763,308 3,598,804 3,388,660 3,743,068 354,408

Campus Ops Total 3,762,433 3,914,831 3,608,615 3,458,332 (150,283)

VC Admin Total 1,798,425 1,816,427 1,996,404 2,322,573 326,169

Academic Affairs Total 78,086 308,552 305,947 371,718 65,771

Communications Total 1,326,013 1,181,564 1,288,883 1,296,813 7,930

Finance Total 3,509,321 3,514,373 3,197,803 3,637,378 439,575

Institutional Compliance Total 170,271 871,278 972,734 967,731 (5,003)

VC Research Total (856) 15,915 (1,467) 21,909 23,376

Cost Allocation Total 35,222,155 37,447,195 38,276,569 35,611,099 (2,665,470)

Depreciation Total 8,095,487 8,689,364 10,633,084 11,341,353 708,269


Detail of Hospital Cost Allocation

HOSPITAL RCA RESULTS FY13

Division G/L Account G/L Description FY10 FY11 FY12 FY13 FY13 /FY12 Change

BLD 810207 AT: Clinical Space 1,110,774 2,120,274 2,237,425 2,287,893 50,468 Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810208 AT: Lab Space 526,768 75,272 85,435 85,301 (133) Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810209 AT: Office Space 2,441,857 1,963,770 1,324,086 1,274,229 (49,856) Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810210 AT: Storage Space 654,198 58,128 132,521 126,905 (5,616) Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810211 AT: Classroom Space 51,449 34,585 26,724 25,965 (759) Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810212 AT: Shell Space 280,854 63,509 218 191 (27) Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810245 AT: Clin Space Inpat 1,286,739 5,407,785 4,732,055 4,858,650 126,595 Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

BLD 810257 AT: Common Space 4,501,961 1,867,801 2,600,232 2,575,440 (24,792) Building Expense Including Utilities, Physicial Plant Work Orders on Buildings, Power Plant Overhead (Omit Dorms)

Buildings Total 10,854,600 11,591,124 11,138,695 11,234,574 95,879

CHA 820024 BP: Executive VC Adm 39,048 - - - Per Dollar of Department Expense

CHA 820193 BP: HIPAA 7,233 - - - Split Allocation between CLP,COM, VCA, VCR (THIS IS THE SECONDARIES for HIPPA ONLY)

CHA 820203 BP: Chancellor Activ 2,129,837 2,346,399 1,554,502 1,166,534 (387,968) Per Dollar of Department Expense

CHA 820204 BP: Human Relations 41,338 38,311 18,106 6 (18,100) Employee Headcount (Avg period 3,6,9)

Chancellor Div Total 2,217,456 2,384,710 1,572,608 1,166,540 (406,068)

CIS 820010 BP: CIS Administrati 7,472,293 7,891,787 10,483,597 7,142,089 (3,341,508) Per Percentage Usage

IT Total 7,472,293 7,891,787 10,483,597 7,142,089 (3,341,508)

INA 810101 AT: INA Electricity - - - 1 to 1 with 1000014 ELECUT

INA 810102 AT: INA Gas - - - 1 to 1 with 1000014 GASUTL

INA 810103 AT: INA Utilities - - - 1 to 1 with 1000014 OVERHD

INA 810104 AT: INA Water - - - 1 to 1 with 1000014 WATERU

INA 820031 BP: INA Employee Health 121,142 - - - Employee Headcount (Not AVG)

INA 820030 BP: INA Building Con 296,650 281,024 330,761 339,612 8,851 Asset Values per department

INA 820059 BP: INA Employee Ben 598,553 892,018 197,962 541,109 343,147 Employee Headcount (Avg period 3,6,9)

INA 820071 BP: INA Eqp Deprec - - - 100% Allocated to CHA

INA 820135 BP: INA Stu Aff 433,069 440,596 407,730 435,439 27,709 Specific to departments with individuals receiving tuition discounts

INA 820136 BP: INA Wrk Comp 589,615 546,285 743,135 573,035 (170,100) Employee Headcount (Avg period 3,6,9)

INA 820164 BP: INA Cen OH - - - - 100% Allocated to CHA

INA 820165 BP: INA CHA Tel - - - - 100% Allocated to CHA

INA 820166 BP: INA Veh Ins - - - - 100% Allocated to Vehicle Operations

INA 820191 BP: INA Compens Abse 95,296 334,879 334,879 446,349 111,470 Calulation by Cost Center for Absences from Finance (ACTUAL 2012 1,988,503 - ACTUAL 2013 -1,207,275)

INA 820205 BP: INA Campus Space 387,086 31,286 120,290 (120,290) On Campus Square Footage

INA 820206 BP: INA Liability In 1,903 465 55,325 60,545 5,220 Per Dollar of Department Expense

INA 820207 BP: INA Audit 193,332 196,535 181,113 173,243 (7,870) Per Dollar of Department Expense

INA 820208 BP: INA OPEB 1,046,661 875,716 1,017,465 1,173,737 156,272 Employee Headcount (Avg period 3,6,9)

INA Central Admin Total 3,763,308 3,598,804 3,388,660 3,743,068 354,408


Projective Capability of Info


Winshuttle For Reporting???

License number 2 that we weren’t using…• Monthly data extracted from SAP into excel tables

• Tables are organized in order to generate reports in various fashions

• Reports compare budget to actual, actual to actual, variance percentages, etc.


Reports

Tedious Reporting Task• Each year ~200 reports to export from SAP

• Took about 1 week

• Winshuttle Script to download all reports into a designated folder

• Took about 3 hours of passive work with the Winshuttle Script


Conclusion

• Winshuttle is great for managing massive amounts of data

• Saved UAMS staff time and effort

• Allowed UAMS to create more complex and detail models which would have never been possible

• Reporting is also a useful tool

• IT SAP group is testing uses of it for various activities


Contact Info

Jake Stover, MHSA, CTP

Assistant Vice Chancellor for Finance and Treasurer


[email protected]

(501) 526- 7646

leveraging winshuttle in sap for abc and scenario building · 2016. 8. 31. · university of...

Documents