This problem involves creating a monthly budget. The column headings for your expenses are given as well as a column for any deposits and a column for the balance in your checking account. The spreadsheet gives you several rows with values inserted. a.) In cell L4, enter 2,387.12 and format as accounting. b.) In cell L6, enter a formula which takes the starting balance in cell L4 and subtracts any expenses for this row (Hint: you may subtract the SUM of the expenses) and then adds in the deposit for that day. Remember that in a template like this our formulas should include blank cells. Next month we may have a deposit or a miscellaneous expense on this first day and our formula should be ready for this. c.) We cannot fill the formula in cell L6 down to L7 because the cell reference in L4 will change to L5, but there is not a number in L5, only the word "Balance". In cell L7, enter a formula which takes the previous balance in cell L6 and subtracts any expenses for that row (Hint: you may subtract the SUM of the expenses) and then adds in the deposit for that day. d.) Now fill the formula in cell L7 down to L24. In row 24 for January 19, enter 22.37 for Gas, 128.99 for Phone, and 75.43 for Electric. Format all as accounting. e.) In A25:A36, enter the dates Jan 20 to Jan 31. Format as in the cells above. In cells L25:L36 fill down the formula from L24. f.) In cell B38, SUM the monthly values for this column and fill the formula across to cell L38. In cell B39, AVERAGE the monthly values for this column and fill the formula across to cell L39. In cell B40, COUNT the monthly values for this column and fill the formula across to cell L40. In cell B41, calculate the MAX of the monthly values for this column and fill the formula across to cell L41. In cell B42, calculate the MIN of the monthly values for this column and fill the formula across to cell L42. g.) Type in the cell reference (answers should be of the form: A21, D13, etc.) for the cell which gives each of the following: i. Average amount spent on gas so far this month ii. Minimum spent on groceries this month iii. Total spent on dining out this month iv. Number of deposits made this month v. Maximum balance this month h.) One of the outputs in cells L38:L42 does not make any sense, and you would not compute this value. Type in the cell reference for the one which does not make sense (answer should be of the form: A21, D13, etc.). Budget Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, unless Insurance Cable Rent $113.72 $145.63 $750.00 Beginning Balance: $2,387.12 Electric Miscellaneous Deposits Balance $1,051.85 $ 1,335.27 $ 1,335.27 $ 1,335.27 $4.30 $100.00 $ 1,235.27 $ 1,235.27 Monthly Budget Date Gas Groceries Dining Out Phone 1-Jan $36.75 $42.50 2-Jan 3-Jan 4-Jan $102.35 5-Jan 6-Jan $43.78 7-Jan 8-Jan 9-Jan 10-Jan 11-Jan 12-Jan 13-Jan 14-Jan 15-Jan 16-Jan $51.33 17-Jan $136.44 18-Jan 19-Jan $22.37 $128.99 20-Jan 21-Jan 22-Jan $2,135.69 $17.98 $42.31 $75.43 23-Jan 24-Jan 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan 31-Jan SUM AVERAGE COUNT MAX MIN Part g: Part h
