Question

Below are data that need to be placed in an excel spreadsheet. The challenge is to create a proposed budget for the public library of Smithtown. Here is all the information you need: Proposed Budget:

§ 5 percent increase in salaries except for clerical staff, who get a 6.5 percent increase

§ Assume fringe benefits as given (12% health insurance, 20% pension, 6% Social Security, 2% disability)

§ One chief librarian at a salary of $75, 893

§ Two librarian positions at Level II at a salary of $61, 234 each

§ Four librarian positions at Level I at a salary of $54, 469 each

§ One MIS position at a salary of $71, 089

§ Five clericals, three of who are budgeted for half time at a fulltime salary of $31,789, and the other two are full-time.

Complete the spreadsheet and answer the following questions: 1) What is the salary plus fringe benefits for one Level II librarian? 2) What is the total number of positions, not people? 3) How much is the library spending on health insurance for a Level I librarian in the proposed budget? 4) What is the total proposed budget for the library?

Answer 1

S.No.	Nos	Position	Current Salary ($)	Budgeted Salary, $ (with 5% Increase)	6.5% increase	Fringe Benefits				Total Salary, $ (with Benefits)			Fringe benefits	Proportion of Budgeeted Salary
						Health Insurance, $ (12%)	Pension, $ (20%)	Social Security, $ (6%)	Disbility, $ (2%)
1	1	Chief librarian	75,893	79,687.65	-	9,562.52	15,937.53	4,781.26	1,593.75	111,562.71			health insurance	12%
2	4	librarian Level I	54,469	57,192.45	-	6,863.09	11,438.49	3,431.55	1,143.85	80,069.43			pension	20%
3	2	librarian Level II	61,234	64,295.70	-	7,715.48	12,859.14	3,857.74	1,285.91	90,013.98			Social Security	6%
4	1	MIS position	71,089	74,643.45	-	8,957.21	14,928.69	4,478.61	1,492.87	104,500.83			Disability	2%
5	2	Clerical	31,789	-	33,855.29	4,062.63	6,771.06	2,031.32	677.11	47,397.40
6	3	Clerical (Half Time)	31,789	-	16,927.64	2,031.32	3,385.53	1,015.66	338.55	23,698.70
										457,243.05
		1. Salary plus fringe benefits for one Level II librarian = $90,013.98
		2. Total number of positions = 5
		3. Library spending on health insurance for a Level I librarian in the proposed budget = 4 x $6863.09 = $27,452.38
		4. total proposed budget for the library = $457,243.05