PROBLEM 7-45______
Suppose you are the controller of Western Idaho State University. The university president, Willa Redcloud, is preparing for her annual fund-raising campaign for 2004-2005. To set an appropriate target, she has asked you to prepare a budget for the academic year. You have collected the following data for the current year (2003-2004):
Undergraduate Graduate
Division Division
Average salary of faculty member $46,000 $46,000
Average faculty teaching load in semester
credit hours per year (eight undergraduate
or six graduate courses) 24 18
Average number of students per class 30 20
Total enrollment (full-time and part-time
students) 3,600 1,800
Average number of semester credit hours
carried each year per student 25 20
Full-time load, semester hours per year 30 24
For 2004-2005, all faculty and staff will receive a 6% salary increase. Undergraduate enrollment is expected to decline by 2%, but graduate enrollment is expected to increase by 5%.
The 2004-2005 budget for operation and maintenance of facilities is $500,000, which includes $240,000 for salaries and wages. Experience so far this year indicates that the budget is accurate. Salaries and wages will increase by 6% and other operating costs will increase by $12,000 in 2004 –2005.
The 2003-2004 and 2004-2005 budgets for the remaining expenditures are:
2003-2004 2004-2005
General administrative $500,000 $525,000
Library
Acquisitions 150,000 155,000
Operations 190,000 200,000
Health services 48,000 50,000
Intramural athletics 56,000 60,000
Intercollegiate athletics 240,000 245,000
Insurance and retirement 520,000 560,000
Interest 75,000 75,000
PROBLEM 7-45
Continued
Tuition is $70 per credit hour. In addition, the state legislature provides $780 per full-time-equivalent student. (A full-time equivalent is 30 undergraduate hours or 24 graduate hours.) Tuition scholarships are given to 30 full-time undergraduates and 50 full-time graduate students.
Revenues other than tuition and the legislative apportionment are:
2003-2004 2004-2005
Endowment income $200,000 $210,000
Net income from auxiliary
services 325,000 335,000
Intercollegiate athletic receipts 290,000 300,000
The chemistry/physics classroom building needs remodeling during the 2004-2005 period. Projected cost is $575,000.
Required:
1. Prepare a schedule for 2004-2005 that shows, by division, (a) expected enrollment, (b) total credit hours, (c) full-time equivalent enrollment, and (d) number of faculty members needed.
2. Calculate the budget for faculty salaries for 2004-2005 by division.
3. Calculate the budget for tuition revenue and legislative apportionment for 2004-2005 by division.
4. Prepare a schedule for President Redcloud showing the amount that must be raised by the annual fund-raising campaign.
Instructions:
1. Review the printed template called Problem 7-45 that follows these instructions. In some problems, some of the problem data have been entered for you in the data input section of the spreadsheet.
PROBLEM 7-45
Continued
2. There are 33 FORMULAS in the template that you need to provide to complete your analysis. Using the spaces provided below, write the formulas where requested in the template.
FORMULA 1 ______FORMULA 18 ______
FORMULA 2 ______FORMULA 19 ______
FORMULA 3 ______FORMULA 20 ______
FORMULA 4 ______FORMULA 21 ______
FORMULA 5 ______FORMULA 22 ______
FORMULA 6 ______FORMULA 23 ______
FORMULA 7 ______FORMULA 24 ______
FORMULA 8 ______FORMULA 25 ______
FORMULA 9 ______FORMULA 26 ______
FORMULA 10 ______FORMULA 27 ______
FORMULA 11 ______FORMULA 28 ______
FORMULA 12 ______FORMULA 29 ______
FORMULA 13 ______FORMULA 30 ______
FORMULA 14 ______FORMULA 31 ______
FORMULA 15 ______FORMULA 32 ______
FORMULA 16 ______FORMULA 33 ______
FORMULA 17 ______
- Click the Excel icon from the program manager screen to start the spreadsheet program. Click on the Open File SmartIcon/Button to retrieve the template for the problem. Then enter the 33 FORMULAS where indicated on the template.
PROBLEM 7-45
Continued
4. Click the Save File SmartIcon/Button to save your work.
5. Click the Print Button to print your work.