How to write the case report? fill in the excel spreadsheet

Case 1 A Practice Problem in Accounting Sparky Fitness Corporation In Chapters 3-5, we discussed the Accounting Information System and the formal financial statements which result. The accounting cycle represents the steps a company would use to capture all of the transactions and events over a period of time that are necessary to eventually prepare the formal financial statements. The purpose of this case is to have you build an integrated Excel spreadsheet that simulates Sparky Fitness Corporation's accounting information system. You will be required to record transactions in the General Journal, post to the t-accounts in the Ledger, record Adjusting Journal Entries, create a Trial Balance Before and After Adjustments using a Worksheet, record Closing entries and prepare a formal set of financial statements in good form. To build an integrated spreadsheet, you will be required to use many of the tools of Excel by writing formulas & using cell referencing to link each of the steps in the accounting cycle together. Instructions: Use the accompanying balance sheet and income statement information from Sparky Fitness Corporation which have been prepared at the end of November 30, 2017 to complete the following requirements: 1. Download the attached Excel file that includes Sparky Fitness Corporations General Journal, General Ledger, Trial Balance Worksheet, Income Statement and Balance Sheet. 2. The General Ledger has been set up with all of Sparky's Balance Sheet & Income Statement account titles as well as the beginning balances in each account as of December 1, 2017. Sparky's fiscal year end is December 31st. Closing entries are done annually at Dec 31. 3. Use the accompanying TRANSACTIONS for December to update the General Journal by recording the necessary journal entries, post to the appropriate t-accounts in the Ledger, and prepare a Trial Balance Before Adjustment as of December 31st using the tab labeled \"Trial Balance Worksheet\". 4. Use the check figure for Trial Balance Before Adjustment to confirm your TB is correct before moving on to the next step in the accounting cycle. 5. Use the ADDITIONAL INFORMATION to prepare (in good form) the necessary adjusting journal entries. Update all t-accounts in the Ledger and post to the Trial Balance located on the tab labeled Worksheet using the two columns labeled Adjustments. 6. Use the check figure for Trial Balance AFTER Adjustment to confirm your TB is correct before preparing the year-end financial statements. 7. Prepare in good form the Income Statement, Comprehensive Income Statement, Balance Sheet and Statement of Stockholder's Equity for the fiscal year end as of December 31, 2017. 8. Prepare all necessary Closing Entries and post to both the ledger accounts and update the Worksheet. 1 *Note: Use the Perpetual Inventory method for all sales of merchandise. Service Revenue Sparky Fitness Corporation Income Statement For the Period ending November 30, 2017 $1,421,2 84 Sales revenue Less: Operating expenses Cost of Goods Sold 172,885 $ 1,580,069 83,514 Advertising 21,080 Bad debt expense 0 Depreciation & Amortization 113,739 Insurance 40,144 Legal & Accounting 20,650 Office & Supplies expense 10,114 Payroll taxes 31,968 Property taxes 54,570 Repair and maintenance 16,028 Telephone 8,085 Utilities 41,049 Wages - Employees 394,670 Salaries - Officers 265,431 Total Operating Expenses 1,101,042 Income From Operations 479,027 Other Income & Expense: Interest expense (26,483) Gain (Loss) on Disposal of assets 0 Interest Revenue 0 Total Other Income (expense) (26,483) Income Before Taxes 466,644 Income Tax Expense 0 $466,6 44 NET INCOME 2 3 Sparky Fitness Corporation Balance Sheet As of November 30, 2017 Assets Current Assets: Cash & Cash Equivalents Accounts Receivables Allowance for Doubtful Accounts Inventory Office Supplies Prepaid Expenses Total Current Assets Long-Term Investments Investments-Available for Sale Securities +/- Fair Value Adjustment Total Investments Property, Plant and Equipment Property, Plant and Equipment Less: Accumulated Depreciation Total PP&E (net) Intangible Assets Patent (net) TOTAL ASSETS $ 213,023 245,680 ( 8,462) 20,160 1,540 26,774 $ $ 498,715 170,000 0 $ 170,000 $ 1,161,652 $ $ 205,000 2,035,367 $ 323,295 $ 1,523,692 ( 362,040) Total Liability & Stockholder's Equity Current Liabilities Accounts Payable Salaries & Wages Payable Payroll Taxes Payable Deferred Revenues Interest Payable Dividends Payable Note Payable - Short Term Income Taxes Payable Total Current Liabilities Long-Term Liabilities $ Note Payable - Long Term Stockholder's Equity Common Stock ($1 par, 160,000 shares issued & out) Additional Paid in Capital Retained Earnings Accumulated Other Comprehensive Income Total Stockholder's Equity TOTAL LIABILITIES & STOCKHOLDER'S EQUITY 21,429 4,350 5,813 265,220 26,483 0 0 0 588,500 $ 160,000 278,356 685,216 0 $ $ 1,123,572 2,035,367 Notes to Financial Statements: Sparky Fitness, an Arizona corporation, is principally engaged in the health and fitness industry. The company's activities are conducted through a gym facility located in the Tempe area. The gym is equipped with cardio and strength training equipment and the Company also provides an array of dynamic group exercise programs including Zumba, yoga, group cycling, peak performance training, and pilates. Fiscal Year Sparky Fitness was established as a health and fitness gym in 2014. The Company follows a fiscal year end of December 31st. Inventories Inventories consist of men's and women's apparel including tees, tanks and hoodies branded with the Sparky Fitness logo. Inventories are valued on a first-in, first-out basis, using the perpetual method. Any inventory losses due to shrink are included as part of COGS as this is not an unusual event for the company's operating environment. 4 Prepaid Expenses & Supplies Prepaid expenses include amounts paid in advance for insurance and advertising. Supplies include both office supplies and selling-related supplies which include hangers, signage and security tags for display of merchandise. Long-Term Investments Sparky purchased a long-term investment in a corporate bond that was issued at par for $170,000 on July 1, 2017. The bond earns 8% APR and pays interest semi-annually each Jun 30 and Dec 31. Sparky classifies this investment as Available-for-Sale and to comply with GAAP, will report this financial instrument at fair value as of the balance sheet date. The Company uses a \"Fair Value Adjustment\" account (an adjunct/contra account) to report any increase/decrease in the asset's value. Property, Plant and Equipment Property, Plant and Equipment are stated on the basis of historical cost. Depreciation of buildings and equipment are computed for financial reporting purposes on a straight-line basis, using service lives of 25 years for buildings and 7 years for equipment. Salvage value is anticipated to be 25% of original cost for buildings and 10% of original cost for equipment. Sparky records depreciation monthly. The book values of these assets are presented below: Land $ 102,472 Buildings Less: Accum. Depreciation $595,000 (69,913) Equipment Less: Accum. Depreciation 826,220 (292,127) 525,087 534,093 Property, Plant & Equipment (net) $1,161,652 Patent Sparky holds one patent for a unique piece of fitness equipment they developed. The patent was acquired on October 1, 2014. Amortization is recorded using the straight-line method, no salvage and a 10-year useful life. Sparky does not use an accumulated amortization account. The company records amortization monthly. Patent (net of $95,000 accumulated amortization) $205,000 Revenue Recognition The Company's sources of revenue include fitness club membership fees, personal training services and group classes. As a general principle, revenue is recognized when the following criteria are met: (i) a contract has been identified with a customer, (ii) the separate performance obligations have been identified, (iii) the transaction price to the buyer is determinable, (iv) the transaction price has been allocated to the separate performance obligations in the contract and (v) revenue is recognized when each performance obligation has been satisfied. The Company relies upon a signed contract between the customer and the Company as the persuasive evidence of a sales arrangement. Delivery of health club services extends throughout the membership terms. Personal Training and group classes represent separate, standalone performance obligations. Sparky allows membership dues to be paid in advance for up to 36 months. All advance payments from members are recorded as Deferred Revenues. Deferred revenues are recognized evenly over the terms of the contract. Delivery of personal training services occurs when individual personal training sessions or classes have been rendered. For sale of merchandise, the Company recognizes revenue upon customer receipt of the merchandise. Long-Term Debt 5 The long-term liabilities were outstanding for all of 2017 and accrue interest at 6% APR. Sparky records accrued interest quarterly (interest was last updated on Sept. 30th). The debt covenant stipulates that Sparky is required to pay interest annually each January 1st. 6 The following events took place during the month of December, 2017 affecting Sparky Fitness. Instructions Use the following TRANSACTIONS AND ADDITIONAL INFORMATION to complete the General Journal, Ledger, Worksheet and Financial Statements for the last month of operations for Sparky Fitness. Use the Perpetual Inventory method for all sales of merchandise. (See the last page for a detailed example of how the Perpetual Method is recorded and used). TRANSACTIONS Dec 1 At the beginning of December, the two principal owners invested an additional $70,000 each in exchange for shares of stock in preparation to expand their operations in the near future. The shares were trading at $4/share on December 1st. Dec 1 Purchased land for $60,000 to be used for a new gym facility with construction planned for 2019. Sparky paid 25% down and signed a 9-month, 6% note for the balance. Principal and interest is to be paid at maturity on August 31, 2018. Dec 1 The Company purchased additional equipment for $154,800, paying 30% down and giving a one-year, 6% note for the balance, with principal & interest payable at maturity. Dec 1 Employees are paid on the 1st and 15th of each month. Paid the balance of what was owed at November 30, 2017. Dec 1 Sold a piece of equipment for $5,420 cash proceeds. The equipment had an original cost of $18,000 and was 80% depreciated. Dec 2 Purchased on account a total of 320 t-shirts and tanks with a silk-screened Sparky logo from T-Shirt Creations at a price of $8 per shirt. These shirts are available for resale to customers. Dec 2 Received a bill for $1,550 from Sparky's attorney for legal services rendered, to be paid by January 2, 2018. Dec 4 Purchased office supplies for $500 on account from Max Office Supplies. Sparky has 30 days to pay for the supplies. Dec 5 During the first week of December, sold eighty, six-month memberships for an introductory price of $180 each. All membership fees were collected in cash. Dec 5 Sold twenty t-shirts to a corporate member, Allen & Associates for $20 each. Collected $100 in cash and the balance was charged to the customer's account. The original cost of the apparel was $160. Dec 7 During the first week of December, provided 210 hours of personal training services to members. Fees are charged at a rate of $20/hour. Of these fees, $700 was collected in cash and the balance was billed to individual member's accounts. Dec 15 For the first half of December, provided a variety of group exercise programs. Each participant is charged $10/class and 720 members were enrolled during this time. Billed the individual members' accounts for services provided. 7 Dec 15 Paid wages to employees of $10,200 and salaries to officers of $15,000; associated payroll taxes on these wages & salaries are $2,041. (You can record in two separate journal entries. Payroll taxes are an expense to the company for unemployment benefits and recorded as a payable to the state and federal taxing authority on a quarterly basis. They will be paid on Jan 1) Dec 15 Sold fifty, six-month memberships for $180 each. Collected the fees in cash. Dec 18 Paid the total amounts due to T-Shirt Creations, Sparky's attorney and Max Office Supplies. (record as one journal entry) Dec 21 Through the first three weeks in December, sold apparel to members for a retail selling price of $3,800. The original cost of the merchandise sold was $2,120. Collected 40% in cash, the balance was billed to each customer's account. Dec 22 Sparky declared a cash dividend of $0.10 per share, payable January 30, 2018. Dec 26 Collected the balance of what was owed on account from Allen & Associates. Also collected 40% of the fees for group exercise classes & personal training services billed to customer's accounts during the first half of December. Dec 27 Received a utility bill that totaled $1,750 for the month and a telephone bill for $256. Both were paid. Dec 31 Sparky Fitness provided you with the following information regarding the remaining activities for the month of December: a. Fees for group classes for the last half of December (billed to members' accounts) b. Fees for personal training sessions completed during December (60% collected in cash) c. Cash sales of apparel (original cost of apparel was $840) d. Credit sales for apparel during the last half of December. Cost of apparel sold from Dec. 16-30 e. Cash received during Dec. for services previously billed to members' accounts. f. Of the amounts owed on account, $11,300 represent Repairs that were made to the heating & cooling system Of the gym in November. This account was paid. g. Sparky was notified that one of their corporate account Members had declared bankruptcy. Spark wrote off The account. 8 $ 8,700 4,900 1,600 2,840 1,436 52,950 11,300 $ 2,170 ADDITIONAL INFORMATION: 1. Wages earned by employees during the last half of December and to be paid on January 1, were $12,400 and salaries to officers were $15,000. Associated payroll taxes on these wages and salaries were $2,810. 2. Of the Deferred Revenue account, 4.1% have now been earned. (round your answer to the nearest whole dollar). 3. Uncollectible accounts are estimated to be 4% of ending Accounts Receivable. (round your answer to the nearest whole dollar.) 4. Sparky prepays for some insurance and advertising. The prepaid expense account has a balance of $26,774 at year end, but before adjustment. This balance includes $12,240 for an 18-month casualty insurance policy purchased on March 1, 2017. Of the remaining prepaid balance, 60% of the advertising has now been used. (round to the nearest whole dollar.) 5. Sparky records depreciation & amortization on limited life tangible & intangible assets on a monthly basis using the straight-line method. (*refer to the footnotes for more details on original cost, salvage value and useful lives.) 6. The long-term liabilities were outstanding for all of 2017 and accrue interest at 6% APR. Sparky records accrued interest on long-term debt quarterly (interest was last updated on Sept. 30.) The company is required to pay the interest annually each January 1 st. Sparky also accrued interest on short-term notes payable. 7. At the end of December, a physical count was taken of Sparky's inventories & supplies. It revealed the following information: a. Inventories - Apparel: there were 1,310 units of apparel (t-shirts, tanks and hoodies) on hand at year end with an original cost of $17,930. b. Office Supplies: There was $780 of Office Supplies on hand as of December 31 st 8. Sparky received notice that accrued interest on the long-term investment had been sent and the check should arrive in early January, 2018. At December 31, the Long-Term Investment (Available for Sale Securities) had a fair value of $174,700. Sparky's tax rate is 35%. (record as two separate AJEs) 9. Income tax is based on a 35% tax rate. 9 10 PERPETUAL INVENTORY METHOD \"Perpetual\" means \"to keep a running total\" of the amount of inventory you have on hand at any point in time. To follow the \"Perpetual Method\CASE 1 Check Figures & Helpful Hints On the Trial Balance Worksheet: 1. 2. 3. 4. The debit & credit columns of your TRIAL BALANCE B-4 ADJ The debit and credit columns of your AJE's should total to: The Adjusted Trial Balance columns should total to: Net Income should be *This should match your closing entry #3. $ 3,874935 $ 240,387 $ 4,083,974 $ 266,891 5. Total Stockholder's Equity on 12/31 Balance Sheet should be: $ 1,047,374 **This should match your end. Bal. on Statement of Stockholder's Equity Helpful Hints: You will have (5) closing entries. They include the following: i. Closing Entry #1: Close ALL revenues and Gains to Income Summary. ii. Closing Entry #2: Close ALL expenses to Income Summary. Every individual expense must be closed out with a credit, and the sum of all expenses/losses can then be debited to your Income Summary in this entry. iii. Closing Entry #3: Take the difference between the credit posted to Income Summary (Closing Entry #1) less the debit posted to Income Summary (Closing Entry #2) to close out the Income Summary account and permanently move NI to R.E. iv. Closing Entry #4: Close out the dividend directly to R.E. v. Closing Entry #5: Close the OCI-Unrealized Gain/Loss-AFS to Accumulated Other Comprehensive Income (your balance sheet account). This closes out your current year (CY) Comprehensive Income accounts & transfers their balance to your Balance Sheet under the account \"Accumulated Other Comprehensive Income\"...a permanent account. Just like NI is permanently closed into Retained Earnings, your OCI items from the current year also need to be closed out and permanently stored in the Accumulated OCI account under Stockholder's Equity on your Balance Sheet. vi. Post all closing entries to the General Ledger in the row located in between the Balance Before Closing and the Balance AFTER closing. Make sure all temporary accounts show a zero balance after closing and that your Ending R.E. balance now matches the balance reported on the Balance Sheet & Statement of Stockholder's Equity. Only when posting the closing entries to RE does your End. RE balance get updated to match your actual financial statements. GENERAL JOURNAL GROUP 01 Date GENERAL JOURNAL Account Titles Page 1 Debit Credit GENERAL JOURNAL Page 2 GENERAL JOURNAL Date Date GENERAL JOURNAL - ADJUSTING ENTRIES Account Titles GENERAL JOURNAL - CLOSING ENTRIES Account Titles Page 3 Debit Debit Credit Credit GENERAL LEDGER PRACTICE SET GENERAL LEDGER ACCOUNT CASH & CASH EQUIVALENTS 1 GROUP 01 DATE BEG DR 213,023 CR BAL NOTES 213,023 CASH BALANCE ACCOUNTS RECEIVABLE BEG 245,680 245,680 ACCOUNTS RECEIVABLE BALANCE ALLOWANCE FOR DOUBTFUL ACCOUNTS BEG 8,462 8,462 UNADJUSTED ADA BALANCE 8,462 ENDING ALLOW FOR DOUBTFUL ACCTS INTEREST RECEIVABLE BEG 0 0 UNADJUSTED BALANCE 0 ENDING BALANCE INVENTORY - APPAREL BEG 20,160 20,160 UNADJUSTED INVENTORY BALANCE 20,160 ENDING INVENTORY-APPAREL BAL. OFFICE SUPPLIES BEG 1,540 1,540 UNADJUSTED SUPPLIES BALANCE 1,540 ENDING OFFICE SUPPLIES BALANCE PREPAID EXPENSES BEG 26,774 26,774 UNADJUSTED BALANCE 26,774 ENDING PREPAID EXPENSE BALANCE INVESTMENTS - AVAILABLE FOR SALE SECURITIESBEG 170,000 170,000 ENDING BALANCE Page 4 GENERAL LEDGER FAIR VALUE ADJUSTMENT BEG 0 0 0 BEG BALANCE 0 END BALANCE AFTER ADJUSTMENT LAND BEG 102,472 102,472 LAND BALANCE BUILDING BEG 595,000 595,000 END. BUILDING BALANCE ACCUMULATED DEPRECIATION - BUILDING BEG 69,913 69,913 UNADJUSTED ACCUM DEPR-BUILDING 69,913 ACCUM DEPRECIATION END BALANCE EQUIPMENT BEG 826,220 826,220 ENDING BALANCE - EQUIPMENT ACCUMULATED DEPRECIATION - EQUIPMENT BEG 292,127 292,127 UNADJUSTED ACCUM. DEPR.- EQUIP 292,127 ENDING ACCUM. DEPR.- EQUIP BAL PATENT BEG 205,000 205,000 UNADJUSTED PATENT BALANCE 205,000 ENDING PATENT BALANCE ACCOUNTS PAYABLE BEG 21,429 21,429 ACCOUNTS PAYABLE BALANCE SALARY & WAGES PAYABLE BEG 4,350 4,350 UNADJUSTED SALARY & WAGES PAYABLE 4,350 ENDING SALARY & WAGES PAYABLE BAL PAYROLL TAXES PAYABLE BEG 5,813 5,813 UNADJUSTED PAYROLL TAXES PAYABLE 5,813 ENDING PAYROLL TAXES PAYABLE DEFERRED REVENUES BEG 265,220 265,220 UNADJUSTED DEFERRED REVENUE BAL 265,220 ENDING DEFERRED REVENUE BALANCE INTEREST PAYABLE BEG 26,483 26,483 UNADJUSTED INTEREST PAYABLE BAL 26,483 INTEREST PAYABLE BALANCE DIVIDEND PAYABLE BEG 0 0 UNADJUSTED DIVIDEND PAYABLE 0 END. BAL. DIVIDEND PAYABLE NOTE PAYABLE - SHORT TERM BEG 0 Page 5 GENERAL LEDGER 0 END. BAL. NOTE PAYABLE - SHORT-TERM INCOME TAXES PAYABLE BEG 0 0 UNADJUSTED INCOME TAXES PAYABLE 0 END. BAL. INC. TAX PAYABLE NOTES PAYABLE - Long-term BEG 588,500 588,500 L-T NOTES PAYABLE BALANCE COMMON STOCK BEG 160,000 160,000 COMMON STOCK BALANCE ADDITIONAL PAID IN CAPITAL BEG 278,356 278,356 END. APIC BALANCE RETAINED EARNINGS - JANUARY 1 BEG 218,572 218,572 RETAINED EARNINGS BEFORE CLOSING 218,572 RETAINED EARNINGS AT DEC 31 DIVIDENDS BEG 0 0 DIVIDEND BALANCE BEFORE CLOSING 0 DIVIDEND BALANCE AFTER CLOSING ACCUMULATED OTHER COMPREHENSIVE INCOMEBEG 0 0 AOCI BALANCE BEFORE CLOSING 0 END. AOCI AFTER CLOSING SERVICE REVENUE BEG 1,421,284 1,421,284 UNADJUSTED SERVICE REVENUE BAL 1,421,284 ADJUSTED SERVICE REVENUE BALANCE 1,421,284 BALANCE AFTER CLOSING SALES REVENUE BEG 172,885 172,885 SALES REVENUE BALANCE 172,885 BALANCE AFTER CLOSING COST OF GOODS SOLD BEG 83,514 83,514 UNADJUSTED COGS 83,514 ADJUSTED COGS 83,514 BALANCE AFTER CLOSING ADVERTISING EXPENSE BEG 21,080 21,080 UNADJUSTED BALANCE Page 6 GENERAL LEDGER 21,080 ADJUSTED BALANCE 21,080 BALANCE AFTER CLOSING BAD DEBT EXPENSE BEG 0 0 UNDADJUSTED BAD DEBT EXPENSE 0 BAD DEBT EXP AFTER ADJUSTMENT 0 BALANCE AFTER CLOSING DEPRECIATION & AMORTIZATION EXPENSE BEG 113,739 113,739 UNADJUSTED DEPR & AMORT EXP 113,739 ADJUSTED BALANCE 113,739 BALANCE AFTER CLOSING INSURANCE EXPENSE BEG 40,144 40,144 BALANCE BEFORE ADJUSTMENT 40,144 ADJUSTED BALANCE 40,144 BALANCE AFTER CLOSING LEGAL & ACCOUNTING BEG 20,650 20,650 BALANCE BEFORE CLOSING 20,650 BALANCE AFTER CLOSING OFFICE & SUPPLIES EXPENSE BEG 10,114 10,114 UNADJUSTED BALANCE 10,114 ADJUSTED BALANCE 10,114 BALANCE AFTER CLOSING PAYROLL TAX EXPENSE BEG 31,968 31,968 UNADJUSTED BALANCE 31,968 ADJUSTED BALANCE 31,968 BALANCE AFTER CLOSING PROPERTY TAXES BEG 54,570 54,570 BALANCE BEFORE CLOSING 54,570 BALANCE AFTER CLOSING REPAIRS & MAINTENANCE EXPENSE BEG 16,028 16,028 BALANCE BEFORE CLOSING 16,028 BALANCE AFTER CLOSING TELEPHONE BEG 8,085 8,085 BALANCE BEFORE CLOSING 8,085 BALANCE AFTER CLOSING UTILITIES EXPENSE BEG 41,049 41,049 BALANCE BEFORE CLOSING 41,049 BALANCE AFTER CLOSING WAGES - EMPLOYEES BEG 394,670 394,670 UNADJUSTED BALANCE Page 7 GENERAL LEDGER 394,670 ADJUSTED BALANCE 394,670 BALANCE AFTER CLOSING SALARIES - OFFICERS BEG 265,431 265,431 UNADJUSTED BALANCE 265,431 ADJUSTED BALANCE 265,431 BALANCE AFTER CLOSING INTEREST EXPENSE BEG 26,483 26,483 UNADJUSTED BALANCE 26,483 ADJUSTED BALANCE 26,483 BALANCE AFTER CLOSING GAIN (LOSS) ON DISPOSAL OF ASSETS BEG 0 0 0 BALANCE BEFORE CLOSING 0 BALANCE AFTER CLOSING INTEREST REVENUE BEG 0 0 UNADJUSTED BALANCE 0 ADJUSTED BALANCE 0 BALANCE AFTER CLOSING INCOME TAX EXPENSE BEG 0 0 UNADJUSTED BALANCE 0 ADJUSTED BALANCE 0 BALANCE AFTER CLOSING UNREALIZED GAIN (LOSS) AFS SECURITIES BEG 0 0 0 UNADJUSTED BALANCE 0 ADJUSTED BALANCE 0 BALANCE AFTER CLOSING INCOME SUMMARY BEG 0 0 BEG. BALANCE 0 INCOME SUMMARY BALANCE 0 BALANCE AFTER CLOSING Page 8 TRIAL BALANCE WORKSHEET GROUP ID 01 TRIAL BALANCE ADJUSTMENTS ACCOUNT DR CR DR CR CASH & CASH EQUIVALENTS 213,023 ACCOUNTS RECEIVABLE 245,680 ALLOWANCE FOR DOUBTFUL ACCOUNTS 8,462 INTEREST RECEIVABLE 0 0 INVENTORY - APPAREL 20,160 OFFICE SUPPLIES 1,540 PREPAID EXPENSES 26,774 INVESTMENT-AVAILABLE FOR SALE SECURITIES 170,000 FAIR VALUE ADJUSTMENT 0 0 0 LAND 102,472 BUILDING 595,000 ACCUMULATED DEPRECIATION - BUILDING 69,913 EQUIPMENT 826,220 ACCUMULATED DEPRECIATION - EQUIPMENT 292,127 PATENT 205,000 ACCOUNTS PAYABLE 21,429 SALARY & WAGES PAYABLE 4,350 PAYROLL TAXES PAYABLE 5,813 DEFERRED REVENUES 265,220 0 INTEREST PAYABLE 26,483 DIVIDEND PAYABLE 0 NOTES PAYABLE -SHORT-TERM 0 INCOME TAXES PAYABLE 0 NOTES PAYABLE -LONG-TERM 588,500 COMMON STOCK 160,000 ADDITIONAL PAID IN CAPITAL 278,356 RETAINED EARNINGS 218,572 DIVIDENDS 0 ACCUMULATED OTHER COMPREHENSIVE INCOME 0 SERVICE REVENUE 1,421,284 SALES REVENUE 172,885 COST OF GOODS SOLD 83,514 0 ADVERTISING EXPENSE 21,080 0 BAD DEBT EXPENSE 0 0 DEPRECIATION & AMORTIZATION EXPENSE 113,739 0 INSURANCE EXPENSE 40,144 0 LEGAL & ACCOUNTING 20,650 OFFICE & SUPPLIES EXPENSE 10,114 0 PAYROLL TAX EXPENSE 31,968 0 PROPERTY TAX EXPENSE 54,570 REPAIRS & MAINTENANCE 16,028 TELEPHONE 8,085 UTILITIES 41,049 WAGES - EMPLOYEES 394,670 0 SALARIES- OFFICERS 265,431 0 INTEREST EXPENSE 26,483 0 GAIN (LOSS) ON DISPOSAL OF ASSETS 0 INTEREST REVENUE 0 INCOME TAX EXPENSE 0 0 UNREALIZED GAIN/LOSS - AFS 0 INCOME SUMMARY 0 Trial Balance Totals 3,533,394 3,533,394 0 ADJ. TRIAL BALANCE DR CLOSING ENTRIES CR DR CR 213,023 245,680 0 0 0 0 8,462 0 20,160 1,540 26,774 170,000 0 102,472 595,000 0 69,913 826,220 0 0 292,127 205,000 21,429 4,350 5,813 265,220 26,483 0 0 0 588,500 160,000 278,356 218,572 0 0 1,421,284 172,885 0 0 0 0 0 0 0 0 83,514 21,080 0 113,739 40,144 20,650 10,114 31,968 54,570 16,028 8,085 41,049 394,670 265,431 26,483 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3,533,394 3,533,394 0 0 0 0 0 0 Page 9 0 0 0 0 0 0 AFTER CLOSING TB DR CR 213,023 245,680 8,462 0 20,160 1,540 26,774 170,000 0 102,472 595,000 69,913 826,220 292,127 205,000 21,429 4,350 5,813 265,220 26,483 0 0 0 588,500 160,000 278,356 218,572 0 0 1,421,284 172,885 83,514 21,080 0 113,739 40,144 20,650 10,114 31,968 54,570 16,028 8,085 41,049 394,670 265,431 26,483 0 0 0 0 0 0 3,533,394 3,533,394 FINANCIAL STATEMENTS GROUP 01 SPARKY FITNESS CORPORATION INCOME STATEMENT SPARKY FITNESS CORPORATION INCOME STATEMENT For the Year Ending For the Period Ending DECEMBER 31, 2017 NOVEMBER 30, 2017 SERVICE REVENUE SALES REVENUE TOTAL REVENUES OPERATING EXPENSES: COST OF GOODS SOLD ADVERTISING BAD DEBT EXPENSE DEPRECIATION & AMORTIZATION EXPENSE INSURANCE EXPENSE LEGAL & ACCOUNTING OFFICE & SUPPLIES EXPENSE PAYROLL TAX EXPENSE PROPERTY TAXES REPAIRS & MAINTENANCE TELEPHONE UTILITIES WAGES - EMPLOYEES SALARIES - OFFICERS TOTAL OPERATING EXPENSES INCOME FROM OPERATIONS OTHER INCOME & EXPENSE INTEREST EXPENSE GAIN (LOSS) ON DISPOSAL OF ASSETS INTEREST REVENUE TOTAL OTHER INCOME (EXPENSE) INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME SERVICE REVENUE SALES REVENUE TOTAL REVENUES OPERATING EXPENSES: COST OF GOODS SOLD ADVERTISING BAD DEBT EXPENSE DEPRECIATION & AMORTIZATION EXPENSE INSURANCE EXPENSE LEGAL & ACCOUNTING OFFICE & SUPPLIES EXPENSE PAYROLL TAX EXPENSE PROPERTY TAXES REPAIRS & MAINTENANCE TELEPHONE UTILITIES WAGES - EMPLOYEES SALARIES - OFFICERS TOTAL OPERATING EXPENSES INCOME FROM OPERATIONS OTHER INCOME & EXPENSE INTEREST EXPENSE GAIN (LOSS) ON DISPOSAL OF ASSETS INTEREST REVENUE TOTAL OTHER INCOME (EXPENSE) INCOME BEFORE TAXES INCOME TAX EXPENSE NET INCOME 1,421,284 172,885 $ 83,514 21,080 1,594,169 113,739 40,144 20,650 10,114 31,968 54,570 16,028 8,085 41,049 394,670 265,431 1,101,042 493,127 26,483 0 0 (26,483) 466,644 0 466,644 COMPREHENSIVE INCOME STATEMENT COMPREHENSIVE INCOME STATEMENT For the Year Ending December 31, 2017 For the Period Ending November 30, 2017 STATEMENT OF STOCKHOLDERS' EQUITY STATEMENT OF STOCKHOLDERS' EQUITY AS OF DECEMBER 31, 2017 BEGINNING BALANCE (JANUARY 1, 2017) CONTRIBUTED CAPITAL NET INCOME DIVIDENDS OTHER COMPREHENSIVE INCOME ENDING BALANCE Current Assets: Cash & Cash Equivalents Accounts Receivables Allowance for Doubtul Accounts Interest Receivable Inventory $ COMMON RETAINED ACCUM STOCK EARNINGS OCI $ 438,356 $ 218,572 AS OF NOVEMBER 30, 2017 TOTAL STOCKHOLDER EQUITY 0 $656,928 BEGINNING BALANCE (JANUARY 1, 2017) CONTRIBUTED CAPITAL NET INCOME DIVIDENDS OTHER COMPREHENSIVE INCOME ENDING BALANCE TOTAL COMMON RETAINED ACCUM STOCKHOLDER STOCK & APIC EARNINGS OCI EQUITY $ 438,356 $ 218,572 0 $656,928 466,644 $ 438,356 BALANCE SHEET BALANCE SHEET As of December 31, 2017 As of November 30, 2017 Current Assets: Cash & Cash Equivalents Accounts Receivables Allowance for Doubtul Accounts Interest Receivable Inventory Page 10 $ 685,216 $213,023 245,680 (8,462) 237,218 0 20,160 0 $ 1,123,572 FINANCIAL STATEMENTS Office Supplies Prepaid Expenses Total Current Assets Long-Term Investments Investments-Available For Sale Securities +/- Fair Value Adjustment Total Investments at Fair Value Property, Plant & Equipment Land Buildings Less: Accumulated Depr - Buildings Office Supplies Prepaid Expenses Total Current Assets Long-Term Investments Investments-Available For Sale Securities +/- Fair Value Adjustment Total Investments at Fair Value Property, Plant & Equipment Land Buildings Less: Accumulated Depr - Buildings Equipment Less: Accumulated Depr - Equipment Total Property, Plant & Equipment (net) Intangibles: Patent (net) TOTAL ASSETS Equipment Less: Accumulated Depr - Equipment Total Property, Plant & Equipment (net) Intangibles: Patent (net) TOTAL ASSETS Current Liabilities: Accounts Payable Salaries & Wages Payable Payroll Taxes Payable Deferred Revenues Interest Payable Dividend Payable Note Payable - Short Term Income Taxes Payable Total Current Liabilities Long-Term Debt: NOTES PAYABLE -LONG-TERM Total Liabilities Current Liabilities: Accounts Payable Salaries & Wages Payable Payroll Taxes Payable Deferred Revenues Interest Payable Dividend Payable Note Payable - Short Term Income Taxes Payable Total Current Liabilities Long-Term Debt: Note Payable - Long Term Total Liabilities Stockholder's Equity Common Stock ($1 Par, _________ shares issued & outstanding) Additional Paid In Capital Retained Earnings Accumulated Other Comprehensive Income Total Stockholder's Equity TOTAL LIABILITIES & STOCKHOLDERS EQUITY Stockholder's Equity Common Stock ($1 Par, 160,000 shares issued & outstanding) Additional Paid In Capital Retained Earnings Accumulated Other Comprehensive Income Total Stockholder's Equity TOTAL LIABILITIES & STOCKHOLDERS EQUITY Page 11 1,540 26,774 $498,715 170000 $170,000 $102,472 595,000 (69,913) 525,087 826,220 (292,127) 534,093 ### $205,000 ### $21,429 4350 5,813 265,220 26,483 0 0 0 323,295 588,500 911,795 $160,000 278,356 685,216 0 ### 2,035,367 ACC340 The Accounting Cycle - Group Project Name _________________________ GROUP EVALUATION FORM Your group project is worth a total of 25 points. Of these points: 20 points will be awarded based on accuracy, completion and the use of EXCEL formulas. The remaining 5 points will be awarded based on your peer evaluations to assess participation and contribution to the successful completion of the project. Please list the members of your group, and rate them from 0 to 5 (5 being the best) for their participation in this project. Any score below 1 or above 4 must be accompanied by a written explanation. Participation includes showing up for scheduled meetings, substantive contributions (does not include bringing food or drinks to meetings), and attitude. You only have to evaluate your other group members...not yourself!! Name Rating ____________________________ ______ ____________________________ ______ ____________________________ ______ ____________________________ ______ Comments INSTRUCTIONS Sparky Fitness A Practice Problem in Financial Accounting Each group will work the project and turn in one printed copy of the formal financial statements and one electronic file with the excel spreadsheet for your group and only your group. Do not copy files between groups, do not share files between groups. (These actions will be treated as plagiarism and grade penalties will be severe.) Case 1 is worth 25 points: 20 points of your grade will be a common group grade based on the completeness and accuracy of your group's work. The remaining 5 points will be based on the other group members' assessment of your individual contribution to the project. Anyone not actively participating in the project will receive a zero on the assignment. 1. Download the transactions for the month of December for Sparky Fitness. Also download the excel spreadsheet file for your group number and save to your own USB drive/disk/hard drive/etc. (Do not attempt to do any work in the Excel file until you have saved it to your own drive/disk/etc. as none of your work will be saved.) Enter transactions for December as journal entries in the General Journal of the spreadsheet. Use the format we use in class to write these journal entries. (The debit line comes first, then drop down one line, indent, and record the second account title). 2. Post from the \"General Journal\" into the \"General Ledger\" (using the cell referencing feature of excel is required to build an integrated spreadsheet). Post the debit or credit in the appropriate account, and make a note in the \"notes\" column (if you feel it is helpful) so that you will be able to track your entries when you make any changes (this is necessary usually only for Cash). The spreadsheet will automatically compute a balance in the \"Unadjusted Balance\" cell. Do not enter any negative numbers. There are enough lines for all necessary entries, so if you run out of space, it means that you have not done it correctly. Start the entries on the top line of each t-account right under the Beginning Balance. You should never get a negative balance. If you do, re-check to see if you posted the entry correctly to either the debit or credit column. I have protected cells in these sheets so that you shouldn't be able to inadvertently mess up the formulae. However, if you post an entry to the Ledger and later want to remove it...DON'T hit the delete key...simply hit the backspace key to clear the cell (unless you're using a Mac...then you should be fine). Hitting delete may cause an error in the formulae and then your trial balance will not balance. You will have to ask me to re-set the formulae in order for your group to finish the project. The unadjusted balances will post automatically into the Unadjusted Trial Balance on the \"Trial Balance Worksheet\" page of the spreadsheet (the third tab at the bottom of the excel file). After you finish with the December transactions you need to check the Unadjusted Trial Balance to make sure it balances! (Refer to check figure to insure you have it correct before moving to the next step.) 3. Enter the adjusting entries in the General Journal starting on Line 113. (It is titled \"General Journal - Adjusting Entries\".) You are provided with a listing of adjusting entries on page 7 of the transaction sheet. Record all 9 AJE's and post to the General Ledger 4. To post the adjusting entries into the \"General Ledger\" use cell referencing. Do not use negative numbers. Place your cursor in the appropriate cell of the t-account on the line below the \"Unadjusted Balance\" and cell reference the AJE to this location. By doing so, the adjusting entries will automatically post into the \"Adjustments\" column of the \"Worksheet\". When you are done with your adjustments, make sure that the debit and credit columns for each pair of columns on the Worksheet are equal. The adjusting entries will be combined automatically with the unadjusted balances to provide the final \"Adjusted Trial Balance\" on the \"Worksheet\". These are the numbers you want to use in preparing the formal financial statements. The \"Worksheet\" page is automated; you will do all of your work on the \"General Journal\