A simple budget spreadsheet that actually works

A budget is an essential tool in managing your finances. It is most effective in ensuring your hard-earned cash is used wisely.

Still, many don't maintain a budget. I think it is mainly because the tools available are difficult to use - containing features only a few are interested.

To tackle this, we have to go back to the basics. In its simplest form, a budget allows you to plan and track where your money is going. On this basis, I created a template in Excel based on the envelope system. I will not get into details with the envelope system but if you are interested, there is a good article on the topic written by Dave Ramsey.

This monthly budget spreadsheet has two tabs. The Envelopes tab is used for budget planning. You can see a screenprint of the Envelope tab below. This tab is divided into two sections - one for recording your income and the other contains the expense distribution.


There are several things you need to update to customised this tab.

• Set the pay dates. To do this, specify the first one to your next pay date and the rest are automatically calculated.
• Specify the different envelopes you need. You can do this by reviewing your expenses from the previous months. If you are not ready, you can keep the current categories and update this as you go along.
  
• If you have some cash on hand before your first pay, I believe that this too should be allocated. You can distribute the amount in the Initial Bal column.
• At the beginning of every period, you should record your income and allocate the amount to the different categories. The whole income should be allocated. If there are still some amount unallocated, the associated amount will highlighted.
• There is an additional column that shows the average spent and available balances on completed periods. this will help you make the appropriate adjustments in the succeeding months.
  

The Expenses tab contains all of the expenditures incurred. Actual expenses are recorded as they are incurred. You will find that this part of the spreadsheet is pretty straightforward - you specify the expense details (date, amount and envelope) for each expense incurred.


The spreadsheet is available for download through this link.

For those who are interested in how this is implemented, just remove the protection from the worksheet. you can see that he spreadsheet follows some of the practical advice I have discussed in the past.

Mainframe automation for logical screens setup

Mainframe developers working in the ISPF environment need to have more than one logical screen to work effectively.

You have an edit session for the source code, another for Changeman, maybe another one for the listing (SDSF or maybe IOF). Most environments are setup to allow you to create up to eight screens (although the actual limit is 32).

Probably you have a list of preferred screens that you setup during startup. Most likely, you have a recorded macro that does this for you. This is fine but there is one issue with doing this.

There are many mainframe emulators out in the market - Hummingbird, Eicon Aviva, Extra. A recorded macro will not be portable between these tools.

To address, I recommend a another solution - creating a TSO command to do this setup.
Below is an copy what I currently have in example of

/* REXX */
/* Name : INITSCR */
/* Website : WWW.TECHNOLOGYSHIFT.COM */
ADDRESS "ISPEXEC"
"VGET (ZPANELID)"
"SELECT PGM(ISPSTRT) PARM(SCRNAME ST PERM;SDSF;ST;SWAP) "
"SELECT PGM(ISPSTRT) PARM(SCRNAME SRC PERM;3.4;SWAP) "
"SELECT PGM(ISPSTRT) PARM(SCRNAME CHK PERM;SCRNAME ON;SWAP) "
ZCMD = "SCRNAME TOOLS PERM"
"DISPLAY PANEL("ZPANELID") COMMAND(ZCMD) "
"SELECT PGM(ISRDSLST) PARM(DSL USERID.TOOLS) "
EXIT 0

Copy this code and paste in a new member in one of the concatenated SYSPROC libraries. To invoke it, you should type in the command line TSO followed by the member name. In mine, the command would be TSO INITSCR.

To customised this, you should know the following.

ISPSTRT is the program needed to create a new screen. As you can see, the PARM contains the commands you needed to execute for the screen

ISRDSLST will show you the dataset list (3.4) using the name specified in the PARM

That's it. Regardless of what mainframe emulator you use, this will work in any of them. Try it and see how it goes.

Simplifying Excel formula

Have you worked with a formula that because it is too long ends up wrapping around a cell when you tried editing it?

Later you realise you made a mistake and updating it is confusing. Worse, this formula is used in several cells so missing one update is easy.

This is a common issue that Excel users encounter. Good thing there is a simple way to address this.

Before you start, you will need to change an option within your Excel session. This is needed to ensure that the formula you are creating will be consistent even if applied in different cells.

Go to the Tools menu and choose Options.



A pop-up window will be shown. In this pop-up, click the General tab and check the R1C1 reference style setting. You should see something similar to the one below.



Now you are ready to for the solution. First, you create and test the long formula in the cell where it is needed. I have included below an actual formula I am using. One thing to note is that the same calculation is needed on the cells below it.


Once you are satisfied with the formula in the cell, you will need to copy the formula to the clipboard.

The next step will be to define a name but instead of specifying a range in the Refers to textbox, paste the formula you previously saved in the clipboard.

The new defined name is now ready for use. This is like any other function that returns a value. So on all cells that needed the formula, type equal sign and then the name you defined.

That's it. If you need to correct some errors, you only need to update it on one place (in the Define Name option) and the changes will be reflected on the cells that calls it.

You can even make this simpler by breaking your formula into segments (with each defined with a unique descriptive name), making your formula easier to manage.

Why don't you try it and see how it goes.