| Step | Instructions for Accounting Services file | Points Possible | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1 | On the Budget worksheet: Use formulas to total the revenues by quarter, by revenue item, and then overall total. Use formulas to total the expenses by quarter, by expense item, and then overall total. Enter a formula to calculate the Net Income by quarter and then overall. | 2 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 | Format the Budget worksheet to look business professional. This may include cell formatting used for total cells, color changes if needed, formatting of numbers, widening of columns, making the title of the sheet standout and centering it across the columns of data, using spell check, etc.
(Remember with ALL the formatting you do in this file that you may like the color pink but it is not very professional, also too much of a dark color or neon colors is not professional.) |
2 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | In column G insert sparklines for each revenue and expense line item (including the totals and Net Income lines) to show the trend over the quarters. This is a great visual way to see the fluctuation of your revenues and expense items over the year. Format the sparklines to blend well with the rest of the document. Again business professional. | 1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | Create Pie chart to show what revenue categories the business’ Total Revenue is coming from. This is a great tool to show a picture of the division of the whole. Put the pie chart on a new sheet named ‘Revenue Chart’ and format it attractively using the knowledge you gained in this class. | 1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | With the Budgeted Net Income for the year being so low you decide you’d like to see what percentage of your total expenses each expense item is. Create a new sheet that will show just your expenses with the total budgeted for the year, use a link back to the total on the budget sheet. Name the sheet Expense Analysis. Add a column to calculate what percentage of the total Expense each item is, no need to calculate the percentage for the Total Expense line item since that should be 100%. Put a Title on this sheet so anyone looking at it can tell what this data is for. Format the sheet to look business professional like the budget sheet. Hint: Work smarter not harder, don’t create this sheet from scratch. | 3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 6 | Now the first quarter financial numbers are in and you want to prepare the Income Statement for the 1st Quarter. Your Income Statement should compare the actuals to the budget amounts for the first quarter. Go to the 1st Quarter worksheet and see the data already entered into the worksheet for you. You need to enter links in the cells so that the 1st Quarter budget numbers appear on this sheet, by doing links you will be able to use this file as a starting point for next year, enter in your new budget on the budget sheet and having the Quarter sheet automatically update. Finish up the sheet so that it will show how much over or under budget each item is, including total lines. Hint: Make sure you have the proper sign showing. If you are over budget on a revenue item is should appear positive, if you are over budget on an expense it should appear as a negative. Add conditional formatting (bold, italic, and a font color that goes with your theme) to your variance column to make the items that are negative stand out just a little. Make sure your 1st Quarter sheet has a professional appearance. | 3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | Make Copies of your 1st Quarter Sheet so that you have one for 2nd Quarter, 3rd Quarter, 4th Quarter, and Year End (use these names for the sheet names). Adjust each of these sheets to have the proper titles, links to the appropriate budget data. When updating consider using find and replace to save yourself time. Remove the Actual data since it is for 1st Quarter. Have the sheets completely ready so all you need to do at the end of each quarter is to enter in the reported actual data for that time frame. Set the Year End sheet to be adding the actuals from the 1st Quarter, 2nd Quarter, 3rd Quarter, and 4th Quarter sheets so it will be ready once your 4th Quarter sheet is done. Now enter the actuals for the remaining quarters.
(See next page)
Make sure your formulas for all the sheets are working properly. |
3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 8 | Set up the printing for all the sheets in the Workbook. Each should print centered on 1 page and have a footer showing the filename and then comma and a space then the sheet name, don’t just type this in. If the file name is changed or the sheet name is changed you want this footer to automatically change as well. You choose the orientation for each sheet based off of what will look the best. | 2 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 | Ensure that the worksheets are correctly named and placed in the following order: Budget, Revenue Chart, Expense Analysis, 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter, Year End. Save the workbook. | 0 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | Now since you are just so happy with this file and you know you will use it year after year you decide to save yourself some time in future years and create a macro that will remove all the data entry cells so that the file is ready for a new year with just the click of a button. You need the macro to delete the data entry cells on the budget sheet and the actual data on the Quarter sheets. Make sure your macro clears all the appropriate cells, leaving those with formulas in them. The macro should bring the active cell in each sheet back to A1 and end on the budget sheet so that it brings the user back to the starting point. After you have your macro recorded you can create a button off to the right on the budget worksheet that is linked to the macro and has text on it that states ‘Click here to prepare workbook for a new year’. This macro button should NOT print when you print this sheet so go in and make sure the print area is not including the cells the button is in. Save your File as ‘Your last name_Accounting Services New Year.xlsm | 3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Total Points | 20 |
7. Submit all 3 of your files using the assignment link in the Final Exam –Part 2 folder in the Course Content section within BlackBoard.