Norfolk Sports Company (NSC), a sports items manufacturer in Norfolk, VA produces basketball and basketball related accessories. NSC has customers across the United States. However, most of their customers are from neighboring cities. NSC uses perpetual inventory system to account for their inventories (raw materials, work in process, and finished goods). We will use NSCs business events from the month of January 2019 for this Excel project. For the simplicity of the project, we did not consider manufacturing related business events and related accounting entries. Step by step instructions: 1. First create a Chart of Accounts (COA) in MS Excel based on the COA provided here. 2. Capture the journal entries from the transactions provided. Remember, some transactions may not have a journal entry. For some transactions, you may have to look for more information in some other related transactions. 3. Once you are done with your general journal entries, you need to do month-end adjusting entries. 4. Once you are done with your general journal entries and adjusting entries, you need to summarize journal information to transfer the summarized information to the trial balance. Here you need to use Pivot Table to get the summary information of the journal entries. You need to get separate Pivot Table for both general journal entries and adjusting entries. 5. We will use some excel tools to avoid duplicate jobs. Once we have one information in the system, if we need to use that information, we will try to use some kinds of links rather than reentering the same information. (For example: Data Validation, VLOOKUP etc.) 6. In your trial balance, you cannot input number manually and you cannot get any total manually. 7. Follow the sample excel file to understand how and where to use any excel tool or formula. Chart of Accounts G/L Account ID Description Type 10100 Cash Cash 10200 Accounts Receivable Accounts Receivable 10210 Accts. Rec. from Employees Accounts Receivable 10300 Allowance for Doubtful Accts. Accounts Receivable 20100 Inventory - Supplies Inventory 20200 Inventory - Raw Materials Inventory 20300 Inventory - Finished Goods Inventory 20400 Prepaid Rent Other Current Assets 20500 Prepaid Insurance Other Current Assets 20600 Prepaid Others Other Current Assets 20700 Marketable Securities Other Current Assets 20800 Interest Receivable Other Current Assets 21100 Land Fixed Assets 21200 Office Furniture & Equipment Fixed Assets 21210 Accumulated Depreciation - Office Equipment Fixed Assets 21300 Production Equipment Fixed Assets 21310 Accumulated Depreciation - Production Equipment Fixed Assets 21400 Notes Receivable Fixed Assets 30100 Accounts Payable Accounts Payable 30200 Wages and Salaries Payable Other Current Liabilities 30300 Federal Income Tax Withheld Other Current Liabilities 30400 State Unemployment Taxes Pay. Other Current Liabilities 30500 Fed. Unemployment Taxes Pay. Other Current Liabilities 30600 FICA Taxes Payable Other Current Liabilities 30700 Federal Income Taxes Payable Other Current Liabilities 30800 Dividends Payable Other Current Liabilities 30900 Interest Payable Other Current Liabilities 30150 Unearned Revenue Other Current Liabilities 31000 Notes Payable Long Term Liabilities 40000 Common Stock Equity 40100 PIC in Excess of Par Equity 40200 Retained Earnings Equity 40300 Dividends Declared Equity 50000 Sales Income 50100 Sales Returns and Allowances Income 50200 Sales Discounts Taken Income 51000 Cost of Goods Sold Cost of Sales 51100 Purchases Discounts Taken Cost of Sales 51200 Freight-in Cost of Sales 52000 G/L on Sale of Fixed Assets Income 53000 G/L on Sale of Mkt. Securities Income 54000 Interest/Dividend Income Income 55000 Miscellaneous Revenue Income 60100 Rent Expense Expenses 60200 Advertising Expense Expenses 60300 Insurance Expense Expenses 60400 Depreciation Expense Expenses 60500 Wages and Salaries Expense Expenses 60600 Payroll Tax Expense Expenses 60700 Federal Income Tax Expense Expenses 60800 Interest Expense Expenses 60900 Bad Debt Expense Expenses 61000 Shipping Expense Expenses 62000 Office Supplies Expense Expenses Account Balances as of December 31st, 2018 G/L Account ID Description Debit Balance Credit Balance 10100 Cash 623,857.00 10200 Accounts Receivable 154,600.00 20100 Inventory - Supplies 12,752.00 20200 Inventory - Raw Materials 182,497.00 20300 Inventory - Finished Goods 684,732.00 20400 Prepaid Rent 30,000.00 20500 Prepaid Insurance 11,250.00 20600 Prepaid Others 900.00 20700 Marketable Securities 3,750.00 21100 Land 715,000.00 21200 Office Furniture & Equipment 37,800.00 21300 Production Equipment 1,305,000.00 21210 Accumulated Depreciation - Office Equipment 22,680.00 21310 Accumulated Depreciation - Production Equipment 261,000.00 30100 Accounts Payable 263,700.00 30700 Federal Income Taxes Payable 37,500.00 40000 Common Stock 1,500,000.00 40100 PIC in Excess of Par 750,000.00 40200 Retained Earnings 927,258.00 3,762,138.00 3,762,138.00 January 2019 Events Event Date Description of Event 1 01/02/2019 NSC placed a purchase order with Hampton Sporting Goods (HSG) to purchase raw materials. The purchase order total is $165,000. The shipping cost is estimated $850. NSC gets free shipping if the total order value is higher than $100,000. NSC follows n/30 payment term with HSG. 2 01/02/2019 NSC received raw materials from Suffolk Sports Heaven (SSH). The order for the raw materials was placed on December 21st. SSH didnt have enough inventory to deliver this product immediately. However, SSH had the manufacturing schedule for this product and promised to deliver the products within 1st week of January. The purchase order total was $98,000 for this order. NSC follows n/30 payment term with SSH. 3 01/03/2019 NSC paid $112,000 for the raw materials that they received from HSG on December 28, 2018. 4 01/03/2019 John Brothers in Richmond, VA ordered $347,000 of Basket Pole Pad from NSC. The Bikes Pole Pad cost NSC $210,000. The shipping cost for this order is estimated $1750 and NSC pays the shipping charge to the freight company when the product is shipped. 5 01/04/2019 Andy Sports in Chester, VA made a payment of $62,500 to NSC via bank transfer for the Basketball bag that they received from NSC on December 28, 2018. 6 01/04/2019 NSC received a payment of 19,800 from Elizabeth Sports in Elizabeth City, NC for the balance due on their account. 7 01/04/2019 NSC sent one of their staffs to Staples to pick offices supplies, the order for which was placed with Staples using Stapless online order platform. The order was placed for $785 of office supplies. NSC purchases on credit from Staples and makes payment within 10 days. NSC determines supplies expense at the end of the month. 8 01/07/2019 NSCs business is growing. To support this new business NSC is planning to recruit some new employees. To accommodate the newly recruited employees, NSC made some renovations in their office complex. As part of the renovation plan, NSC placed a purchase order with Wayfair for $24,000 office furniture. They are expecting to receive the furniture in two weeks. NSC established a payment term of n/30 with Wayfair. 9 01/07/2019 Andy Sports in Chester, VA placed order for $196,800 of Basketball Bag with NSI. The Basketball Bag cost NSC $114,600. The shipping cost for this order is estimated $1250. NSC offers free shipping for its customers when the order value is higher than $200,000. 10 01/08/2019 NSC received the raw materials from HSG for the order placed on January 2nd. NSC also received the invoice for this order. 11 01/09/2019 NSC made the payment for office supplies that they purchased on January 7th. 12 01/09/2019 NSC shipped the products to John Brothers for the order that they received on January 3rd. 13 01/14/2019 NSC pays its employees twice a month. The pay dates are 16th and 30th of the month. For February its the last day of the month. NSC accrues salaries and wages expense two working days prior to the payment day. Today is the day for NSC to estimate salaries and wages expenses. NSC estimated that the total salaries and wages payment for the pay date on 16th is $78,200. 14 01/14/2019 NSC made the payment for the raw materials received on January 8th. 15 01/14/2019 NSC made a payment of $134,000 to SSH for the products that they received on December 29th. 16 01/15/2019 NSC shipped the products for the order that Andy Sports placed with NSC on January 7th. 17 01/15/2019 Jenny Sports in Huntington, WV has a due balance of $74,800 from their purchases in 2018 from NSC. They made the full payment of their due balance. 18 01/16/2019 John Brothers made the payment via bank transfer for the products that they purchased from NSC. NSC shipped the products to John Brothers on January 9th. 19 01/16/2019 NSC paid its employees today. (We are ignoring payroll taxes for this project). 20 01/18/2019 NSC received the office furniture today from Wayfair. They placed the order with Wayfair on January 7th 21 01/18/2019 Due to increased business growth, NSC is considering increasing their production. In line with this plan, NSC rented a warehouse to keep their raw materials. They will start using newly rented warehouse from February 1st ,2019. NSC paid $8,775 as six months advance rent to use this warehouse. 22 01/21/2019 NSC made a payment of $42000 for the raw materials that they received from SSH on January 2nd. 23 01/23/2019 Procurement manager of Sheldon sports, NC paid a visit to NSC facility. They are very impressed with the quality of NSC products. They entered into an agreement with NSC to purchase Basketball products from NSC. 24 01/24/2019 NSC pays federal income taxes quarterly basis. They paid the due taxes for the quarter ended on December 31st, 2018. 25 01/28/2019 NSC accrues salaries and wages expenses for the last half of the month. The next pay date is on 30th January. There are no changes in the employees (no new employee was hired, and no existing employee left.) The total salaries and wages expenses should be the same as the last pay date. 26 01/30/2019 NSC paid the employees today. Below is the information related to month-end adjusting entries for the month of January 2019. All the adjustments are done as of January 31st, 2019. 1. NSC rented their current office premise in June 2018 and made a payment of $60,000 to use the office premise for next 12 months starting from July 1st, 2018. NSC considers equal rent expense for each month and adjusts the rent expense at the end of each month. 2. NSC made insurance payments for the production machinery to be used in their factory. New insurance period started on October 1st, 2018 and ends in September 30th, 2019. The insurance expense is equal for each month and NSC adjusts the insurance expense at the end of each month. NSC paid $15,000 in September 2018 for 12-month insurance starting the insurance period on October 1st, 2018. 3. NSC planned to campaign their products at different universities in the Norfolk area. As part of that plan, they initiated a 3-month advertising program with Old Dominion University. They will be using display monitors at Strome College of Business to advertise their products. NSC paid $1350 in October 2018 to use the display monitors starting from November 1st, 2018. NSC will consider equal advertising expense for each of the month and adjust the expense at the end of the month. 4. The expected life of the production equipment is 15 years and has no salvage value. NSC placed them in Service on January 1st, 2016. NSC uses straight line depreciation for fixed assets. If any asset is placed in service at the 1st half of the month, NSC depreciates that for the entire month for that month. If the asset is placed in service in the 2nd half of the month, NSC starts depreciating that asset from the 2nd month. 5. The expected life of the office equipment is 5 years with no salvage value and all the office equipment (except the new office furniture that were purchased in this month) were placed in service at the same time as the production equipment. The new office furniture also has expected life of 5 years and no salvage value. 6. NSC counted the office supplies at the end of the month and found that NSC has $7,800 unused office supplies. Closing Entries: You will also have to do closing entries, same as, year-end closing entries. Assignment Submission: 1. Submit your project (Excel File) in blackboard. DO NOT EMAIL ME YOUR PROJECT. 2. The format of excel file name should be: Lastname_Firstname_CRNnumber. For example, if my last name is Johnson, first name is Andrew, and my CRN number is 30205 then my file name should be: Johnson_Andrew_30205. Rubrics: SL Particulars Point 1 Right Use of File Name 6 2 Data Validation Tool Used 12 5 Use of VLOOKUP 18 4 Use of Pivot Table 14 6 Use of IFERROR 8 7 Use of Formula to get balances in Trial Balance 12 8 Beginning Balance Total (3,762,138) 5 9 Total Balance of General Journal Entries (2,474,895) 5 10 Total Balance of Adjusting Entries (20,317) 5 11 Adjusted Balance Total in Trial Balance (4,110,318) 5 12 Net Income (40,733) 5 13 Final Balance in Trial Balance (3,607,251) 5 Total 100