Balancing the state college budget: why must tuition increase and by how much?(Instructor's Note)

Bradbard, David A. ; Robbins, D. Keith ; Alvis, Charles 等

CASE DESCRIPTON

The primary subject matter of this case concerns (1) managerial responsibilities with respect to planning, budgeting, and controlling and (2) managerial decision making with the assistance of a decision support system. Secondary issues examined include the use of Excel spreadsheets to construct two-variable data tables and engage in sensitivity analysis. The case has a difficulty level of: appropriate for junior level or senior level management or finance courses. The case is designed to be taught in one 75-minute class and is expected to require two hours of outside preparation by students.

CASE SYNOPSIS

The organizational setting for the case is a moderately sized public university with a total enrollment of 6,500 students. The major players are the Vice President of Finance (Percy Bradshaw) and the Director of Academic Computing and User Support (Gerald Radner). It is early summer and Percy is attempting to finalize the university's budget for the coming academic year. His task is complex for several reasons. First, the state legislature has not completed deliberations with respect to allocations of funds for the coming year. Second, there was a budget shortfall in the current fiscal year that was covered by the university's reserve funds. The university president has stated this cannot happen again.

Percy must present the president with a draft of the budget and he is feeling overwhelmed. Fortunately at this crucial decision time, Percy has a chance meeting with Gerald. As a result of this meeting, Gerald offers to show Percy several tools from Excel that might make Percy's task easier.

Percy needs a way to present the budget so it can quickly be revised as the situation changes. In the case, Percy explains the major variables related to expenses and revenue. Based on this discussion, Gerald shows Percy a way to systematically vary tuition revenues to examine the effect of this variable on the budget.

As state support for universities has declined, most state schools have responded by increasing tuition to offset the diminished funds from the state. Public state universities generally enroll both in-state and out-of-state students. Usually, state universities charge in- state students a lower tuition rate than out-of-state students. The higher tuition paid by out-of- state students provides an incentive for schools to enroll more out-of-state students.

INSTRUCTOR'S NOTES

CASE GUIDELINES

This case is intended for a principles of management or finance course required in most undergraduate business curricula. Students should quickly warm to discussions of such sensitive issues as tuition price increases, student user fees, tuition price discrimination (e.g., like airline seats if you polled five students in a classroom you'd find that no two pay the same tuition), net present value estimates for college education, faculty salaries, and faculty student ratios or class sizes. Students and their parents confront these issues on a year-to-year and semester-to- semester basis. Many students are incurring debt as a means to invest in their financial future. Two areas where the case is relevant include: (1) managerial responsibilities with respect to planning, budgeting, and controlling and (2) managerial decision making with the assistance of a decision support system (i.e., a spreadsheet set up for what-if analysis). The case can be assigned as soon as these topics have been covered.

With respect to area one, the objective of the case is to put students on the managerial spot where they can experience the difficulties of planning, budgeting, and decision-making in a real life situation with which they are relatively familiar. Regarding area two, the objective is to show students effective spreadsheet techniques (i.e., what-if analysis) that can be used for planning, budgeting, and decision-making situations. By using what-if analysis, students can systematically change the input variables to determine the effects on the output variables.

TEACHING APPROACHES

This case was assigned for use in a 75-minute class. The case and the questions (see below) should be assigned prior to the class. In the beginning of the class, students share their responses to the case questions. Instructors may find the anecdotes contained in Appendix A useful for this portion of the class. Approximately 30-35 minutes would be devoted to this activity. Individual student responses to the questions should be collected and graded.

The remaining class time would then be used to discuss the various spreadsheet models. The instructor may use the spreadsheet models in different ways depending on the class's experience level with Excel. For some classes with little experience, the instructor may want to distribute the spreadsheet models and let students experiment with changes in selected variables. For more experienced students, the instructor may ask students to build additional data tables. Should the instructor be interested in providing students with instruction related to data tables, the material in Appendix B will prove useful.

ASSIGNMENT QUESTIONS AND ANSWERS

1. Other than raising tuition or increasing enrollment identify other strategies for the university to increase its revenue stream.

Answer: State appropriations, contracts and grants, and other are the possible areas where the university can increase revenues. With respect to state appropriations, the university can try to persuade state legislators to increase support for higher education. This is an excellent approach for universities if they can convince state legislators of the value of higher education. The difficulty of this approach is that it often requires cooperation of other state universities in order to be effective. More than likely this activity is a long-term rather than short-term solution.

This part of the question provides an opportunity to discuss some of the politics of state supported universities within your state. The best students should be able to bring recent history about state funding of higher education into their answer.

Contracts and fees that the university receives from outsourcers that provide dining services, housing management, or managing the bookstore should be reviewed to determine if the university can save money in this area. Alternately, if the university is not outsourcing these services, then this alternative should be examined.

Contracts for these services are usually multi-year contracts so if the university is already under contract not much can be done. However, for schools not under contract or renegotiating contracts, there is a potential for savings. Depending on the universities situation, this could be either a long-term or short-term solution.

For contracts and grants relating to faculty research, the university can encourage faculty to seek outside contracts and grants. If faculty are successful, these activities can provide monies for new equipment, laboratories, etc. In addition, contracts and grants provide financial support for faculty and students. Unless the university has already developed a successful support system for faculty to pursue contracts and grants, this is a long-term strategy because it takes time to develop the support infrastructure and it takes time for faculty to develop the necessary grant application skills. The university may also have to change the reward structure to encourage faculty to pursue contract and grant applications. If the university can develop a history of successfully competing for contracts and grants, it can lead to an important selling point for attracting faculty and students.

In smaller public institutions students may not have much of an understanding of how contracts and grants work. The instructor may have to discuss how this process works.

With respect to the category other, this will vary from institution to institution.

For some universities, increasing the size of endowed funds is a way to increase revenue. Capital fund drives also serve a similar purpose. For many smaller state institutions this is not a significant part of the revenue stream. The use of endowed funds is a point that brighter students may raise.

2. Consider the university's expenses; identify strategies that the university can pursue that will reduce expenses. For each strategy, discuss whether it is a short- or long- term strategy. For each strategy, discuss the pros and cons.

Answer: The expense variables in the model include: faculty salaries, staff salaries, faculty fringe benefits, staff fringe benefits, services and supplies, and utilities.

Regarding faculty salaries, the most dramatic way universities have reduced this expenditure is to eliminate faculty positions (likewise for staff positions). Reducing the number of course sections or increasing class sizes can also reduce expenditures for faculty salaries. Other cost-savings can be achieved by leaving vacant positions unfilled (or fill them with part- time faculty) or discontinuing low-demand programs.

With respect to reducing expenditures for faculty salaries (short of eliminating faculty positions), universities have limitations for the following reasons: (a) for new faculty, salaries are often determined by discipline and market condition (e.g., supply and demand). This is also true with respect to veteran faculty and (b) in many states, faculty are considered state employees. Where this is the case the state legislature may mandate a salary increase for all state employees and the university must comply. (c) At universities that have unionized faculty, salaries are determined by collective bargaining between the faculty union and the university. (d) As mentioned above, some economies can be achieved by increasing class sizes or faculty workload. For example faculty could teach four courses per semester rather than three. These strategies have limits due to accreditation standards and/or availability of large classrooms. Further, increasing class sizes may be inconsistent with the university's image. For example, a big part of some universities attractiveness is small classes. (e) As mentioned above, hiring part-time or non-tenure track faculty is a viable strategy for reducing faculty salaries, but there are limits on this approach due to accreditation standards.

For staff salaries, some of the same issues mentioned for faculty apply. For example, some staff are considered state employees, some staff positions are effected by market conditions (e.g., director of computing), and unionization. Most staff positions are related to supporting some part of the university community whether it is students, faculty, alumni, prospective students, parents, or the surrounding community. The Internet provides a universal way to achieve economies by empowering members of the university community to do things on their own. For example, students can use the Internet to pay bills, register for courses, access library facilities, or purchase transcripts. Similar activities apply for the other members of the community. Use of the Internet enables staff to service more constituents without increasing staff size. Further, costs are reduced due to less handling of paper and reduction of errors.

Outsourcing some staff functions is also a potential way to economize on staff salaries. Many universities outsource food preparation and dormitory management. For secretarial staff that supports faculty and administration, there may be some economies by pooling secretarial support rather than assigning full-time secretaries to small academic units.

With respect to fringe benefits for faculty and staff, this is another area where public universities have little control. Benefits such as retirement, health insurance, and life insurance are often determined at the state level. Since most university employees are considered state employees, there is little the university can do to control the costs of these benefits.

For services and supplies, the university should take the following steps: Contracts with outsourcers should be periodically reviewed to determine if the university is paying competitive prices for these services. For supplies, the university should consider centralized ordering policies to take advantage of bulk discounts, long-term contracts with suppliers, or join a consortium with other universities to increase their bargaining power.

Utility costs can be lowered by improving the energy efficiency of buildings and facilities. For new construction, this should be a major design consideration. For older structures, the potential energy savings must be weighed against the costs of improving energy efficiency of these structures. With respect to telecommunication services, in most areas there is intense competition in this industry. The university may be able to benefit from this competition.

3. Describe factors that raise a university's costs and contribute to tuition increases.

Answer: In public universities, tuition has never covered the entire costs of instruction. Most of the short-fall has been made up through state support for public universities. In recent years, public universities have been caught in a vice; enrollments have been increasing but state support has been decreasing. Universities have responded by raising tuition. Increases in enrollment not only increase costs for instruction, but also increase the cost for student support services.

Universities have also had to use financial resources to stay abreast of new technologies. Schools must constantly upgrade laboratories and information technology to assure that their facilities are compatible with the outside world.

There are many costs that are uncontrolled mentioned earlier such as the cost of energy and health insurance for faculty and staff.

4. Why does the tuition revenue not equate to the number of students times the stated tuition price for in-state plus out-of-state students at Winegar University?

Answer: As described in the case, the determination of student tuition revenue would seem to be the result of the following calculation: [Total Tuition Revenues = (number of in- state-students * in-state-tuition) + (number of out-of state-students * out-of- state tuition)]. In reality, this calculation is considerably more complicated.

There are several additional factors university budgeters must take into consideration when projecting student tuition revenue. These factors include: the number of (1) full scholarships, (2) partial scholarships, (3) out-of-state tuition waivers or reciprocity agreements, and (4) work study programs. As a result of these factors, many students receive a tuition discount of some form and the university nets far less than full tuition for each student.

The line item student tuition revenue in the university's budget includes (1) revenues from students paying the full cost of tuition, (2) Pell Grants, (3) endowed scholarship annual interest income, and (4) athletic program earnings. The latter three revenue sources can fluctuate widely and add complexity to the budgeting process. For example, with a significant down-turn in the economy, there will be little or no annual investment interest income to be used to fund scholarships. This shortage will have to be encumbered elsewhere in the budget.

Similarly, the amount of monies needed to fund scholarships and out-of-state tuition waivers is also a moving target. Some of the factors influencing the funds needed for scholarships and tuition waivers would typically include: (1) How many high achieving students with strong SAT scores are to be given a scholarship in order to attract them to the institution? (2) How many athletic scholarships are required to support the institution's athletic goals? (3) How many of the students provided with tuition waivers or scholarships will be out-of-state students? (4) What is the expected increase in student enrollment for both in-state and out-of- state students?

5. Since out-of-state students pay more tuition than in-state students, discuss the pros and cons of increasing out-of-state enrollments.

Answer: Since out-of-state students pay more tuition, an attractive option for many schools is to increase out-of-state enrollment. If the university has a good academic reputation, the school may be able to attract high achieving students. Because these students are from out- of-state, they automatically increase the diversity of the student body. Since admission standards for out-of-state students can be higher than the comparative standards for in-state students, the out-of-state students can raise the academic standards of the institution.

This strategy generally causes no problems if the university has the capacity to absorb these students. However, if out-of-state students are displacing in-state students, this will usually cause problems for the university. State legislators and parents of prospective in-state students are not likely to be in favor of increasing out-of-state enrollments at the expense of in-state students.

6. Discuss strategies and policies that your university employs to increase revenues and decrease expenses.

Answer: There is no universal answer to this question as it will vary from school to school. This is a good opportunity to determine how aware students are of university policies.

EPILOGUE

The university that served as the basis for background and budgetary information for the Winegar University case actually experienced a worse than expected worst case scenario. The state general assembly held an emergency session in October 2008 due to the fact that state revenue projections had vastly underestimated the rate of flow of taxes and fees into state coffers. That meant the legislators had to revise the appropriations that had been given each state agency for the fiscal year that began July 1, 2008, and ended June 30, 2009.

The legislature decided to cut public higher education institutions up to 15 percent. At "Winegar" the actual cut was $3.4 million equal to 14.8 percent of the entire state funding. The university's initial response was to cut its spending by $3.2 million and to cover the remaining $215,000 shortfall via a mid-year student assessment fee of $50.

Two months later the budgetary downward spiral had yet to subside and the state ordered an additional 7 percent cut to the university's state appropriation. This resulted in the university imposing a nine-day furlough plan to compensate for the budgetary shortfall. The furlough covered all university personnel and resulted in a savings of nearly $200,000 per furlough day (it should be interesting to see how students respond to the notion of student fee increases and/or faculty furlough days). Faculty furlough days were to be taken on non-teaching days so as to be as minimally disruptive as possible.

The additional 7 percent ($1.6 million) cut comes on top of the $3.4 million cut from Winegar's state appropriation by the legislature in October and the $735,000 reduction to its annual appropriation level that took effect last July 1. Therefore for fiscal year 2008-2009, the actual state appropriation was $5,735,000 below the previous year's level or nearly twice as severe as the forecasted worst case scenario.

REFERENCES

American Association of University Professors (2004). Don't blame faculty for high tuition: The annual report on the economic status of the profession 2003-04. Retrieved from http://www.aaup.org/surveys/04z/zrep.htm

Christian Science Monitor (2003). Costs soar at public universities. Retrieved from http://moneycentral.msn.com/content/CollegeandFamily/P57654.asp

Magrath, P. C. (2003) NASULGC statement on college costs. Retrieved from http://www.nasulgc.org/Public%20Affairs/Collegecosts10-15.pdf

Parsons, J., Oja, D., Ageloff, R. & Carey, P. (2010). New perspectives on Microsoft Excel 2007, Comprehensive. Thomson Course Technology:Boston, MA.

Winston, W. L. (2004). Microsoft Excel data analysis and business modeling. Microsoft Press: Redmond, WA.

APPENDIX A: ANECDOTAL COMMENTS THAT SUPPORT THE RESPONSES FOR ASSIGNMENT

QUESTIONS 1-3

The following quotes and anecdotes come from the Christian Science Monitor (2003) "Since 1993-94... average tuition and fees have risen 47% ($1,506) at four year public colleges and universities...'' (p. 1)

"Over a lifetime, the gap in earnings between those with a high school diploma and a B.A. or higher exceeds $1,000,000, the College Board reports." (p. 2)

"... the average yearly cost to attend a four-year public institution is 71% of the annual income of a family in the bottom economic fifth of Americans, ..." (p. 2)

The following quotes and anecdotes come from a statement presented by C. Peter Magrath (2003) while he was president of the National Association of State Universities and Land-Grant Colleges (NASULGC) Land-Grant Colleges.

"The Department of Education estimated in 2002 in Projections of Education Statistics to 2012 that enrollment in degree-granting postsecondary institutions would increase more than 15 percent between 1999-2000 and 2011-2012." (p. 2)

"The share of states' general fund spending devoted to higher education dropped from 14.6% in fiscal 1990 to 12.7% in fiscal 2002, according to the National Association of State Budget Officers." (p 2)

"In a 1999 NASULGC study, members reported spending an average of about 5% of their operating budgets on information-technology expenditures - a category of spending that exploded during the past decade. They reported that they typically had to patch that funding together from a variety of sources since few federal or state programs provide direct support for such expenditures." (p. 2)

"According to data from the Education Department's National Center for Education Statistics, on average, NASULGC institutions received 14.4% of their current funds revenue from tuition in 1990-91 and 18% from tuition a decade later in 2000-2001. State appropriations made up 34. 7% of the revenues in 1990-91 and 29.3% of revenues in 2000-01." (p. 2)

"The University of Virginia estimates that it has saved almost $2.9 million by burning 89% coal, instead of gas, in its main heating plant, and saved $2.7 million form using central plants to chill water rather than doing this in individual buildings." (p. 3)

"Many institutions have modernized their lighting systems for considerable savings. The University of Virginia calculates total savings of $818,000 from lighting upgrades." (p. 3)

"Many institutions have saved considerable money through improving purchasing systems or joining purchasing consortia. The University of Michigan spearheaded formation of the Michigan Life Sciences Purchasing Consortium, covering itself and three other institutions .. The consortium successfully negotiated contracts with four of the biggest suppliers of life-science materials .... resulting in savings for Michigan of about $95,000 in fiscal 2002 and savings of $550,000 for the other institutions." (p. 4)

"The University of Missouri estimates that about $6 million will be saved through a variety of consolidated standardized procurement and purchasing services, including use of an insurance consortium." (p. 4)

"Many institutions have upgraded administrative and financial systems to use electronic rather than paper systems to save processing costs,...For example, Oakland [University] estimates that it saves $50,000 a ear in paper and staff time by having its accounting department send monthly financial reports to campus departments electronically rather than sending paper copies.".. (p. 4)

"A great many universities have contracted with private companies to save money on items including food services, book stores, custodial services, and vehicle maintenance. The University of North Carolina at Chapel Hill outsourced operation of an inn on campus, which now is generating rent payments of more than $1.5 million annually." (p. 4)

"Institutions also closed low-demand programs and reallocated funds to upgrade the quality of high- demand programs. For example, the Pennsylvania State University will save $464,000 by closing a mineral economics program and merging its business logistics program with its management science and information systems program." (p. 4)

"The University of Binghamton-SUNY eliminated its MBA in the Arts program to free funds for other programs. The University of Mississippi eliminated several masters programs, including its Master of Fine Arts in Theater Arts program so as to focus resources on undergraduate programs, including its Bachelor of Fine Arts." (p. 4)

The following quotes and anecdotes come from The Annual Report on the Economic Status of the Profession 2003-04 (American Association of University Professors [AAUP], 2004).

"The rate of increase in the consumer Price Index between December 2002 and December 2003 was 1.9 percent, a smaller increase than the previous year's 2.4 percent. Given that average faculty salaries increased by only 2.1 percent, the real increase in average faculty salaries this year was extremely small: .02 percent." (p. 23)

"The data collected for the AAUP's salary survey are for full-time instructional faculty. In recent years, however, a growing share of faculty have been employed in contingent positions -- part- or full-time non-tenure- track appointments." (p. 24)

"The cost to colleges and universities of the medical and dental insurance they provided to their faculty members averaged 8.1 percent of faculty salaries in 2003-04. The comparable percentage for 2002-03 was 7.6; five years ago, in 1998-99, the percentage was 6.2." (p. 24)

"... colleges and universities often claim that faculty salary increases are among the major reasons that tuition persistently increases an average of 2.0 to 3.5 percentage points more each year than the rate of inflation. This past year's experience suggests that this argument does not always hold. As has been noted, tuition and fees rose by an average of 6.0 percent at private four-year colleges and universities between 2002-03 and 2003-04 and by 14.1 percent during the same period at public two- and four-year institutions... however, that average faculty salaries at private four-year institutions rose by approximately 3 percent this past year, and average faculty salaries at most public two- and four-year institutions rose by less than 2 percent." (p. 29)

"Annual rates of increase in faculty salaries between 1990-91 and 2002-03 were again substantially less than the annual rates of increases in average tuition and fees." (p.30)

"The bottom line is that although faculty and staff salary increases obviously contribute to increases in tuition, other factors have played more important roles during the last quarter century. These factors include the escalating costs of benefits for all employees, reductions in state support of public institutions, growing institutional financial-aid costs, expansion of the science and research infrastructure at research universities, and the increasing costs of information technology." (p. 30)

APPENDIX B: DATA TABLES

A significant feature of spreadsheets is they permit users to vary the values of one variable and observe how these variations affect the values of other variables. Such activity is called "what-if analysis. What-if analysis is enabled in Excel through one- and two-variable data tables. Data tables are a way to organize and display the results of multiple what-if analyses. Excel supports two kinds of data tables: one-variable and two-variable.

One-Variable Data Tables

Data tables enable users to examine the relationships between input variables and output variables in a mathematical model. In Figure 1, a simple mathematical model appears that involves the relationship among the variables interest rate, term, loan amount, and total payments. The monthly payment is calculated by using the PMT function from Excel. The total payments variable is the product of the monthly payment and the term.

Data tables involve two important types of cells: input cells and output cells. An input cell contains a variable you want to vary, and an output cell contains a variable you want to examine under the variations of the input cell. Consider the mathematical model in Figure 1 where the values for the monthly payment and total payments are calculated based on the values of the interest rate, term, and loan amount. In this example, the variables in cells B3-B5 are all possible input cells while variables in cells B6-B7 are possible output cells. In the text that follows, we will develop a one-variable data table where the value of the interest rate cell (input cell) is varied to determine its impact on the monthly payment and total payment cells (output cells). This example enables users to determine how variations in the interest rate effect the monthly and total payments.

The general procedure for building a one-variable data table is to build a spreadsheet with the structure shown in Figure 2. In this structure, the upper left corner cell contains a reference to an input cell. The cells to the immediate right contain cell references to cells containing the output variables. As the diagram suggests, there can be more than one output variable in this type of analysis. The column immediately under the reference to the input cell will contain the possible values of the input variable.

[FIGURE 2 OMITTED]

For the car loan analysis, a one-variable data table would look like Figure 3. Once you have the spreadsheet in the form of Figure 3, select the rectangular range D3:E14. Next click on the Data option on the top line of the Excel screen, then click on What-if- analysis, and finally click on Data Table.

After this final click, you will see the Data Table window in Figure 4. Since the input variable is varied in a column, you fill in the Column input cell. For this example, the entry would be "=B3". When you click on OK, you are instructing Excel to substitute each value listed in the range D4:D14 into the input cell B3, and place the corresponding result(s) for the output variable(s) in the appropriate column(s) in the data table. Once you click on OK you will see the results shown in Figure 5.

[FIGURE 4 OMITTED]

One-variable data tables can have more than one output variables for a single input variable. Specifically, you could also examine how the total payments over the life of the loan vary as the interest rate varies. This is done by adding another cell reference to the output cell B7 as shown in cell F3 of Figure 6. Next you would select the rectangle defined by the range D3:F14. The remaining steps are identical to the previous example.

The appearance of the data table in Figure 5 can be improved if the entries for cells D3 and E3 are customized in the following manner. In particular, the data table would read better if the labels "Rates" and "Payments" were displayed in cells D3 and E3, respectively. This can be done by selecting cell D3 and clicking on Number/Format/Cells. The Format Cells dialog box appears in the left side of Figure 7. Click the Number tab and click Custom in the Category list box. Delete the contents of the Type box. Enter "Rates" in the Type box as shown in the right side of Figure 7, then click on the OK button. The label "Rates" will display as the column heading, but the underlying formula is used in all calculations. In a similar manner, cell E3 can be formatted to display the word "Payments."

[FIGURE 7 OMITTED]

Two-Variable Data Tables

Excel also permits data tables where you can simultaneously vary two input variables and observe the effects on a single output variable. For example, in the car loan analysis, you might want to simultaneously vary the interest rate and the terms of the loan to see the effect on the monthly payment. The general format of the two- variable data table is shown in Figure 8.

For the car loan analysis, we might want to vary interest rates from 4% to 5% in increments of .01%, and at the same time vary the terms of the loan from one to five years (i.e., 12 to 60 months in increments of 12). The starting layout of the spreadsheet appears in Figure 9.

Once you have the spreadsheet in the form of Figure 9, select the rectangular range D3:I14. Next click on the Data option on the top line of the Excel screen, then click on What-if- analysis, and finally click on Data Table. The data window shown in Figure 4 will appear and it should be completed as shown in Figure 10. By filling out this window, you are requesting Excel to systematically substitute pairs of values in the ranges E3:I3 and D4:D14 for B4 and B3, respectively. As each pair is substituted in the model a new value for the monthly payment is calculated and placed in the data table. In other word, you are asking Excel to try all possible combinations of values for ranges specified for the input variables and place the corresponding value of the output variable in the data table.

[FIGURE 10 OMITTED]

The results for the two-variable data table are shown in Figure 11. Note that the cell in the upper left corner was formatted to display the column heading "Rates."

Additional material on one- and two-variable data tables is available in books that specialize in Excel (e.g., Oja, D., Parsons, J, Carey, P., & Ageloff, R., 2010; Winston, 2004). Figure 1. Car loan analysis with formulas. A B Formulas used in column B 2 Car Loan Analysis 3 Interest Rate 4.50% 0.045 4 Term (months) 60 60 5 Loan amount $15,000 15000 6 Monthly payment $279.65 =PMT(B3/12,B4,B5)*(-1) 7 Total payments $16,779 =B6*B4 Figure 3. Set up for a one-variable data table. A B C D E 2 Car Loan Analysis 3 Interest Rate 4.50% =B3 =B6 4 Term (months 60 .040 5 Loan amount $15,000 .041 6 Monthly payment $279.65 .042 7 Total payme $16,779 .043 8 .044 9 .045 10 .046 11 .047 12 .048 13 .049 14 .050 Figure 5. Results for the one-variable data table. A B C D E 2 Car Loan Analysis 3 Interest Rate 4.50% 4.50% $279.65 4 Term (months) 60 4.00% $276.25 5 Loan amount $15,000 4.10% $276.93 6 Monthly payment $279.65 4.20% $277.60 7 Total payments $16,779 4.30% $278.28 8 4.40% $278.96 9 4.50% $279.65 10 4.60% $280.33 11 4.70% $281.01 12 4.80% $281.70 13 4.90% $282.38 14 5.00% $283.07 Figure 6. Set up for a one-variable data table with two output variables. A B C D E F 2 Car Loan Analysis 3 Interest Rate 4.50% =B3 =B6 =B7 4 Term (months) 60 .040 5 Loan amount $15,000 .041 6 Monthly payment $279.65 .042 7 Total payments $16,779 .043 8 .044 9 .045 10 .046 11 .047 12 .048 13 .049 14 .050 Figure 8. Structure of the Two-Variable Data Table. Cell reference for Values of the first the output cell. input cell (no formulas). These are the values for the row input cell. Values of the second This part of the table input cell (no contains values of the result cell formulas). These when various combinations are the values of values for the first and of the column second input cells are used in input cell. the model. Figure 9. Starting layout for the two-variable data table. A B C D E F G H I 2 Car Loan Analysis 3 Interest Rate 4.50% =B6 12 24 36 48 60 4 Term (months) 60 .040 5 Loan amount $15,000 .041 6 Monthly payment $279.65 .042 7 Total payments $16,779 .043 8 .044 9 .045 10 .046 11 .047 12 .048 13 .049 14 .050 Figure 11. Result for the two-variable data table. Terms Rates 12 24 36 48 60 4.00% $1,277 $651 $443 $339 $276 4.10% $1,278 $652 $444 $339 $277 4.20% $1,279 $653 $444 $340 $278 4.30% $1,279 $653 $445 $341 $278 4.40% $1,280 $654 $446 $341 $279 4.50% $1,281 $655 $446 $342 $280 4.60% $1,281 $655 $447 $343 $280 4.70% $1,282 $656 $448 $343 $281 4.80% $1,283 $657 $448 $344 $282 4.90% $1,283 $657 $449 $345 $282 5.00% $1,284 $658 $450 $345 $283