Accounting Homework
Accounting Homework
ORDER A PLAGIARISM FREE PAPER NOW
Don't use plagiarized sources. Get Your Custom Essay on
Accounting Homework
Just from $15/Page
Procedures for Final Exam – Part 2, Actg. Spreadsheet Applications
1. Download the Personal Finances.xlsx file, being sure to save it as Your_Last name_Personal Finances.xlsx.
2. Open the Your_Last name_Personal Finances.xlsx file.
3. Take a look at the file and complete the steps listed below in the grading rubric.
| Step | Instructions for Personal Finances file | Points Possible |
| 1 | On the JuneTotals worksheet, sort the data in the range A3:E16 in ascending order by Category. At each change in Category, use the Sum function to add subtotals to the data. Accept all other defaults. Collapse the outline to show the grand total and Category subtotals only. | 2 |
| 2 | Create a PivotTable in cell F1 on the AnnualExp worksheet using the data in the range A1:D17. Add the Expense field to the PivotTable as the row label; add the Amount field as the value; and then add the Year field as the column label. Change the format of the values in the PivotTable to accounting with no decimal places. | 3 |
| 3 | Add the Category field to the Report Filter area of the PivotTable. Filter the data so that only expenses in the Variable category are displayed. Display the values as percentages of the grand total. | 1 |
| 4 | Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from 2011 is displayed. Change the height of the slicer to 2” and then reposition it so that the top left corner aligns with the top left corner of cell I2. | 2 |
| 5 | Create a PivotChart based on the data in the PivotTable using the pie chart type. Change the chart title text to Variable Expenses, and then remove the legend. Add data labels to the Outside End position displaying only the category names and leader lines. Reposition the chart so that the top left corner aligns with the top left corner of cell F13. | 2 |
| 6 | On the HomeLoan worksheet, in cell A10, enter a reference to the monthly payment from column B. Create a one-variable data table for the range A9:H10 using the interest rate from column B as the Row input cell. | 2 |
| 7 | On the HomeLoan worksheet, in cell A12, enter a reference to the monthly payment from column B. Create a two-variable data table in the range A12:H16, using the interest rate from column B as the Row input cell and the term in months from column B as the Column input cell. | 2 |
| 8 | On the HomeLoan worksheet, perform a goal seek analysis to determine what the down payment in column B needs to be if you want the monthly payment in column B to be $2,000. Accept the solution. | 1 |
| 9 | On the HomeLoan worksheet, create a scenario named Maximum using cells B2, B3, B5, and B6 as the changing cells. Enter these values for the scenario: 280000, 24000, .075, and 360, respectively. Show the results, and then close the Scenario Manager. Undo the last change. | 2 |
| 10 | On the June2012 worksheet, in cell I7, sum the values in E7:E24 if the purchase in column C is groceries; in cell I8, average the values in E7:E24 if the purchase in column C is groceries; and in cell I9, calculate the number of times groceries were purchased during the month. | 2 |
| 11 | On the June2012 worksheet, in cell I11, calculate the total amount spent on groceries using a credit card; in cell I12, calculate the average spent on groceries using a credit card; and in cell I13, calculate the number of times groceries were purchased using a credit card during the month. | 2 |
| 12 | On the June2012 worksheet, in cell F7, nest an AND function within an IF function to determine if the transaction was paid using a credit card and the amount of the transaction is less than -100. If both conditions are met in the AND function, the function should return the text Flag. For all others, the function should return the text OK. Copy the function down through cell F24. | 2 |
| 13 | Group the June2012 and JuneTotals worksheets together. Fill the contents and formatting from the range A1:F1 on the JuneTotals worksheet across the grouped worksheets. (Hint: You will use the fill drop-down in the Editing section on the Home ribbon.) Ungroup the worksheets. In cell I19 on the June2012 worksheet, insert a reference to cell E26 on the JuneTotals worksheet. | 1 |
| 14 | On the June2012 worksheet, create a validation rule for the range D7:D24 to only allow values in the list from the range I21:I24. Create an error alert for the selected range that will display after invalid data is entered. Using the Stop style, enter Invalid Entry as the title, and then type Please select a valid method. (include the period) as the error message. | 2 |
| 15 | Apply the Civic theme to the workbook. (Hint: this means all the sheets in the workbook) Apply the cell style Accent1 to cells A3 and D3 on the CarLoan worksheet. | 1 |
| 16 | Set the Author property of the workbook to Exploring Excel Student, and then set the Title property to Personal Finances. | 1 |
| 17 | Change your user name to Exploring Excel Student. On the CarLoan worksheet, insert the comment Updated on 7/17/2012 in cell A1. Mark the workbook as final. | 2 |
| 18 | Ensure that the worksheets are correctly named and placed in the following order in the workbook: JuneTotals; June2012; AnnualExp; HomeLoan; CarLoan. Save the workbook. Close the workbook and then exit Excel. | 0 |
| Total Points | 30 |
4. Download the Accounting Services.xlsx file, being sure to save it as Your_Last name_Accounting Services.xlsx.
5. Open the Your_Last name_Accounting Services.xlsx file.
6. Take a look at the file and complete the steps listed below in the grading rubric.
| 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.
Page 1
