Skip Navigation

Office of Undergraduate Studies

Helpful Tips for Your UNL Experience

Instructions for Financial Record and Planning Spreadsheet

 

The financial planning and tracking program consists of these instructions, a "Cashflow Planning" paper and pencil exercise, and a semester (17 week) "spreadsheet" preprogrammed  to accept, process and store the information you need for financial planning and record keeping. The program is tailored to respond to the unique needs of individual college students. It should be sufficient to allow you to track and plan your financial transactions.

After you have completed a semester, and saved the record, you can change the dates, remove the figures you have entered into the white cells on the sheet, and reuse it. It can be used for the several weeks of midwinter break (one does not need to use each column or row for the program to be effective); and it can be reused to cover the fourteen weeks (ordinarily) of summer. (The instructions for the averaging columns, V and W, will need revision if they are to work for these shorter periods of time.)  If you change the categories (see Appendix), the program should also work to track the cash flow of most of the small organizations or co-curricular activities you engage in while in college. Thus it will equip you to deal responsibly with your own money, and with some experience it will permit you to exercise responsible financial leadership among your peers. The Appendix will tell you how to make the adjustments for different dates, for time periods of different duration, or for different categories.

The Semester Cashflow Spreadsheet program consists of easily learned record keeping processes: keeping detailed information, reading summaries by category and time period, and forecasting future expenses and income. The system on this site will work with any combination of cash, checks and credit or debit card

At the outset, let's note several things that this activity is NOT designed for:

  • The spreadsheet program is not designed to figure why the balance in your check book and the balance on your bank statement do not match, (A step by step outline for this process can be found on this site under "Balance and Reconcile your Checkbook and Bankstatement".)
  • This program is not designed to help you pick out a credit card, or to determine interest on a credit card purchase. Information on these subjects can be found at "Money," "Establishing Financial Credibility," and "Credit."  Further information can also be found "Calculate your Debt" or directly go directly to http://bankrate.com/brm/calc/loancost_smm.asp
  • This activity is not designed to reflect your overall financial well being. It covers one semester only. The program will not show your savings (like long-term savings bonds given you by your family) or debts (like last year's student loans) that have no direct effect on this semester's income or spending. Tracking items beyond the semester, either past or future is outside the scope of this program.)
  • This is a spreadsheet.  It is not a data entry program where you can massage the stored information in a variety of different ways.  You may have such a program (say, Quicken) on your computer or you can get one for about $30 if you look around at various websites.
  • As written, this program will not be of much use for tax purposes.  It assumes you will enter net (i.e., after deductions of all kinds) income.  It could be adjusted to accommodate pre deduction income, but that would fill most of the cells now reserved for entries unique to you.
  • This program will not require (nor give you command of) an accountant's detailed report of debits and credits, culminating in a zero-zero bottom line.  It will not teach you double entry bookkeeping, nor the entry of negative amounts, nor other technical processes.

Nevertheless, for the purposes of planning and tracking a semester's money,  this spreadsheet should suffice:

  • The spreadsheet program on this site will plan and record financial information through a 17-week semester (15 weeks of class, one of holidays, and one of exams.)   If you  record your reasonable plans and expectations on the one hand, and if, on the other hand, you accurately record your actual income and expenses as they occur, this program's financial forecasting power will grow progressively more reliable.  If you make careful adjustments to your plans to meet your circumstances as the semester progresses, you will discover that this program is very helpful in the task of sound money management.
  • The spreadsheet provides level of precision  (rounded figures, whole dollar amounts, or down to the penny?)  entirely determined by your entries.  Early estimates should probably be rounded off  (to, say, the nearest $10), actual expenditure reports should probably be precise at least to the dollar level.   But the choice is yours.
  • The spreadsheet format will save you a lot of time and boring calculation. Just for fun, when you open the Spreadsheet file on this site notice that rows 25, 67, 69, and 70  and Columns U, V, W,  and Y are all lightly colored and filled with zeros.  Just enter one number, say a 5, into any white cell in column "C" (say row 12-24 or 31 to 66) and count the number of times it shows up in one form or another in these brightly colored cells, columns and rows over the Spreadsheet. This exercise shows how powerful this spreadsheet is as a tool for doing many calculations simultaneously.


Use of this Spreadsheet takes Three Steps:

 

Step One.  CashFlow Planning.  Here you do your best to predict the amount and the dates of your income and expenditures.  For help in doing this, go to the material labeled "Cash Flow Planning." Fill in the "Cash Flow Planning" charts, using the sources listed there for help when you get stuck.  Forecasting expenses is an uncertain science, especially when you are in a new situation. In many cases the first figures you enter will be rough "penciled in" estimates based on reasonable judgments that are often subject to adjustment in the light of events. This CashFlow Planning process does not produce a budget to which you must strictly adhere at peril of failure in managing your money.  Adjustments you may make should not discourage you: they reflect a learning process that will be an advantage for the future.

Step Two.  Understand the Spreadsheet.  To understand a spreadsheet generally, see Part I below.  To understand the Financial Planning and Records Spreadsheet, see Part II below.  Familiarize yourself with the Financial Planning Spreadsheet.  What you find in these places, supplemented by material below, should be enough to permit you to use the spreadsheet satisfactorily.

Step Three. Use of the Spreadsheet.   This step will show you how to enter and update income and spending estimates, and how to read the implications of your entries.

If you are already familiar with a spreadsheet you could skip quickly over Part One.  If you find yourself comfortable with the categories on this Spreadsheet, you can skim quickly over Part Two.  Part Three provides the actual instructions.

Enter nothing into the spreadsheet until you have read at least Part Three below.

Part One: A Spreadsheet

 

If you are unfamiliar with a spreadsheet you need to know that it is a very powerful and readily available tool.  Any computer equipped with Microsoft Excel (or any generic spreadsheet program) has the capability of generating a spreadsheet matrix.  The power of such programs lies in the swiftness with which it will simultaneously do a variety of mathematical functions with numbers you provide.  It keeps records and calculates so it is useful for things as various as keeping track of a store's inventory, serving as a teacher's grade book, or as in this instance, planning and tracking your money.  The ability to use a spreadsheet is a handy and marketable skill.

We start with definitions, place setting, and instructions applicable to almost any spreadsheet.

  • A "cell" is a single box into which data can be entered.
  • A "column" by conventional definition is a vertical stack of cells, each stack from left to right headed consecutively by a letter of the alphabet, beginning with A.  If a spreadsheet extends further than 26 columns, the next columns are labeled AA, AB, AC, etc.
  • A "row" by conventional definition consists of  a set of cells sitting to the left or right of one another in a straight horizontal line.  These horizontal rows are numbered consecutively from the top of the spreadsheet.
  • Each cell, then, lies simultaneously in both a column and a row, and is readily identified by the letter (stated first) and the number which correspond respectively to the column and row the cell occupies  (A1, B1, C1, etc. across the top row; C1, C2, C3, etc. down the third column.)
  • Customarily, the cells in a column down the left of a spreadsheet identify categories of items to be tracked (in this case income and spending categories.)  The cells in a row across the top will identify categories (in this case, weeks) for recording the items listed in each column.  If the spreadsheet gets large, as this one does, these identification lines may be repeated in the middle and end of the spreadsheet to facilitate orientation.  (We do this in the semester-long financial teaching spreadsheet, but not in the much simpler check balancing spreadsheet.)

Entering, Changing,  and Deleting:

  • If you want to enter an amount into a cell, just put your cursor on that cell and make your entry. Then press "ENTER."  Beware, though: this process will override anything (including instructions) already in this cells.  Make changes in the cells where the material is originally entered, not in the cells where the figures have been calculated into a resulting composite figure reflecting the aggregate of several entries.
  • To delete the contents of a cell, put your cursor on the cell and press the space bar.
  • To change the contents of a cell put your cursor in the cell and type the new entry.  Click on enter.
  • Unless you know how to reprogram the spreadsheet to perform the functions you want, do not add a new row or column.  Without special new instructions to the spreadsheet program, new rows or columns will not be integrated into the existing process.
  • In this Spreadsheet, only enter dollar figures into, or delete them from, white cells.  Otherwise you will  interfere with the results of the programmed calculations that appear in the colored cells.  Entering or erasing amounts in a colored cells will effectively erase the instructions that control the calculations the results of which are programmed to appear in that cell.
  • If you by accident erase instructions in a colored cell, pull down a new copy of the spreadsheet from the webpage and reenter the data.

Saving: Because it is so easy inadvertently to lose information or instructions, you are well advised regularly to save what you enter into this spreadsheet.  Then, if do you inadvertently erase a formula, you can easily copy saved data into the new spreadsheet.

Part Two: An overview of  this particular "Financial Planning and Record Keeping Spreadsheet"

 

To begin: print this "Instructions" file so you have it at hand when you look at the spreadsheet file. Yes, do it,  print it now. Begin reading the printed hard copy of the text.

First, let's get an overview of the spreadsheet and its  various elements. You might look at the whole as we go forward.  For a hard copy of the spreadsheet. Print it from the Spreadsheet site. (If you find you cannot get the spreadsheet to print on a single page, go to "print properties" and then click on "landscape" as opposed to "portrait."  Then go to "preview" and manipulate the margins until the spreadsheet fits on one page - about 40% of normal size.)

  • On this spreadsheet  the cells in which you enter data are white;  the colored cells are either filled already or blocked in heavy color. The colored cells that originally contain a dollar sign and three zeros are programmed automatically to calculate the numbers you put in the white spaces so as to give you totals based on your entries. If you enter dollar amounts into the colored cells, or delete from them, you will delete the commands that instruct the spreadsheet what calculations it is to perform. If you do that, you will need to either reenter the instruction into the cell or call up the website and download the spreadsheet again. (Since this spreadsheet contains so much information, it is good to save it frequently and print it out regularly so your records do not get lost.  Minimally, save it each week after you have entered new data on income, spending, and adjustments.)  Only enter or delete dollar information with your cursor in a white cell.  If you want to start your spreadsheet (or some part of it over), delete the relevant figures from the white cells: that will bring the figures in the  colored cells back to zero.

This spreadsheet as a whole can be looked at as having three parts:

I.    The identification rows and columns.
II.   The middle cells where income (C12-S24) and expenses (C31-S66) are entered.
III.  The rows (25, 67, 69, and 70 and the columns (U,V, X, and Y)  in which the automatic calculations appear.

I.  The Identification Rows and Columns That Surround the Spreadsheet:

  • The identifying letter for each Column appears in the Spreadsheet program at the top (Row 1) and near the bottom of the sheet (Row 74.)  The identifying number for each Row appears on the left and right (Columns A and AA.)   As you move across the spreadsheet on the screen  these identifiers move with you, so you can easily find the location of any cell. On the screen these letters and numbers appear once on the gray program border and once again each in the pink Rows 1 and 74 and in the pink Column A and AA. This apparent redundancy has been programmed because the first set will not print out so the second set serves as locators on printed copies.

Columns:

Columns B and Z, the second column from either side are also devoted to identifying the place and context of cells on the spreadsheet. These columns, along with interior column T,  indicate what goes in each row, specifically, the categories of Income and Expense.  The spreadsheet is so programmed that the information in Column B can always appear on the left side of your screen.  If you are getting another column in that place, put your cursor in the column just to the right of the gray numbers, and scroll to the right or left until you get to Column B. The repetition in Column T and Z to facilitate location on printed copies.

  • Column B Row 10 calls for whatever miscellaneous money you have in cash or checking account that you are bringing to this semester. This row permits entry only once, not for successive weeks.  Your later resources will be recorded below (see next item) as they come in.
  • Column B, Rows 12 through 24 list various resources or income sources common to college students. Cells B (T and Z) 20 down through 24 are left open so you can list your own particular resources or income not already listed.  If you do not need a row for one or another suggested category of income you can substitute another category of income or resource you do need.  (Just put your curser in the B column cell and type in the new category name and  press "Enter.") Note the Columns T and Z automatically copy your new Column B entry.  (This function is unique: Column B will not copy from T or Z, nor T or Z from each other.) Or you can leave cells in the unused row blank.
  • The cells in Column B (T and Z) from Rows 31 to 66 identify various categories of expense common to college students.  As with the "Income" section there are then several rows (57 to 66) left open for you to enter other expense categories appropriate to your own spending, E.g., you may have vehicle, pet, pharmaceutical expenses not common to all students. You may have no phone or utility expenses: again, if you do not use these cells the spreadsheet program will not be harmed. You can also change the content label of any of these rows without harm.

Identification Rows:

  • Rows 1 and 74 indicate cell by cell the letter identification of each column.
  • Row 2 (Columns B through R) contains the spreadsheet title.
  • Rows 4 &  5 (Column B) call for your name or ID. You may wish to put a code name in here, one which will allow you to claim the disk should it get misplaced but which will not allow someone who finds it to identify you with your private financial business.
  • Rows 6, 7  and 8 (Columns C-S) identify the time periods for successive entries, the beginning and ending for each week in the semester.  For ease of  location, these dates are repeated (Rows 26, 27, & 28 and 71, 72, & 73) between the Income and the Expense sections of the Spreadsheet and at the bottom. If you start this program late, it will not matter to the program; the spreadsheet will process what you enter irrespective of for how long.   (Of course, the averaging functions of Columns V and W will not work unless the divisor "17" is adjusted to the time period you are addressing.  If you use this same sheet again, the dates may be deleted and new ones put in without affecting the workings of the program. (See The Appendix.)
  • Rows 3, 29 and 68, (and parts of Rows 2, 4, 5, 10, 11 and 30) are dark divider rows marking borders of the different sections.

II.    The great mass of cells in the spreadsheet where you enter money figures.

  1. Income: Cells C 10, C12 to S24, a column for each week of the 17-week semester.
  2. ii.    Expenditures: Cells C31 to S66, again, a column for each week.   All the cells in this block are white; they are for you to fill out with dollar amounts.  (In the first week that you  use the spreadsheet enter estimates.  In the following weeks you will displace these early estimates with reports of actual income and spending.

III.    The cells for the calculated cumulative figures, Rows 25, and 67, 69 and 70, and Columns U through Y. With the exception of one category ("Column X" to be explained below), these cells are all automatically presented.

  1. Rows:
  • Rows 25, ("Wk's Total Income") and 67, ("Wk's Total Expense") from Column C to S are exactly what you expect: their cells present column by column the totals from the relevant income or expense cells for the week entered into each column in which you find them.
  • Row 69, "Wk's Cash flow." In this row, from Column C through S, appears the answer to the question "This week how much more (or less)  have I taken in than I spent?"  If the cashflow for a given week is positive (that is, if there is more income than outgo) the number in that week's cell on this row will appear in black and without any positive or negative sign. If the outgo has been more than the income, the cashflow for the week will be negative and the number for that week in Row 69 will appear in parentheses (an indication of a negative cash flow.)   These numbers reflect what you "penciled in" as expectations or what you have noted as actually taken in or spent for that week, depending on whether the figures in the column represent actual past facts or future expectations.  Because it is often well into the semester before  money from loans, scholarships,  parental support, or wages arrives, it is possible for the number in a cell in Row 70 to be negative without there being a cause for real concern. Money in savings can often be drawn on to cover the shortfall and that money can be replaced when scholarships, loans, wages, or allowances come in.
  • Row 70, "Cumulative Cash Flow." This row, from Columns C through S answers the question "What is my cash flow status for the semester through the end date of this column?"  Again, these numbers will be real or only reflective of expectation depending on which week you are looking at.   If, however, there is not enough income and savings to cover the expenses for the week, this can become a cause for concern. Row 69, Cumulative Cash Flow, automatically calculates this issue and alert us when cumulative expectations, commitments and outgo are exceeding current and expected resources.  It is good to be aware of the potential problem, because one cannot incur bills based on expectations indefinitely. After all, something like "Aunt Minnie's birthday check" may not come through as expected and there is often no way to effectively ask for it.  Illness or study priorities can interfere with work and so with wages.  Etc.  When Cell S70 and Cell U69 show up negative, or when they are positive but close to O,  then trouble may be becoming serious: you are approaching or have crossed that line where expenses will run beyond your projected capacity to cover them.  (Of course, if you have been salting money into savings each week you may have a cushion.
  1. Columns:
  • Column U ("Term Running Total.")   In this column appears a combination of the facts of your actual income and expenditures to date and your expected expenses and income for the full semester. It notes for both expenses and income what you have and what you expect to have cumulatively over the whole semester, category by category (row by row.) In the beginning of the semester your figures are largely conjecture; they represent informed conjecture no doubt,  but conjecture nonetheless. Obviously some items (tuition and rent, e.g.) can be known ahead of time. But most others (including some potential big ones like books and car expenses) are not predictable, so this column in the beginning of the semester is largely an estimate. That is ok, indeed  necessary.  Such estimates are the best baseline we can have for planning future expenses and how we will pay for them. As the semester progresses, more and more columns are filled  with actual, fact-based, information.  As that process continues, your room for flexibility and change diminishes as the bottom line figures are becoming more and more firm.  Again, note the power of cells U69 and S70: at any given time they provide the best informed report of  how the balance currently stands between your past and expected expenditures on the one hand, and your past and anticipated income on the other. The figures in these cells bear close watch an indicative of where your finances will stand at semester's end.
  • Column V ("Weekly Average"), category by category (row by row), divides your expenses and income by 17, the number of weeks in the UNL semester. This can be a useful figure when Row 67 looks like it is getting unmanageable early in the semester. If you look at tuition and book expenses as if they represented a constant level of expense each week for the semester, they can present an unsettling picture. But if, more realistically, we recognize that these costs are only occasional and largely early, then their impact can better be seen in perspective.  Averaging (Col. V) provides this perspective and largely early.
  • Column X (Original Budget Estimates), [yes, Column W has been omitted for the moment; it receives treatment just below.]  Column X is the only one in this section of the spreadsheet that will not fill automatically. It is a white column, that is, one left for you to enter the figures. Here you record for future reference what Column U originally said.

When, at the start of your work with this spreadsheet, you have finished calculating your anticipated expenditures and income for the whole semester, the totals will appear in Column U.   But U will be unstable, it will change each week as you enter factual figures in place of projected figures.  If you want to keep track of where your expectations have proven on or off target you will need to have retained a copy of the figures in the original Column U.  To keep that record, copy the figures from original Column U into Column X before changing any figures in cells C10 to S66 from expectations to factual reports.  This copying MUST be done manually, transferring only the values of Column U, not any of the programmed formulas.  (If it were to be done automatically, the figures in Col. X would change each week and the purpose of this Column, keeping a steady record of original projections, would be thwarted.)

So, as you complete your initial projections of income and expense, copy the material from Cells U10 through U 24 and U31 through U 66 to cells X10-24 and X31-66 respectively.  You will note that as you do so the computer will fill in Cells X25 and X67 automatically. When you enter actual income and expenditures into the spreadsheet between cells C10 and S66, the numbers in Col. U  will change to reflect the new updated entries. But in Column X your original estimates will remain unchanged throughout the semester. This allows you to make comparisons between what you had expected and what has happened.  No one's actual expenditures ever precisely match their expectations-life is not like that. But watching for significant differences between projections and actuality will help you make a better financial plan for the rest of this semester and for future semesters.

  • As you enter the figures from original Column U into Column X, Column W will show a set of averages from the first projections (matching the original Column X.) Column V, like Column U, changes over the semester;  Column W, like Column X,  remains constant.   Column V and W are so placed as to permit you easily to compare actual income and expenditures and those you had originally expected.
  • Monitor the lower right hand corner of the spreadsheet carefully.  We saw earlier (item II above) that occasionally a week's expenses could be larger than the income without leading one to expect serious damage. But when the running totals for income and expenditures show a negative cash flow, then the expenditures are moving not only beyond current income but also beyond the income which can be reasonably expected.  A look at Cells S70, and U69 or comparisons between W67 and U67 or between U67 and X67 can all be used to spot and confirm the signs of a pending cashflow crisis. They offer the earliest alert for the need to curb spending or expand income.

Part Three: The Process

 

I. Laying the Groundwork:
The first step of this process is the longest.  Because it can be stressful, it can seem tedious. Remember that as you go through the steps so you can stay the course.  This first step is to "pencil in" your expectations for income and expenditures for the semester. (To "pencil in" is a metaphor, as you know, for putting things down which will guide your initial steps but which you know may have to be changed later. I "pencil in" engagements in my date book for the Fall during the summer, knowing that in the press of the actual semester I may have to cancel them. Everyone understands the need for such flexibility and, although someone may be disappointed, if forewarned that the date is tentative they will not be too annoyed.)  It can be difficult to create these estimates, but it need not be as difficult as you might think.

  1. "Penciling in" expected income.
    This material suggests sources of information to enable you to estimate your income in an informed and efficient manner.  To begin this process, look at the material on Cash Flow Planning.  Using the hints offered there from various sources for the semester. Income in all the different categories should initially be calculated for each week, and placed in the column under the week each "income" occurs.   If you expect to receive money from your parents (or from a scholarship, or income from work, etc.) in the first week of class, enter the amount you expect in the appropriate row and in Column C, under 8/26.   If you do not expect it until October 1st, enter it in column covering that date (H.)   If you are not confident you can do this successfully, just begin.  It often happens that seemingly impossible tasks become manageable, once we actually begin to deal with them on a systematic, step by step process.

This program has eight pre-entered income categories (entered in Column B, Rows 12-19.)  You have room to add up to 5 more categories, one each in rows 20-24.   (You might have income from a second job, whatever.) If you do not need to use any of these rows, just leave it blank.  

  • You have some idea of what your income and resources might be for the semester from savings , expected parental assistance, loans, etc. Enter these in the cell appropriate to the category and time you expect to receive each.
  • Cell C10 is for checking account or cash that you have on hand to spend as the semester begins.  If you are saving something aside for use beyond this semester, you might not want to enter it here among your currently available resources, although you should enter it in Row 41as a drain on those resources if you keep adding to it during the semester.
  • Parental money may come all at once or in installments. Enter it as you expect it, in the appropriate week's column in Row 12.
  • If you will receive a lump sum from somewhere that you expect to parcel out in equal weekly installments, enter it for a moment in the appropriate row in Column X. Column W will immediately tell you what the weekly installment should be and you can enter that number in cells C through S on that row.
  • Your savings, or the savings you are willing to dip into this semester, are present in a lump sum and should go in  Cell C15. Other savings you expect to accumulate for use during the semester should be placed in appropriate cells along Row 15 as well.  (The cells in this row could match the figures in Row 47; but if you are saving for a later time, say for expenses over holidays or a break, then Row 15 figures may show up as less than those in Row 41.  Remember: this spreadsheet is tracking income and expenses within this semester, not your total net worth.)
  • If you have a steady job which provides a steady income, enter that in the cells along Row 16 when you expect to receive income checks (e.g., biweekly starting in the week of Sept. 1 [Col. D].)
  • If folks who love you will send checks at your birthday and have been doing so like clockwork for years, it might be prudent to enter them as gifts in Row 1.
  • If any of the amounts you actually receive turns out to be less than what you anticipated, then change that entry as soon as you know of the change. If, for example, your scholarship is delayed and you now do not expect it until the fourth week, delete the entry from Cell C13  (8/28) and enter it into Cell F13 (under 9/17.)  If it turns out that you work more hours than you expected in the 2nd week, enter the actual net income: replace the estimate in the row for work under 9/11 (Col E) with the accurate higher number in the row for "work" in the same column, etc.  The types of changes that are an effective part of this program are limited to these:
    • Substitute the real figures for estimates of actual income or expense each week.
    • Shift figures for future expected income (e.g., as you get, change, or quit a job.)
    • Lower expected  expenses in one row (say, for example, 43) and  apply the excess to, say, necessary book costs, or to savings, or whatever
    • You charge something, so you need to add the payments (and interest) to the estimated expense categories (Rows 44 and 45) each month.  Respect the difference between accomplished fact and flexible futures: Change no entry reporting actual past income or expenses, except to correct errors.  Only the future is subject to adjustments.

When you have entered all your expected income by category,  look at the cell at the intersection of Row 24 and Column U;  that is the report, positive or negative, if your expected income and resources for the semester.

  1. "Penciling" in expected Expenditures.
    When you begin this process, estimate your expenses for the entire semester.  Find the pencil and paper charts for roughing in these figures in on the Website, in the places noted under "Income."   Expenses in all the different categories should initially be calculated for each week, and placed in the column under the week each occurs.  If you expect to have to buy expensive books during the first week of class, enter the amount you expect in the appropriate row and in the column under 8/21.   If you do not expect to have to buy it until October 1st, enter it in that column. (Be careful here, the bookstores will begin returning unsold books around midsemester; after that buying it may get tricky.)  If an anticipated expense is delayed or canceled, go back into the spreadsheet and move or remove it.
    • After you have estimated your basic, largely inflexible, expenses, enter them in the appropriate category and date cells (rows and columns between C31 and S66.)    Then look at the total (U67), and then at the Cumulative Cash Flow (U70)  to see how much of your expected income and resource base remains. Allocate that remainder over the remaining categories and time periods, keeping in mind your own needs and habits.  If there is not enough to go around, you have just provided  yourself with ample warning to cut back somewhere or to expand your resource base.
    • At first, when you make estimates for the various categories, you need not put an estimate for every category for every week. For example, clothing. You do not know in advance how much you will spend or when. If you want to budget some amount, try putting $25 in the cell for the last week of August September, October and November, which will give you a total of $100 for the semester. You choose the suitable amount. When you are putting actual values in the sheet, and you come to the end of September and you have not spent the $25, take it out of the Expense part of the Spreadsheet.  Or if it should be $35, put that in. If your budget is tight, you can take $10 out of the October or November estimates, to accommodate the extra September spending.  You can always change estimates in future week cells and you will always replace an estimate with an actual value for a specific week.  (Note, however, that the later in the semester it gets, the less flexibility for compensatory shifting remains available.
    • This program has twenty-seven pre-entered expenditure categories (entered in Column B, Rows 31-58.)  You have room to add up to 8 more categories, one each in rows 59-66.  If you do not need to use them, you can leave one, some, or all of the cells in any given row blank. When you enter your expectations for the semester as part of this first step, you should probably feel free to enter whole dollar amounts: it is frankly impossible to estimate to the penny.   Later, when you turn to entering actual expenditures, you may want to be more precise.   Or you may want to round out to the nearest dollar. This is your spreadsheet and it should be done to your specifications to the level of detail that suits your needs.

You do not need to enter dollar signs, these have already been entered into the program.

If, on consideration you determine to shift your projections around and put different projections into one or another cell, just put your cursor on the cell, type in the new information and press "ENTER," and the cell will accept the new number in place of the old.   There is no need to do more to erase or delete.

Remember: put numbers only into white cells.

  1. Filling in Column X. The next step is to take the figures for the semester you have entered as they have been cumulatively calculated  in Column U and copy them into Column X. This, as you saw above, retains the figures from your originally estimated budget for comparison purposes for the duration of your active use of this spreadsheet.

The three parts of this first step generally takes up to a couple of hours, but it is well worth the effort to lay a solid groundwork. After that, the time demands are minimal.
 

II.  The follow-up process:

  1. Each day, in a flip page calendar on your desk or bureau, or on slips of paper in an envelope (whatever works for you) jot down notes (or stick in receipts) for that day's expenditures.
  2. Weekly. Pick one day of the week and use it consistently to transfer your notes and receipts from your calendar onto the spreadsheet category by category. Most calendars of this sort run for a month and their record-balancing day coincides with the arrival with the bankstatement.   This program calls for weekly entries--no later than the Wednesday after the close of the week whose records are being entered--because that is the limit of what most folks can consistently remember.  Later than that memory fails: then the "Miscellaneous" entries consume more and more of the record, and records labeled vaguely "Miscellaneous" are the least helpful for purposes of tracking or planning your finances.

Entering actual figures, representing real information. Here we enter the real figures from the daily record, displacing the earlier "penciled in" estimates. Income entries should be distributed among the various categories. To make new entries, put your cursor on the cell into which you wish to enter a change, type in the new figure, and press "ENTER".  There is no need to delete.  (Again, do not enter dollar signs for figures.   Do not add zeros for empty cells either. They just add clutter.) For real entries, as opposed to the ones "penciled in" at the start of the semester, you may want to use cents as well as dollar amounts. Again, your choice. If by some chance, an expected bit of income does not come in, use the same process to replace the expectation with the fact.  You can always add more in later if it appears, but it is not good practice to act as if the uncertain were certain.

Expenditure entries.  In the cells of Column B under the heading "EXPENSES" are listed categories for ways in which students ordinarily spend their money.   As with "Income," these categories may not all apply to you; where that is true in a given week, leave the cell blank for that category in the column for the appropriate week.   It will make no difference ro the work of the spreadsheet  if you never spend money for a particular category (say "housing," Row 31, if you are living at home for no rent.) The "Misc." row is really only meant for expenditures less than $1 that do not fit in any other category.   (Again, do not enter dollar signs for figures, nor zeros for empty cells.)  When you enter expenses, be sure to enter in what you have put on a debit or credit card, or drawn from your checking account.   Although these moneys may not yet be drawn from your account, they inevitably will be and so, when you make the purchase, they should be he entered into the spreadsheet in the week(s) you expect the bill.  Interest accruing on a credit card is often not predictable, but it should be entered when the bill is paid.  Although the actual costs of items are to be entered once (at the time of purchase), the interest on late or delayed payments is a separate expense that must be entered separately, generally, monthly each it is paid.

As you can see it is important that your estimates be realistic and your reports of actual income and expenditures be both full and accurate.  If you expect to (or do) spend significantly for entertainment (movies, betting, sports, barhopping, etc.) be sure you have a category for the expected costs-suitably masked, if need be.   If you have medical or pharmaceutical (therapeutical or recreational) needs, these too should be entered both as expected and as actual outlays as they occur.  You may want to disguise categories to thwart spying eyes, but it is foolish to omit significant expenditures.  (I am not advocating illegal activity, just that you work in ways that are realistic and that you not feel you must lie to yourself as well as hide from others. Frankness with others and with oneself reduces stress and as well as sustains character.)

Taxes.  Ordinarily (unless you develop a special interest in tracking taxes) include the tax on an item in the recorded purchase price and include the auto or housing taxes in the maintenance costs for those items.   Income taxes can be a significant extra bite and you may want to use a special category (a Row between 59 and 66) for State and Federal income taxes if you are using the spreadsheet in the spring semester.

Don't feel stress if you forget to enter a quarter for a parking meter, or 50 cents for a newspaper. This spreadsheet is not penny exact like the bank's balance has to be.  There are tradeoffs, and this being your spreadsheet, you make the choices.

Examining your current state:

  • Each week update the Spreadsheet by entering your actual income and spending in each row, substituting the real figures for the earlier estimates; doing so will provide you with information as to where you are now and where you can expect to be at the end of the semester.  If the figure as to where you will be, based on past spending and future expectations, shows in the red at the end of any given week (see Cells S70 and U69) you know you need to make adjustments.  Since you cannot change what you have already earned or spent, changes can only be made in the cells to the right of the current week's updated column.
  • So ask your spreadsheet:  Are you spending more or less, category by category, than you expected?   If, as is likely, you are spending more in some areas than expected but less in others, the latter areas may allow you some leeway to transfer expected but unused expenditure money from one to another category. For example, if by mid-semester your book bills are considerably higher than expected but (due to mild weather) your Utilities have been less costly than expected, you can reasonably adjust to a lower figure what you expect to spend for utilities and apply the newly discovered extra available money for needed book purchases. Your housing costs, e.g., should remain exactly what you have contracted for. Your food, entertainment and other expense cells may fluctuate from week to week and from your original estimates. The numbers in some cells, say those in the row labeled "Entertainment" (Row 43) may shrink toward $0.00 if you get into a bind and need to conserve resources.
Some folks regard such shifts in budgeting as somehow cheating, as not sticking to original commitments. For this process that opinion is nonsense.  Some expenses and some income are not predictable and to regard yourself as somehow failing if you make adjustments among such categories is foolish.   One could, after all, if text book demands come in lower than expectations not so high, move some money originally earmarked for books into savings. That would also be legitimate, even trustworthy. The point of this exercise is to discover where your money comes from and where it is likely to go, so you can control and predict it better. If your book expenses were underestimated, you have to know that so next year you can make more realistic estimates.

When you come to the end of your entries of expenses and income for a week, look to the bottom right-hand side of the spreadsheet. What do your running totals show for the week and for the semester? Are they in the black? Do you expect them to continue that way? If not, where are they getting out of hand? Where can you find resources to reallocate to bring income and expenditures into balance? What can you put off or cut back?  Etc. Or are you going to expand your income and resource base? How can that best be done without interfering with higher priorities, like staying in school and doing well there?

If your bottom line is showing up negative despite your efforts to transfer available funds, you may be looking at a significant shortfall. There are two other ways of making up shortfalls. One is to lower spending--either in some less essential category or across the board; the other is to work or work more. Either is legitimate, both have potential drawbacks--which you don't need me to reiterate.  Remember, the earlier you discover that you may have to work more hours, the fewer hours you will need to work each week to sustain your needs. Remember, too, that somewhere between 15 and 20 hours of work per week, grades begin to suffer noticeably.

Do not enter any dollar figures into a colored cell.  Doing so will obliterate the instructions to the spreadsheet that controls what goes into that cell.  If you do this, down load a new copy of the Spreadsheet from the website onto your disk and start again.

  • Saving your spreadsheet:  It is not difficult to copy the spreadsheet once you download it onto a hard drive or disk.  It is worthwhile to "save" separately each week's completed spreadsheet, and regularly, if less frequently,  to print out a current copy of your Spreadsheet for your records. This because it is so easy to press a wrong entry button and lose a significant amount of information. Lost information is more easily retrieved from hard copies or from hard drive or disk memories than from the laborious effort to retrieve and reenter scraps of paper.   If you keep careful records this semester,  estimating income and expenditures next semester ought to be easier, less time consuming and more realistic.  Your sense of realism, of your own needs and desires, your recollection of unexpected expenses will all be better grounded. So your financial planning and your economic progress should be safer, more secure.
  1. Later Use of Your Spreadsheet
  • At the end of the time period covered by the spreadsheet, you can (as described at the beginning of these Instructions) reuse it for other semesters, even other time periods, the year around.  See The Appendix.
  • If you do reuse the sheet for your own financial planning, the record of this semester's planned and actual cashflow will be a great resource for making your predictions and plans more accurate.
  • Further, it is not difficult to change the categories of income and expense so they are suitable for keeping  records for others, or for other uses, such as organizations. Mastering this process will make you attractive for leadership positions that involve money and trust that many others are leery about undertaking. If the computer will do all the calculating and your records can help you do soundly based planning you will be recruited for difficult but rewarding positions in the various organizations on campus and then later in life.
  • If you discover you enjoy this activity (some will), there may be a future, even a major,  for you in business, in accounting or management, for example.

The Appendix.  To adjust the Spreadsheet for future or alternative use:  Several different parts of this program can be easily adapted on your disk.  Do not try to adjust it on the website.  If what you are trying to do fails and you wish to try again, just download another copy of the spreadsheet from the website into your disk and start over.

  1. To adjust the dates, substituting one set of weeks for another:
    1. Using your mouse, go to Cell C7.
    2. Type in the day you wish to start your weekly cycle on.
    3. Press enter.  This should change all the date entries in all 6 lines containing dates to a cycle that begins on the date you chose.  (Cell C7 controls the dates in Rows 6 and 7, 27 and 28, 71 and 72.  No other cell is programmed to exercise that control.)

Note: Without much other change, different copies of this spreadsheet can serve for two semesters and an entire summer break; two seventeen week periods will cover the semesters, one seventeen week period will cover the time between Spring Commencement and the beginning of fall classes (perhaps with couple of weeks week over - usually at UNL 15 weeks elapse between those events.)  3 seventeen week periods add up to 51 weeks; the winter break will make up the remainder.   Adjusting it carefully will allow one to use the spreadsheet the year round, and thus develop a full year's record.

It is possible to change the length of time for which you use the spreadsheet, but this will affect the averages columns ( V and W) which are programmed to divide by 17 (for the number of weeks in a 17 week semester.)  To change the calculation of the averages to meet some particular new time period requires getting into the program instructions and that can get dicey.  To do this it is advisable to seek the advice of a person knowledgeable in the structuring of spreadsheets. 

  1. To adjust the categories of income or expenditure:
    1. Using your mouse, go to the cell in column B that identifies the category you wish to replace.
    2. Type in the new category.
    3. Press enter.  This should dump the current category and add the new one, not only in Column  B but  in Columns T and Z as well.  (Remember to do this effectively, you must enter the new category in column B; entering it into column T or Z will not work in the other two.)
  2. To adjust income or expense projections:
    1. Using the cursor, go to the cell (C10, C12-S24 for income, C30 through S66 for expenses) you wish to adjust.
    2. Enter the new projection.
    3. Press enter. This should adjust all the entries to in the colored cells below and to the right of the new entry.

Acknowledgment: The material at this website is designed to help you to work to track and allocate your money and to predict your financial future cash flow over the semester. This spreadsheet and instructions are heavily dependent on the initiative, expertise, advice and gentle corrections of Dr. Truman Hunter who kindly shared with me a prototype of this material which he had developed over almost 65 years of use.  Dr. Hunter has utilized this process as a student, "naval officer", a single person and married ( with and without children at home), "business  manager", and an adjunct professor at Miami University of Ohio and in retirement.  The errors are all my own. (Jim McShane.)

-- Written and/or complied by James A. McShane