Personal Budget Assignment
The assignment is to create a personal budget using an Excel spreadsheet. I will only accept files in Excel format. Many other spreadsheet programs provide a mechanism for saving a file in Excel format. The spreadsheet that is included in the free productivity suite OpenOffice does. The link is to a site for downloading it. Excel is also available on all computers in UMUC labs.
Before beginning the project, please read Units G-I of Hunt and Waxer and pages 216-219 of Morley and Parker.
You will find a sample of the completed project attached as "Sample.jpg" above. Please follow directions exactly.
One of the purposes of this exercise is for you to learn how to use formulas. The file "ScrShot1.doc" attached above shows the effect of changing the number in D11 from 200 to 100. The numbers in D15, F5, and F6 change automatically. The file "ScrShot2.doc" Shows the effect of replacing the number in C8 with 500. The numbers in D8, D15, F5, and F6 all change. Finally, the file "ScrShot3.doc" shows the effect of changing the number in A5 to 1.30. The numbers in D8, D13, D14, D15, F5, and F6 all change.
Unless otherwise directed, use the default font.
1. In row 1, merge the cells in columns D-F, enter your actual name in bold, and align it to the right.
2. In row 2, use merge and center to merge the cells in columns A-F. Enter the label shown in the sample in bold, 14-pt font. Please notice that the label will always be centered in columns A-D regardless of the width of any of those columns.
3. Enter the label shown in A4 in bold, and expand column A to fit it. Leave it aligned to the left.
4. Set the format of A5 to Accounting Number ($) to two decimal places, and enter the current exchange rate in the cell. Leave it aligned to the right.
5. Merge the cells in columns A and B of rows 7-15. You will have to do this one row at a time.
6. Enter the labels shown in columns 7-8 of the now merged cells. Leave them as aligned to the left. Put the one in row 7 and the one in row 15 in bold.
7. Enter the labels in C7 and D7 in bold. Leave them aligned to the left.
8. Set the format of the cells in rows 8-14 of column C to Euros to two decimal places. Leave them aligned to the right.
9. Set the format of the cells in rows 8-15 of column D to Accounting Number ($) to two decimal places. Leave them aligned to the right.
10. Enter a formula in D8 to convert whatever is in C8 from Euros to dollars. Since you will be filling down the column, use a strictly relative address for C8, but use an absolute address or at least an absolute row for the conversion rate in A5.
11. Fill the formula in D8 down to D14.
12. Enter a formula using the SUM function in D15 to make it hold the sum of the numbers in columns 8-14 of column D.
13. Enter some numbers into rows 8-14 of columns C and D. Notice that anything entered in column C will appear in the same row in column D. Notice that anything directly entered in column D will replace the formula. Leave some numbers in column C to let me see the formula in column D.
14. Enter the labels shown in rows 4-6 of column E in bold. Leave them aligned to the left.
15. Set the format of the cells in rows 4-6 of column F to Accounting Number ($) to two decimal places. Leave them aligned to the right.
16. Enter a number in F4.
17. Enter a formula in F5 to put whatever is in D15 into it as well.
18. Enter a formula in F6 to calculate the difference between what is in F4 and F5.
19. Change some numbers to see what happens.
Save your file, and name it with your last name.
NOTE: Please change your name in Excel and save the file as your last name.xls
The assignment is to create a personal budget using an Excel spreadsheet. I will only accept files in Excel format. Many other spreadsheet programs provide a mechanism for saving a file in Excel format. The spreadsheet that is included in the free productivity suite OpenOffice does. The link is to a site for downloading it. Excel is also available on all computers in UMUC labs.
Before beginning the project, please read Units G-I of Hunt and Waxer and pages 216-219 of Morley and Parker.
You will find a sample of the completed project attached as "Sample.jpg" above. Please follow directions exactly.
One of the purposes of this exercise is for you to learn how to use formulas. The file "ScrShot1.doc" attached above shows the effect of changing the number in D11 from 200 to 100. The numbers in D15, F5, and F6 change automatically. The file "ScrShot2.doc" Shows the effect of replacing the number in C8 with 500. The numbers in D8, D15, F5, and F6 all change. Finally, the file "ScrShot3.doc" shows the effect of changing the number in A5 to 1.30. The numbers in D8, D13, D14, D15, F5, and F6 all change.
Unless otherwise directed, use the default font.
1. In row 1, merge the cells in columns D-F, enter your actual name in bold, and align it to the right.
2. In row 2, use merge and center to merge the cells in columns A-F. Enter the label shown in the sample in bold, 14-pt font. Please notice that the label will always be centered in columns A-D regardless of the width of any of those columns.
3. Enter the label shown in A4 in bold, and expand column A to fit it. Leave it aligned to the left.
4. Set the format of A5 to Accounting Number ($) to two decimal places, and enter the current exchange rate in the cell. Leave it aligned to the right.
5. Merge the cells in columns A and B of rows 7-15. You will have to do this one row at a time.
6. Enter the labels shown in columns 7-8 of the now merged cells. Leave them as aligned to the left. Put the one in row 7 and the one in row 15 in bold.
7. Enter the labels in C7 and D7 in bold. Leave them aligned to the left.
8. Set the format of the cells in rows 8-14 of column C to Euros to two decimal places. Leave them aligned to the right.
9. Set the format of the cells in rows 8-15 of column D to Accounting Number ($) to two decimal places. Leave them aligned to the right.
10. Enter a formula in D8 to convert whatever is in C8 from Euros to dollars. Since you will be filling down the column, use a strictly relative address for C8, but use an absolute address or at least an absolute row for the conversion rate in A5.
11. Fill the formula in D8 down to D14.
12. Enter a formula using the SUM function in D15 to make it hold the sum of the numbers in columns 8-14 of column D.
13. Enter some numbers into rows 8-14 of columns C and D. Notice that anything entered in column C will appear in the same row in column D. Notice that anything directly entered in column D will replace the formula. Leave some numbers in column C to let me see the formula in column D.
14. Enter the labels shown in rows 4-6 of column E in bold. Leave them aligned to the left.
15. Set the format of the cells in rows 4-6 of column F to Accounting Number ($) to two decimal places. Leave them aligned to the right.
16. Enter a number in F4.
17. Enter a formula in F5 to put whatever is in D15 into it as well.
18. Enter a formula in F6 to calculate the difference between what is in F4 and F5.
19. Change some numbers to see what happens.
Save your file, and name it with your last name.
NOTE: Please change your name in Excel and save the file as your last name.xls
