Accounting Spreadsheet Fundamentals
A05 Accounting Spreadsheet Fundamentals
Directions: Be sure to save an electronic copy of your answer before submitting it to Ashworth College for grading. Unless otherwise stated, answer in complete sentences, and be sure to use correct English, spelling and grammar. Show all work.
SERVICE COMPANY WORKSHEET (F1WORK)
Note: For a problem using the sole proprietorship form of business organization, download the P1WORK file that is attached to this assignment.
PROBLEM DATA
The trial balance of Wikki Cleaners at December 31, 2012, the end of the current fiscal year, is as follows:
Information for the adjusting entries is as follows:
- Cleaning supplies on hand on December 31, 2012, $18,750.
- Insurance premiums expired during the year, $1,800.
- Depreciation on equipment during the year, $21,600.
- Wages accrued but not paid at December 31, 2012, $1,830.
REQUIREMENT
- As the accountant for Wikki Cleaners, you have been asked to prepare financial statements for the year. A file called F1WORK has been provided to assist you in this assignment. As you review this file, it should be noted that columns H and I will automatically change when you enter values in columns E or G.
- Open the file F1WORK file that is attached to this assignment. Enter the formulas in the appropriate cells on the worksheet. Then enter the adjusting amounts in columns E and G. Also, in column D or F, insert the letter corresponding to the adjusting entry (a–d). Column A is “frozen” on the screen to assist you in completing requirement 3.
- Complete the income statement and balance sheet columns by entering formulas in columns J, K, L, and M that reference the appropriate cells in columns H or I. Net income will be automatically calculated for the income statement and balance sheet. Check to be sure that these numbers are the same. Enter your name in cell A1. Save the completed file as F1WORK3. Submit the worksheet. Also submit your formulas using landscape orientation and fit-to-1 page scaling. Check figure: Net income (cell J30), $71,320.
WHAT-IF ANALYSIS
- Suppose you discover that an assistant in your department had misunderstood your instructions and had provided you with the wrong information on two of the adjusting entries. Cleaning supplies consumed during the year should have been $18,750, and insurance premiums unexpired at year-end were $1,800. Make the corrections on your worksheet and save the corrected file as F1WORK4. Submit the worksheet.
- Prepare an income statement, a statement of retained earnings, and a balance sheet, using the corrected worksheet (F1WORK4).
CHART ANALYSIS
- With the F1WORK4 file open, click the Chart sheet tab. On the screen, a pie chart shows the percentage composition of the total expenses of Wikki Cleaners. Enter the percentages in the space provided. Compare the percentages of Wikki Cleaners with the national statistics provided.
Comment on the differences noted. Why might depreciation, utilities, and rent be so far off from the national percentages?
CHART TICKLER
Using the F1WORK4 file, create a 3-D column chart showing the dollar total of each of the expenses of Wikki Cleaners. Use the Chart Data Table as a basis for preparing the chart. Use appropriate titles, legends, and formats. Enter your name somewhere on the chart. Save the file again as F1WORK4
