Walden County Community College has 21,123 students and 1,562 faculty (full-time and part-time), in addition to staff and administrators. Every year, Barbara McLaughlin develops a budget that is ultimately presented to the colleges Board of Trustees to be approved before being sent to the county government for final approval. The college obtains revenue from student tuition, state and county contributions, and miscellaneous fees (lab fees, etc.).

Every year, the college tries to avoid raising student tuition. However, state and county aid sometimes does not always keep pace with increasing operation costs. Currently, while Walden County itself is doing fine economically, there is an economic decline in other parts of the state such that the college is not certain that state aid will remain steady. Indeed, it could decline.

Thus, it is important for the college to develop preliminary budgets so that they can project anticipated income and expenses. Thus, the college would be able to anticipate possible ways to respond depending on what the state legislature does.

A budget lists anticipated income and projected expenses, totals each, then subtracts the expense total from the income total to determine if there is a budget surplus or shortfall. If there is a shortfall, the college must determine how to close the budget gap -- cut expenses somehow or raise tuition or fees.

Ms. McLaughlin has asked you, her assistant, to prepare a preliminary budget projection for the next two years.

Use this attached file as a starting point. It has the income and expense categories and beginning amounts.

Excel Business Case 2.xlsx

CASE DETAILS

For now, Ms. McLaughlin is optimistic that state aid will hold steady and that student enrollments will continue steady, also. The countys contribution normally increases to keep pace with inflation. The college president is optimistic that, with the countys economy doing well, that the countys contribution may increase 6%. Employee benefits costs have increased 10% annually in the past and this trend is expected to continue. All other expenses should increase at their usual rate of 3% annually. Since the Board of Trustees approved an increase in the computer lab fee to cover additional expenses connected with keeping the college up-to-date by allowing wireless network access, miscellaneous receipts are expected to increase 5% annually.

One thing that I have found is that many students are stumped at how to calculate a percentage increase or decrease. For example: "...miscellaneous receipts are expected to increase 5% annually." That is a percentage increase of 5% per year. To illustrate how to do this, let's work with easy round numbers and assume that the miscellaneous receipts are currently $100 per year.

Start with 5% of $100:

5% is 5/100 (that's 5 over 100), or .05

You multiply to determine the percentage of a number. So,

5% of $100 is $100 X .05, or $5.

Now we need to calculate the (in this case) percentage increase:

$100 + 5 = $105

So, $105 is a 5% increase from $100. The general calculation for a percentage increase is:

beginning amount + (beginning amount X percentage)

You would substitute a - for the + for a percentage decrease, of course. (Another way to do a decrease is to leave the +, but use a negative percentage.) Also, I tried to use symbols that you may remember from math class. Of course, Excel does not recognize the X multiplication symbol, it uses the *.

So, for each of the remaining budget items, you need to calculate a percentage increase or decrease, where appropriate. The figures will also build or progress from one year to another. For instance, if the current year's miscellaneous receipts are $100, the next year's receipts would be $105, then the next year would be 5% greater than $105, or $110 (actually $110.25, but budget figures are usually rounded to whole dollars).

EXCEL HINTS THINGS TO REMEMBER

I would advise doing the unusual ones first (5%, 8%, etc.), then doing the topmost "all other" expenditure at 3% and copying its formula (use an absolute cell address!) to any blank spots remaining.

TASKS

If you have any questions about anything, please ask. Also, given the "Excel Hints -- Things to Remember", this should not be too difficult to do. If you find yourself spending a great deal of time on this, review the "hints" for Excel Case 1 again. If that doesn't help, check with me before continuing!

1.	In the Current Values area, add labels for the other percentages to the existing list. Add rows, if necessary.
2.	To the right of each label in the Current Values area (in column B or C, for instance), list the appropriate percentage for that budget category. Format the percentages as percentages.
3.	Use formulas to calculate projected income and expenses for the next two years. Remember the Excel Hints Things to Remember from both above and Excel Case 1! This should not be difficult to do if you remember and review the proper techniques. Be sure that the formulas reference the appropriate cell for the percentages in the Current Values section! It is all right if you use relative cell references for the calculations that are unique -- for instance, only Employee Benefits is 10%. However, you should definitely use an absolute cell reference for the "all other" for all those that will be 3%. As per the "EXCEL HINTS THINGS TO REMEMBER", you can do the topmost one, then use the Fill Handle until you reach a cell with a unique percentage. However, then you can copy ANY of the cells with the 3% cell reference to below that point and again use the Fill Handle. Be sure that the last year's calculations are based on those of the second year!
4.	Total the income and expenses for the next two years in the spaces provided. Use the Sum function or the AutoSum key!
5.	In the Income minus expenses row, subtract the total expenses from the total income in the spaces provided for the first year and the next two years.
6.	Adjust column widths, if necessary, so that all of the amounts display properly. Format each column using the Comma Style button. (I don't think that your textbook used this one -- it's to the right of the Percent Style one (%) and has a comma icon.) Then, because budgets are always whole numbers, remove any decimal places.
7.	Save your changes (if you haven't already).