Question

In-Class Spreadsheet Activity (Version A)

Your income and expenses:

JANUARY:

Income $1900; Rent $600; Car payment $199; Groceries $125; Utilities $89.50; School Expenses $125.75; Movie Tickets $61

FEBRUARY:

Income $1950; Rent $600; School books $278.97; Groceries $79.16; Utilities $76.57; Car Payment $199.61

MARCH:

Income $1800; Rent $600; Car payment $199; Groceries $73.93; School Expenses $55.99; Medical Expense $100; Car Insurance $350; Utilities $69.51

APRIL:

Income $2100; Rent $625; IRS expenses $279.54; Car payment $199; Utilities $58.88; Groceries $88.85; Misc Expenses $15.01

Using the information above, create a budget on spreadsheet to keep track of your money. Make sure you do the following:

1) Format all numbers with currency to 2 decimal places.

2) Adjust column width appropriately.

3) Bold and center align the column headings and “merge and center” the title

4) Calculate totals for each type of expense and for each month.

5) Include a grand total for all 4 months of expenses.

6) Add another column to calculate the percentage of total expenses for each category by dividing each category total by the grand total.

7) Add another row to calculate the percentage of total expenses for each month by dividing each monthly total by the grand total.

8) Show how much is going into savings. (Total income minus total expenses)

9) Use additional tools to make the report attractive. (Possibilities include: fill colors, text colors, font and size changes, word art, etc.)

10) Make sure it all fits on one page and name your spreadsheet "BUDGET".

*Hint: Since you have fewer months than expenses you might be better off putting expenses as rows and months as columns so it’ll fit on one page.

10 points for each question. Total = 100 points.

The weight of this assignment is 5% of the course grade.

Answer 1

Following is the screenshot of the excel sheet to be created:

As we cant upload the excel this is the best possible way to explain the same.

• In column F we have take the total of income and each kind of expense.
• The grand total expense is 4549.30
• We have changed the color of expenses to red to demarcate them from income
• Monthly expense totals are given in row 14, calculated by using the sum formula for the expenses in each of the 4 columns for 4 months
• Column chart presents the same information graphically and shows that the rent is highest expense.