엑셀) 2016년형 교직원 맞춤형복지비 관리 서식

 작년에 월급주는 업무를 겸하면서 시작했던게 직원들 맞춤형복지비 신청받고 지급하는 업무였다. 전임자가 관리하는 방식이 있긴했지만 시스템에 의존하는 성격이라 관리에 불편함이 있어서 엑셀로 별도 관리서식을 만들었는데 대충 사용만 가능하게끔 꾸며두고 방치했다가 또다시 한해가 지나 맞춤형복지의 시즌이 다가옴에 정비하고 블로그에 혹시나 정보가 필요할 경우가 있을까봐 공유하고자 한다.

 서식의 용도는 단순하다. 각 개인이 맞춤형사이트에 접속해서 등록한 카드로 결재한 내역을 청구한걸 담당자가 확인해서 지급하면 되는데 사이트가 조금 허접하기도하고 녀석도 실제 지급까지는 아닌 접수받고 관리하는 역할을 담당한다.

그 사이트에 자료를 관리하기 위해 필요에 의해 엑셀로 서식을 만들어야했는데, 단순히 복지비를 배정받고 그 금액을 100% 다 쓸수 있는게 아니라 거기서 단체보험료를 빼야하고, 10%는 의무적으로 온누리상품권도 구입해서 그 잔액을 자율항목비로 쓸수가 있는것이다.
 그과정을 모르는 일반 직원들은 단순히 복지사이트에 올려둔 금액만큼 카드청구하는게 전부여서 업무담당자는 조금 귀찮아진다. 아예 100% 사이트에서 모든 업무를 다 할수 있으면 좋으련만 여기저기 정보를 찔끔찔끔 흘려서 관리하는게 번거로워 만들게 되었다.

 일단 서식에서는 관리대장을 만들고 그 자료를 토대로 안내자료를 생성한다.
생성된 PDF 안내자료를 메신저로 보내 해당자가 기간내 사이트에서 청구할수 있도록 도와주는 역할을 한다. 내가 얼마쯤 쓸수 있는지를 알기쉽게 설명하는게 목적이다.
그리고 받은 예산을 사용한뒤 얼마가 남게되는지 파악을 할수 있다. 이게 첨부된 서식의 용도이다.

 지난해에는 안내파일을 엑셀로 추출하여 다시 특별검진받는 병원명단과 신청서를 첨부하느라 시간이 조금 걸렸지만 올해는 운좋게 PDF로 추출하는 기능을 발견해서(?) 해당자가 필요한 서식만 골라서 메신저로 보낼수 있게 되었다. 이제 이 기능을 발견했으니 올해는 이 기능을 우려먹을 차례이다.
 예를 들어 본인의 경우 아직 나이가 안되어(2016년의 경우 1976년 12월 31일 이전 출생자중 짝수년도 인원만 특별검진 대상자이다) 특별검진을 받을 필요가 없는데 괜히 서식을 같이 날려버리면 혼동하기 쉽다. 그래서 비해당자의 경우는 안내페이지만 추출되어 생성되고, 검진대상자의 경우는 신청서와 병원 명단이 같이 첨부되어 하나의 파일로 추출이 된다.

 뭐 이렇게 생성된 파일도 개개인 한건씩 메신저로 보내야하지만 이 복지비도 해당안되는 기간제인원들이 있기에 어차피 하나씩 찍어줘야하는데 그나마 2번의 일을 줄일수 있으니 만족스러운 결과물이다.

▷ 2016년형 맞춤형복지비 안내문 배포기
▷ 맞춤형복지비 안내문 샘플
첨부파일이 다음(Daum)의 외부링크 제한으로 한번에 다운로드 되지 않습니다..
403 Forbidden 창의 주소표시줄에서 엔터를 입력하면 다운받을수 있습니다.

- 참고자료
▷ 엑셀 소수점 둘째자리 반올림 6이상 부터 - 네이버 지식인
▷ 엑셀 금액단위별 잔돈계산하기 - 오피스튜터
▷ 엑셀 다중페이지 선택하기(VBA Selecting Multiple sheets using a range - Stackoverflow
▷ 벡터방식 QR코드 생성하기
▷ 벡터방식 QR코드 생성하기(2)



▷ 자동입력되는 안내문 페이지이다. 여기에 자료를 입력하고 PDF추출하게되면 아래 두번째 배포용 시트에 자료가 복사되고 PDF로 생성이 되는 방식이다.


▷ 자료입력시트에서 복사해온뒤 pdf로 생성된다.


▷ 관리대장 시트이다. 여기에 노랑색 부분을 수기로 정보를 입력해줘야한다. 시스템상 긁어오는게 안되기 때문에 입력할수 밖에 없는데 거의 매년 변동이 없기 때문에 근무년수, 가족구성, 보험료단가 정도만 업데이트 해주면 된다.
 나머지는 기준금액을 계산해서 자동으로 전체복지비, 10% 할당된 상품권 구입수량, 실제 사용가능한 자율항목비 잔액을 알려준다.


▷ 정산시트이다. 관리대장에 입력된 정보 끝단에 집행실적(1차, 2차..)을 입력하게 되면 정해진 차수에 얼마, 몇명이 사용하여 잔액이 얼마 남았는지 확인할 수 있다.
 보험료 단가를 여기서 수정할수 있다.


▷ 특별검진대상자에 한해 보내야할 시트이다. 자동으로 이름, 생년월일을 긁어와서 생성된 PDF자료를 배포하면 병원에서 검진받고 하단에 병원장 확인도장을 받아오면 된다.


▷ 검진 업무협약된 병원을 기입하는 곳이다. 저 위에 복지비 신청서를 출력하게 되면 상단에 QR코드가 있는데 그걸 찍으면 인근에 어느병원이 있는지 폰으로 위치확인할수 있다.


▷ 이건 셋째자녀를 출생한 인원에게만 배포가 되는 신청서식이다.


▷ 예시로 가길동씨의 자료를 PDF추출해보면 이렇게 3장이 나온다. 가길동씨가 특별검진 대상자이기 때문이다.


▷ 관리대장 화면을 조금더 키워보았다.
 성명, 구분, 성별, 생년월일, 근무년수, 가족사항, 보험선택안, 개인 실손보험 가입여부, 개인 상품권 구입여부만 입력하면 사용할수 있는 자율항목비가 계산되어 나온다.

 작년부터 복지비 사이트가 똑똑해져서 사이트에서 청구하는 기능 하나 추가된것만해도 엄청나게 편해졌는데 그래도 조금 아쉬운게 지급까지 100% 담당하지 못하는게 흠이다. 하기야 회계시스템이 있는데 새로 만들기도 그렇지만 두 시스템이 연동이 안되는게 문제일것이다. 복지비 관리사이트가 범용적으로 여러 기관에서 사용하다보니 예산, 시간이 많이 들어서 그럴수도 있겠지만 아예 복지비만큼은 이사이트에서 관리하도록 나눈다거나 하면 좋을텐데.. 언젠가는 통합이 되겠지?