Keeping track of how much you're spending can be a chore but with a new year looming, now is a good time to get financial matters in order. An easy way to do this is using a spreadsheet, such as the Calc program included with the free Open Office suite.
In this workshop we'll show how to set up a monthly budget planner that will identify those areas where you regularly overspend. We'll explain how to set it up so that all your outgoings are associated with a particular budget: spend more than you've budgeted for in a given month and the overspending will be automatically highlighted.
Don't worry if it looks a bit messy and chaotic at first, as we will show you how to improve the spreadsheet's appearance towards the end of the workshop.
If you don't have Open Office installed, visit www.openoffice.org, click the 'I want to download OpenOffice.org' link and click the link below the 'Download now!' heading. If the File Download Security Warning dialogue box appears, click Save and choose a location for the downloaded file. Firefox users should select Save File to save the download to Firefox's default download folder. Now locate and double-click the downloaded file. Follow the prompts to install Open Office, launch Calc, the spreadsheet application. The first step is to flesh out the spreadsheet with cells containing the different types of income and outgoings. We've put a report section at the top, with the income and outgoings sections below, themselves split into separate subsections - we would suggest copying our example for now. V
Now type some sample figures into the various sections, as this will help when adding formulae later. Also, spend a little time making the cells more readable by expanding them. To do this, drag and drop the column boundaries at the top. Alternatively, double-click a column boundary and it will automatically resize to fit the longest entry within that column. The cells that contain monetary values need to be displayed as currency: click to highlight the appropriate cells, open the Format menu and choose Cells. Now click the Numbers tab and, in the Category section, select the Currency option. Within the Format section, choose precisely how you want the currency figures to appear. Make sure there's a tick in the 'Negative numbers red' box, as this will help identify where money is being overspent. V
Now let's set up some formulae. For the Difference columns in the Outgoings section, we want to calculate the difference between the Budget and Actual columns. To do this, highlight the first Difference cell and type =sum(. Next, click the Budget cell within that row, press the minus ('-') key, and click the Actual cell. Finish the formula off by typing a closing bracket, and press Enter. The resulting formula will look something like =sum(E10-F10). This formula can now be copied for all the Difference cells. Simply highlight the cell with the formula and, from the Edit menu, select Copy (or use the keyboard shortcut Control and C). Now highlight all other Difference cells (hold the Control key to select multiple cells) and select Paste from the Edit menu (or use the Control and V keyboard shortcut). V
Appropriate formulae for the total cells within each section need to be created. Click the Total cell for the Budget column within the Bills section and start the formula by typing =sum(. Next, left-click the first entry in the Budget column and, keeping the button held down, drag the mouse over the rest of the cells in the section; a red box will be drawn around the cells as you do this. Finish the formula with a closing bracket and press Enter. The resulting formula will look something like =SUM(E10:E14). Copy and paste this formula into the appropriate cells, as before. Repeat this process for the other sections in the spreadsheet. V
For the Monthly Report section at the top, we want to tally the Total cells for all the sections under Income and Outgoings. For the Total Income formula, click to highlight the cell and type =sum(. Now hold down the Control key and click on the Total cell within the Actual column for each section. When the last one is selected, finish the formula by typing a closing bracket and press Enter. Now do the same for the Total Outgoings cell. Finally, the Difference cell in the Monthly Report section needs a formula. Click to highlight it, type =sum( and click on the Total Income cell. Now press the minus ('-') key and click the Total Outgoings cell. Finish it off with a closing bracket, and press Enter. V
All that remains is to spruce up the spreadsheet's appearance. We've added some borders to the different sections: to do the same, highlight the relevant cells, choose Cells from the Format menu and select the Borders tabs. The colour of cells can also be changed from here by clicking the Background tab. In this example, we've applied a bold effect to some of the section headings - just highlight the relevant cells and click the 'B' icon on the toolbar at the top. Finally, we've resized some of the rows and columns for neatness. *
UGT
Source Citation
"Fix your finances andmaintain a budget." Computer Act!ve 22 Dec. 2011. Computer Database. Web. 15 Apr. 2012.
Document URL
http://go.galegroup.com/ps/i.do?id=GALE%7CA275530213&v=2.1&u=22054_acld&it=r&p=CDB&sw=w
Gale Document Number: GALE|A275530213
No comments:
Post a Comment