While creating the spreadsheet that is provided in Part 2, a number of tricks and guidelines were used that may be of interest to others. Specifically, obtaining the screenshots and making them download as fast as possible, creating line charts that stopped at the current month, deciding what colors to use to ensure there is no confusion when viewing and printing the charts, and the tradeoff between using a single sheet rather than multiple sheets to ensure that formulas are not modified.
This is Part 4 of a four-part post.
- Part 1: Why – The reasons for managing your expenses and why it is difficult to do so are given.
- Part 2: Spreadsheet – An explanation of how to use the Cash Flow spreadsheet is provided. You may download it by clicking on Download Spreadsheet.
- Part 3: FinanceBase – Only so much can be done with a spreadsheet. A more robust and full-featured application as an alternative to the spreadsheet is discussed.
- Part 4: Design – A summary of the design of some of the features of the spreadsheet and the posts is presented.
- – Fixing charts so they will print
- – Setting a chart’s horizontal axis at the bottom
- – Getting cash flow chart to stop at an empty month
- – Deciding on chart colors
- – Preserving formulas by using 1 sheet
Charts too close to the right edge of a page will not fully print on the page. Not sure why, but Excel continually seems to shift charts to the right. Therefore, make sure that the chart has some space to the right and always preview before publishing. Back to Contents
For charts that have a negative value, the default for the horizontal (x) axis is at the 0 value of the vertical (y) axis. This position puts the month text amongst the lines or columns and make is hard to read. To have the horizontal axis at the bottom of the chart, double-click on the x-axis to open the Format Axis dialog box. Then select Ticks along the left side. In the Axis labels area select the Low radio button. Back to Contents
Nornally, to obtain a cumulative cash flow by month from a series of amounts for each month, the formula used is
However, using this will result in the line continuing with the last amount, even if there is a zero amount for the month. This looks odd when charted as the line continues horizontally to the end of the year.
To make a cumulative cash flow line chart stop at an empty month, rather than continue with the last cumulative amount, make sure that the value is #N/A when the month has a zero value. Excel will not chart #N/A. This is done by using NA() to fill the cell with #N/A. Thus the equation would be
However, this is still a problem when some month’s values are zero, such as when the cash flow starts in the middle of a year or when a month is missing. In these cases, the Cum amount stops at the last value that is not #N/A. To overcome this, use a sum from the first month instead of cumulating the amounts. The equation now is
The Sum ignores any #N/A and creates a value for this month.
The last equation, when used as a line on a line chart, will properly create a line that stops when there is nothing to chart after the last real value.
However, there is still one last thing that has to be done. As mentioned above, #N/A will be shown in the cells after the last real value. To make this go away there is no number or custom format that will do it. Instead, you must use conditional formatting. It is a little complicated, so here are the steps that you must follow:
- – Click on the Home tab.
- – Click on the Conditional Formatting icon which is under the Format section and select “New Rule”.
- – In the New Formatting Rule window, from the Style drop-down menu, select “Classic”.
- – From the second drop-menu, select “Format only cells that contain”.
- – From the 3rd row of drop-down menus, from the 1st one on the left, select “Errors”.
- – From the last row, from the “Format with:” drop-down menu, select “custom format…”.
- – In the Format Cells window that opens, click on the Font tab and select from the Color menu the white theme color.
- – The click on the Fill tab and also select a white color for the Background.
- – Then click on the OK buttons. Back to Contents
Deciding on the colors for the charts was difficult as it had to satisfy the following criteria:
– Plan and Actual must have different colors
– When printed on a black and white printer, there is to be no more than 1 solid color per chart. The rest must have a pattern.
– There must be a Marker for line charts so that when only 1 month has been entered it shows. It must not be so large that it overwhelms the line.
– When there are 2 lines on a chart, the Marker sizes must be set so the top line fits inside the bottom line so that when the lines overlap, both are visible.
– When there are 2 or more lines on a chart, only one can be solid and the others must have a pattern so that when printed in black and white they are different.
To satisfy these criteria, the following are done for each of the types of charts.
Plan Income and Expenses – a Column Chart. A solid Blue is used for Income and a solid Magenta is used for Detail Expenses. This violates on of the criteria given above, but the solid colors are needed for other charts. Hopefully, it will not be too hard to differentiate in black and white.
Income By Month – Column Chart. Blue is used for Income with a solid blue for the Plan and a blue pattern (bottom to top) for the Actual with a black border.
Detail Expenses By Month – Column Chart. Magenta is used for Detail Expenses with a solid magenta for the Plan and a magenta pattern (top to bottom) for the Actual with a black border.
Group Expenses for Year – Column Charts. Orange is used for Group Expenses with a solid orange for the Plan and an orange pattern (top to bottom) for the Actual with a black border.
Detail Expenses For a Month and Totals – Bar Charts. Same colors as for the Detail Expenses by Month. That is, a solid magenta for the Plan and a magenta pattern (left to right) for the Actual with a black border.
Cumulative Detail Expenses by Month – Line Charts. Solid magenta line for the Plan and a black dashed line for the Actual. The Plan line is 2.25 pt, marker is a square of size 8, with 1.5 pt line and no fill. The Actual line is 1.5 pt, marker is a circle of size 5, .75 pt line and no fill. With these sizes, if they overlap, the actual fits inside of the plan and the plan shows through the actual. A magenta for the actual did not look good and the plan was hard to see if they overlap.
Actual Income and Detail Expenses by Month – Column Chart. Uses the colors set above: a blue pattern (bottom to top) for the Actual with a black border and a magenta pattern (top to bottom) for the Actual with a black border.
Cumulative Cash Flow by Month – Line Chart. This uses a different color scheme to make it stand out from all of the other charts. The Plan is a solid 3.75 pt green line with a square 8 pt marker with no fill and a green 1.5 pt line. The Actual is a dashed 3.75 pt dark red line with a square 8 pt marker with no fill and a green .75 pt line.
Available Cash Account Amount for Next Month – Column Chart. This has 4 columns that show and 1 invisible column to provide separation between the first 3 and the last one. The Cash Assets is a torquoise with horizontal bars, the To Pay Charge Account Liabilities is red while the Minumum Balance is green with a slated pattern (bottom to top). The Available for Next Month is separated from the other 3 and is black.
Expected Cash Account Amount at End of Month – Column Chart. All but one of the visible columns have full colors but since they is some spacing and overlap, they should be recognizable when black and white printing is used. The Available at Start of Month is the first one and it is black because it is the same as the Available for Next Month in the chart above it. There is an invisible column to provide spacing between the next 2 which are the blue column for Plan Income and the magenta column for Plan Detail Expenses used on other charts. There is a another invisible column to provide separation for the last column which is the Expected at End of Month which is lime-green with a pattern (bottom to top) and a black outline.
All Assets and Liabilities by Month – Column and Line Chart. The columns are All Assets which is green with horizontal bars and the All Liabilities is Red with vertical strips. The Worth = All Asset-All Liabilities is a 4.5 pt green line with an 8 pt diamond marker and 1.5 pt marker line with no fill. The green line stands out very well against the green asset column. Back to Contents
The Excel spreadsheet provided in Part 2 has a lot of formulas that must be changed when a user enters new Income and Expense Categories. There are a number of ways to preserving formulas and some are listed below, with comments on if each way is acceptable.
a) The spreadsheet can be locked to keep the formulas from being changed, but then the user could not delete or insert rows, so this approach was rejected.
b) There are a number of different areas to the spreadsheet that require the categories to be shown in the first few columns. These can be duplicated (by using a link) on separate sheets, which would appear at the bottom of the page. The user can then select what area is desired to view the details and enter data. However, to insert or delete rows, the user would have to Group the sheets by selecting all of the ones that have the categories before doing the inset or delete. This would be confusing an not intuitive and there are other sheets that do not have the categories. Therefore, this approach was also rejected.
c) All of the areas that require the categories can be on 1 sheet and the user instructed to scroll to the area. However, then the categories disappear. To solve this, the screen is split vertically to keep the categories fixed to the left. It is also split vertically so that some charts always appear at the top. The panes are then frozen to remove the wide bars. The user can then scroll vertically and horizontally and the top-left corner alway show the Cash Flow chart and the Categories always stay in the first few columns. This approach is used.
Because approach (c) is used and the spreadsheet is not locked, the user is warned about not changing the formulas and given specific instruction on how to delete, move and insert rows that will preserve the formulas. Back to Contents
Media files such as screenshots and pictures should be uploaded as png files rather than gif and jpeg as they are smaller and will download faster.
I use a Mac, so getting the screenshots to be included in the post has been reasonably easy because a screenshot can be made by holding down the Command-Shift-4 keys. When the cross-hair appears, select the area to be shot. After releasing the mouse, a file will appear on the desktop. Rename it to whatever is desired.
The default file type is jpeg, but it should be changed to png to make it easier to compact. To change it to png, use the following Terminal command:
If another file format is desired, replace the png with, for example, jpg, gif, pdf, tiff.
Once the png has been created and renamed, it should still be compacted as the Mac creates a loss-less file. If you do not compact your picture files, you will receive a negative rating when you check your page with PageSpeed Insights.
The best one I have found is the online TinyPNG at https://tinypng.com which can batch process files. Or you can download the ImageAlpha application for single files on your Mac which can be found at http://pngmini.com. They use pngquant that is found at http://pngquant.org which lists other programs that use it for other platforms. Compression savings of from 65% to 80% are possible.
According to the website, TinyPNG provides “Advanced lossy compression for PNG images that preserves full alpha transparency. TinyPNG uses smart lossy compression techniques to reduce the file size of your PNG files. By selectively decreasing the number of colors in the image, fewer bytes are required to store the data. The effect is nearly invisible but it makes a very large difference in file size!”
These screenshots are then uploaded into the WordPress Media Library and then inserted into the posts. Back to Contents
There is a lot of examples on the internet for making a scrollable box that can show code without the line wrapping. The code boxes used above (the yellowish boxes) have been created by using a HTML <div> and not <code> or <pre> around the text to be shown with a CSS class that sets a number of parameters. Here is an example of the HTML:
There is one trick that is required to make this work: any text between the <div></div> that has a <, must be replaced by the equivalent code which is “<”.
The CSS code is as follows:
padding: 0px 0px 0px 5px;
border: 1px solid #000;
Some of the other parts of this post have links that are within the same post. One example is links to references that are kept at the end of the post rather than within the body of the post. Each is given a number and only this number is used in the body. Clicking on the number will take take the reader to the reference. To get back to the location in the post, a return link is used at the end of the reference. Here is what is done to make this happen.
a) In the body of the text, you use an anchor with href=”” as normal, but instead of the address of a website (i.e. http://…) you use # plus a unique name. For example, here is how the link and return to reference (1) is shown where the unique name is (1). The return is also an anchor but instead of href, the keyword id is used followed by a unique name which is “return(1)”. (As an indicator that there is an id, when in the edit mode, WordPress shows a grey down arrow. This is not shown when viewed normally on the website.)
b) Just before the (1) of the reference, place the following code that is an anchor with an id equal to the unique name of (1) set above. For example, If you click on the link shown as (1) above, the page will scroll to here, (1) Part 1: Why return
c) To allow the user to return to the location of the link to reference (1), place this code which is an anchor with an href to an internal address with the unique name of return(1) at the end of the reference:
It may seem odd to put the reference number in brackets within the href and id, rather than just the number, but there is a reason for it. When I am creating a post, I assign a letter to each reference and put it inside a bracket, such as (a). Later, I order the references by when they appear in the post. Then all that is required is to replace the letter with the correct number in the Text (not the Visual) editor of WordPress. However, neither WordPress nor my Safari browser provide a Find and Replace command so it is necessary to do it another way. The process is to use the Find for (a), click on the text area, do a Find Next to highlight the (a) and then type (1). Continue this for all instances that need changing. To reduce the typing, go back to the first (1) and copy it. Then with each Find Next, just paste to replace it with the (1). Do this for each lettered reference. Back to Contents
Many of the screenshots on the posts will be too small to view on non-desktop platforms such as smart phones and some tablets. Because the screenshots are vectors, they will resize and become some-what readable, but the font size will be very small on small screens. There are ways to show the screenshots in larger size with overlays and perhaps double-clicking. However, if the screen is small, the overlay will also be small and the problem will still remain.
The solution decided upon is to use the method normally provided by most browsers. This is, instruct the user to use the “contextual menu” to open a new Window with the screenshot. This is done by hovering over the screenshot and right-clicking for Windows or control-clicking if a Mac. If the screenshot cannot open in a window big enough for it, the browser will show a magnifying cursor and there will be scroll bars. Back to Contents