Please help with answering the 4 questions and preparing a excel spreadsheet analysis for this case study. Thanks!

HBSP Product Number TCG 219 THE CRIMSON PRESS CURRICULUM CENTER THE CRIMSON GROUP, INC. Moray Junior High School Educational material supplied by The Case Centre Copyright encoded A76HM-JUJ9K-PJMN9I Order reference F284827 Ms. Hilda Cook, Principal of Moray Junior High School, had just returned from the March meeting of the Moray Public School System's School Committee, where she had agreed to cut her school's budget by almost 12 percent. Although Ms. Cook did not consider her projected costs to be excessive, neither did several of the other principals, who also had agreed to attempt to reduce their budgets. BACKGROUND Moray Junior High School was one of three junior high schools in the town of San Pedro, Arizona. It was in excellent physical condition, and had an enrollment of approximately 700 students a year. The quality of education was considered extremely high, and a student-teacher ratio of no more than 15:1 had always been maintained. Among the school's special programs were a highly regarded Drug and Alcohol Awareness Program, and an Understanding Handicaps Program, in which trained parents and handicapped speakers provided a course of instruction to both students and teachers to acquaint them with the various handicapping conditions, such as epilepsy, blindness, physical handicaps, retardation, and deafness. Moray was best known, however, for its Spanish Language Program, which used native speakers of Spanish to teach courses that began in the 7th grade and continued through the 9th grade. A special language laboratory with 30 student \"stations\" and three instructor stations was equipped with the latest in audio technology, including an \"interrupt\" feature that allowed an instructor to listen in on a student practicing with a cassette tape and intervene electronically, when necessary, to correct the student's pronunciation or grammar. Students successfully completing the Program were considered to be extremely proficient in the Spanish language, and a special field trip to a \"sister\" junior high school in Anguila, Mexico was organized each year for the 9th graders. The students lived with local families for an entire week while actively participating in the Anguila school system's activities. As principal of Moray for over ten years, Ms. Cook had witnessed numerous changes in the school. For over 40 years, Moray had been the only junior high school in San Pedro; however, in the late 1970s, when migration from the northern U.S. had led to a large influx of new residents, additional demands had been placed on the school system. As a result, Moray had been expanded and two new junior high schools had been built. With such a dramatic increase in services, the School Committee had become increasingly concerned with budgeting, cost control, and accountability. Accordingly, in the past few years, Ms. Cook had become more actively involved in the financial management of Moray. Beginning with the current fiscal year, she, along with other principals in the San Pedro system, had assumed responsibility for constructing her school's annual budget. Moray's proposed budget for the upcoming fiscal year is contained in Exhibit 1. Budget Data San Pedro's budget process began in January. At that time, the Central Office made enrollment projections, and, using these figures, all school principals held conferences with their teachers and program heads to determine their school's requirements for staffing, supplies, and other cost items. _____________________________________________________________________________________________ This case was prepared by Professor David W. Young. It is intended as a basis for class discussion and not to illustrate either effective or ineffective handling of an administrative situation. Copyright 2012 by The Crimson Group, Inc. To order copies or request permission to reproduce this document, contact Harvard Business Publications (http://hbsp.harvard.edu/). Under provisions of United States and international copyright laws, no part of this document may be reproduced, stored, or transmitted in any form or by any means without written permission from The Crimson Group (www.thecrimsongroup.org) case centre Distributed by The Case Centre www.thecasecentre.org All rights reserved North America t +1 781 239 5884 e info.usa@thecasecentre.org Rest of the world t +44 (0)1234 750903 e info@thecasecentre.org Purchased for use by Sarah Detrick on 23-Nov-2016. Order ref F284827. You are permitted to view the material on-line and print a copy for your personal use until 23-Nov-2017. Please note that you are not permitted to reproduce or redistribute it for any other purpose. This budget cut is a serious problem for us. I don't know quite how to reduce our costs because there really wasn't much flexibility in the budget to begin with. However, we're all in the same situation; we expect to have only $2.2 million to spend on Moray and we have to find some way to live with that. We can't possibly cut teachers' salaries in the way you envision because the teachers are already overworked. We have to cover lunch and recess periods, and most of us substitute regularly during our break periods for teachers who are out sick. So we need a minimum of 1 teacher for every 15 students. Unless we cut down on students, we can't possibly reduce the number of teachers. Next, Ms. Cook met with Dr. Mariana Olivera, the lead teacher for the Special Education Program, and Ms. Lillian Higgins, the librarian. Ms. Higgins, the most senior of the two, discussed the use of books and other instructional supplies, and her ideas for reducing costs: The instructional supplies and library item does appear to be a large amount, but there is really nothing included in it that's excessive. I think we're already quite frugal in our supply use, and we can't just stop ordering pencils, paper, books, or anything else we need for instructional purposes. I do see one problem with the budget, however; we're budgeting for a full 690 students when, in fact, due to absences, we probably have only about 650 students in school at any one time. If we adjust the budget to reflect our actual attendance, we can cut costs by at least 5 percent. Dr. Olivera also had an idea for cutting costs. She suggested that the school reduce or eliminate the Spanish Language Program, thereby reducing the budget by almost $189,000. In considering Dr. Olivera's suggestion, Ms. Cook called the audio equipment manufacturer to discuss the resale value of some of the school's equipment. The company informed her that machines used for four years or more could not be sold, even for scrap. All of the equipment in Moray's language laboratory had been purchased at least five years ago. Ms. Cook also reviewed the salaries for the Spanish Language Program and found that $35,000 was for a lead teacher, with the remaining $50,000 designated for two regular teachers, at $25,000 each. No substitutes were budgeted since, in the case of a teacher absence, the aide could cover. She also noted that the program's size was limited by the number of teachers. That is, since a strict 10:1 student-teacher ratio was maintained, and the students attended the lab daily, the maximum number of students the program could accept was 180 (30 per class period with six class periods in a day). This did not mean that the lab equipment was fully utilized, however, since the nature of the instructional process was such that some days the students would not use the lab at all. As she reflected on the nature of the task before her, Ms. Cook realized that she had to consider the interactive effects of several factors. First, there was the question of the nature of the direct costs in her budget. Although Mr. Hartman had given her a good indication of how teachers' costs might _____________________________________________________________________________________________ Moray Junior High School June 2012 2 of 5 Purchased for use by Sarah Detrick on 23-Nov-2016. Order ref F284827. You are permitted to view the material on-line and print a copy for your personal use until 23-Nov-2017. Please note that you are not permitted to reproduce or redistribute it for any other purpose. Educational material supplied by The Case Centre Copyright encoded A76HM-JUJ9K-PJMN9I Order reference F284827 For the current fiscal year, all budget needs for Moray were calculated on the basis of a projected enrollment of 690 students, although not all programs served all 690 students. In particular, as Exhibit 1 shows, Regular Instruction was scheduled to serve 615 students, Special Education 75 students, and the Spanish Language Program 180 students. (As Exhibit 1 indicates, some students were enrolled in more than one program.) The student-teacher ratio in Regular Instruction was scheduled to be 15:1, while in the Special Education Program it was only 6:1. Shortly before the budget was completed, Ms. Cook and other principals met with the Director of Finance and Administration to discuss the Central Office costs. These \"indirect\" costs were allocated to individual schools based on measures such as salary expenses and student enrollments. The specific allocation bases for the upcoming budget year are shown in Exhibit 2. In reviewing her budget, Ms. Cook realized that the nature of the costs varied. She quickly ascertained that the budget contained no superfluous costs that simply could be cut; indeed, the instructional and administrative supply costs reflected only higher supply prices, and the teacher and administrative salaries were based on a very small increase in the wage rate. It appeared that if Ms. Cook wanted to reduce the budget, she would have to analyze the behavior of each cost, and adjust those that were flexible. If necessary, she also was prepared to alter Moray's operations to comply with the School Committee's budget ceiling. To prepare a modified budget for the School Committee, Ms. Cook decided to meet with some of Moray's teachers and program heads, who she thought could provide information concerning some of the budgeted expenses. Her first meeting was with Mr. Steven Hartman, the teacher with the greatest seniority in the school, and the designated representative of the teachers' union, to discuss the teachers' salary expense. Ms. Cook hoped to make substantial cuts in the teacher salary expense item by increasing the average class size from 15 to 20 students. Mr. Hartman's response was not particularly encouraging: Assignment 1. What is the average teacher salary for the Regular Instruction and Special Education Programs? 2. Analyze the costs in the category \"Direct Costs-Instruction,\" and classify each line item as either fixed, variable, semi-variable, or step-function. If variable, semi-variable, or step-function, indicate specifically how the cost behaves. How, if at all, is this analysis useful to Ms. Cook? 3. What are the budgetary options open to Ms. Cook? What are the cost savings associated with each? 4. What should Ms. Cook do? _____________________________________________________________________________________________ Moray Junior High School June 2012 3 of 5 Purchased for use by Sarah Detrick on 23-Nov-2016. Order ref F284827. You are permitted to view the material on-line and print a copy for your personal use until 23-Nov-2017. Please note that you are not permitted to reproduce or redistribute it for any other purpose. Educational material supplied by The Case Centre Copyright encoded A76HM-JUJ9K-PJMN9I Order reference F284827 change with changes in enrollment, the behavior of the other costs was less clear. Administrative salaries and supplies, she reasoned, would remain about the same regardless of the number of students. This would probably be true for operations and maintenance expenses as well. Instructional supplies and library expenses, on the other hand, would probably change in direct proportion to the number of students. A second consideration of Ms. Cook's was the level of indirect costs. When she called the Central Office to learn more about the allocation process, she was told that the distribution of indirect costs among programs within Moray used a different set of allocation bases from those used to allocate the costs to the school; these are shown in Exhibit 2. She also realized that at least some of the indirect costs allocated to Moray from the Central Office would change as both student enrollment and the level of Moray's direct costs changed. Nevertheless, she felt quite certain that the School Committee would hold her responsible for whatever amount was allocated. But then, if she was responsible for these costs, she wondered about the extent to which she could control or reduce them. Finally, Ms. Cook mused about Dr. Olivera's suggestion. Reducing or eliminating services did not seem appropriate, yet it might be the only way to meet the targeted budget reduction. If she were to cut the Spanish Language Program in half, she thought she might be able to reduce some of the Program's costs, but she was not at all sure. She also noted that, approximately two-thirds of the depreciation in her budget was for language laboratory equipment. As she began to prepare her budgetary modifications, Ms. Cook realized that Dr. Olivera's suggestion posed some very difficult issues. She decided to revise her budget first by making the appropriate changes in costs associated with an average attendance of 650 students. Only if this failed to produce the requisite reduction, would she consider cutting back the Spanish Language Program. However, in order to demonstrate to the School Committee the true impact of its request, she also decided to calculate what her average attendance in the Regular Instruction Program would have to be in order to meet the Committee's requested cut without curtailing the Spanish Language Program. Since several teachers were expected to retire at the end of the current fiscal year, she realized that if attendance levels were cut on a permanent basis, she might be able to get by without hiring replacements. Since Ms. Cook would soon be required to make employment offers for any new or replacement teachers, she realized that preparing revised budgetary projections and gaining School Committee approval for them was of the utmost priority. MORAY JUNIOR HIGH SCHOOL Exhibit 1. Budgeted Statistics and Expenses Statistics Number of registered students Number of days in academic year Number of potential students days Expected number of student days Attendance rate 75 180 $1,119,300 $37,200 $20,300 $84,870 $0 $12,300 $1,273,970 $376,875 $12,500 $9,100 $37,275 $0 $8,000 $443,750 $85,000 $0 $6,500 $8,280 $3,000 $40,000 $142,780 $15,000 $0 $3,000 $1,100 $0 $0 $19,100 $1,596,175 $49,700 $38,900 $131,525 $3,000 $60,300 $1,879,600 $87,655 $29,514 $6,657 $1,175 $125,000 $10,869 $3,660 $825 $146 $15,500 $175,500 $40,500 $27,000 $27,000 $270,000 $5,259 $279,283 $1,771 $75,444 $399 $34,881 $70 $28,391 $7,500 $418,000 $1,553,253 $519,194 $177,661 $47,491 $2,297,600 Indirect Costs -- Allocated from Central Ofce School Committee $2,419 Administration $30,362 Health/Life insurance $94,514 Operations and maintenance $4,739 Rent and depreciation $2,243 Contract services $1,048 Travel $581 Total $135,905 $815 $10,223 $31,824 $1,094 $518 $128 $196 $44,796 $184 $2,306 $7,177 $729 $345 $307 $44 $11,092 $32 $407 $1,267 $729 $345 $767 $8 $3,555 $3,450 $43,298 $134,782 $7,290 $3,450 $2,250 $828 $195,348 $563,990 $188,753 $51,046 $2,492,948 Direct Costs -- Administration [2] Administrative salaries (Regular teacher salaries) Administrative supplies (Regular teacher salaries) Operations and maintenance (Square feet) Other (Regular teacher salaries) Total Total Direct Costs Total Direct and Indirect Costs $1,689,159 Average Cost per registered student Notes: 1. Registered students do not crossfoot, since students are enrolled in more than one program. 2. Basis for allocation to programs shown in parentheses () 450 Total [1] 615 Direct Costs -- Instruction Regular teacher salaries Substitute teacher salaries Aide salaries Instructional supplies and library Travel and lodging Depreciation Total Educational material supplied by The Case Centre Copyright encoded A76HM-JUJ9K-PJMN9I Order reference F284827 Other Programs 690 170 117,300 110,497 94.20% $3,613 Purchased for use by Sarah Detrick on 23-Nov-2016. Order ref F284827. You are permitted to view the material on-line and print a copy for your personal use until 23-Nov-2017. Please note that you are not permitted to reproduce or redistribute it for any other purpose. Regular Special Spanish Instruction Education Language Program Program Program Moray Junior High School Exhibit 2. Allocation Bases Indirect Cost Basis for Allocation to Moray School Committee Administration Health/Life Insurance $5.00 per registered student $62.75 per registered student $.08 per teacher salary dollar (regular teachers, substitute teachers, and aides) $.027 per Operations and Maintenance Dollar in the school $5.00 per registered student $.018 per administrative salary dollar $1.20 per registered student Indirect Cost. Educational material supplied by The Case Centre Copyright encoded A76HM-JUJ9K-PJMN9I Order reference F284827 School Committee Administration Health/Life Insurance Operations and Maintenance Rent and Depreciation Contract Services Travel Basis for Allocation to Programs within Moray Proportion of regular teacher salaries Proportion of regular teacher salaries Proportion of regular teacher salaries Proportion of floor space: 65% to Regular Instruction; 15% to Special Education; 10% to Spanish Language Program; 10% to Other Programs Same as Operations and Maintenance Proportion of registered students Proportion of regular teacher salaries Examples of Calculations for Allocation to Programs within Moray School Committee Regular Instruction salaries = $1,119,300; Total salaries = $1,596,175. Proportion = .7012. Therefore Regular Instruction share = .7012 x $3,450 = $2,419. Administration Regular Instruction share = .7012 x $43,298 = $30,362 Health/Life Insurance Regular Instruction share = .7012 x $134,782 = $94,514 Operations and Maintenance Regular Instruction share = .65 x $7,290 = $4,739 Rent and Depreciation Regular Instruction share = .65 x $3,450 = $2,243 Contract Services Regular Instruction share = [615/(615+75+180+450)] x $2,250 = $1,048 Travel Regular Instruction share = .7012 x $828 = $581 _____________________________________________________________________________________________ Moray Junior High School June 2012 5 of 5 Purchased for use by Sarah Detrick on 23-Nov-2016. Order ref F284827. You are permitted to view the material on-line and print a copy for your personal use until 23-Nov-2017. Please note that you are not permitted to reproduce or redistribute it for any other purpose. Operations and Maintenance Rent and Depreciation Contract Services Travel