How to Create a Simple Budget In Excel

This is a monthly budget I put it together for a friend who was frequently overdrawing his bank account. It tracks daily expenditures and balance. While simple, it contains some unique features. Here is an example for downloading and modifying to your needs. It is not necessary to understand the formulas in order to use the budget spreadsheet. You can just change the categories of expenses, notes, target amounts and prepopulated expenses then record your daily expenses as they occur.

If you wish to see all the formulas in the spreadsheet at once:

• Windows PC - ctrl + ` (the acute accent key, found next to the number 1 on your keyboard)


• Mac - Cmd + `

Note that the columns automatically expand so as to reveal the formulas.

The primary purposes of this budget are to track expenditures, let users known what the ending month's balance is likely to be and pinpoint exactly where they stand at all times during the month .

• The white fill space (F6:AI31) is where users enter expenditures. Inserting explanatory comments in the cells can be helpful (right click on the cell, click Insert Comment and enter your comment. Comments are denoted by a small red triangle in the upper left corner of cells.

• Cell B3 [=MIN(INDIRECT(D3):AI5)] contains the lowest balance based on the following:
  
  
  
  • Cell B2 contains the likely ending balance based on projected income less the target expenditures [=F3+SUM(F4:AI4)-D32].
  
  
  • in cell D2 [=(HLOOKUP(TODAY(),F2:AI5,4))] which return today's balance and
  
  
  • cell D3 [=CELL("Address",INDEX(E5:AI5,MATCH(D2,E5:AI5,0)))] which returns the location of today's balance.
  
  
  
  


• And cell B4 contains the date that the lowest balance will occur [=INDEX(F2:AI2,MATCH(B3,F5:AI5,0))].

Row 3 beginning at Column F reflects the amount available as each day begins and Row 4 beginning at Column F reflects the total of the amounts incoming during that day. Row 5 beginning at Column F reflects the amount remaining at the end of the day. Rows 3 and 5 are computed automatically and Row 4 is input by users.

Each month is recorded on a separate Excel worksheet. To get started funds carried over from the previous are automatically recorded in cell F3 and the first date of the month is carried over from the last date of the previous month plus 1 into cell F2. The remaining cells in column 2 are computed by adding 1 to the previous cell. The days of the month are returned from a VLOOKUP function [=VLOOKUP(WEEKDAY(F2),$AM$2:$AN$8,2)] from the array at AM2:AN8.

The panes are frozen at F6 to facilitate entering and viewing date in the white fill area [View>Freeze Panes>Freeze Panes].

The light blue fill cells in Column D are the monthly target expenditures for each category. The red fill cells in Column E are the actual expenditures that seldom change so are prepopulated. The darker blue cells in Column E are the expenditures to be recorded as they occur. All the cells in Column E are computed by a simple sum of each row 6 thru 31 [e.g. =SUM F10:AI10)]. The totals of Columns D and E are in Cells D32 and E32. The debit row  (Row 32) sums each expenditure column. The cumulative debits and credits are reflected in rows 33 and 34 respectively.