NVCC Accounting Inventory and Depreciation analysis | Fall 2015 | |
Instructions: Use the numbers below for each PART (A, B etc) and Step (1,2 etc). Add numbers into the cells and answer |
any questions asked in each of the "call out" boxes. |
| | | | | | | | |
NUMBERS PROVIDED (See Instructions and use for Part A Income Statement) | | | | |
| | | | | | | | |
Sales price | $700.00 | | Revenue for 2014 | | Step 1 | | |
Quantity (Units) sold in 2014 | 12,000 | | | | | | | |
| | | | | | | | |
Units carried over from 2013 | 4,000 | | Cost of Existing inventory | Step 2 | | |
Price per unit of inventory carried over | $45.00 | | | | | | | |
| | | | | | | | |
Units purchased for each monthly purchase | 500 | | | | | Step 3 | | |
Purchase price of Jan 1 units | $110.00 | | | | | | | |
Purchase price for Jan 15 units | $115.00 | | | | | | | |
Purchase price for Feb 1 units | $120.00 | | Used to complete the | Inventory carryover | | |
Purchase price for Feb 15 units | $125.00 | | | and purchases table | | | |
Purchase price for Mar 1 units | $130.00 | | | | | | | |
Purchase price for Mar 15 units | $135.00 | | | | | | | |
Purchase price for Apr 1 units | $140.00 | | | | | | | |
Purchase price for Apr 15 units | $145.00 | | | | | | | |
Purchase price for May 1 units | $150.00 | | | | | | | |
Purchase price for May 15 units | $155.00 | | | | | | | |
Purchase price for Jun 1 units | $160.00 | | | | | | | |
Purchase price for Jun 15 units | $165.00 | | | | | | | |
Purchase price for July 1 units | $170.00 | | | | | | | |
Purchase price for July 15 units | $175.00 | | | | | | | |
Purchase price for Aug 1 units | $180.00 | | | | | | | |
Purchase price for Aug 15 units | $185.00 | | | | | | | |
Purchase price for Sept 1 units | $190.00 | | | | | | | |
Purchase price for Sept 15 units | $195.00 | | | | | | | |
Purchase price for Oct 1 units | $200.00 | | | | | | | |
Purchase price for Oct 15 units | $205.00 | | | | | | | |
Purchase price for Nov 1 units | $210.00 | | | | | | | |
Purchase price for Nov 15 units | $215.00 | | | | | | | |
Purchase price for Dec 1 units | $220.00 | | | | | | | |
Purchase price for Dec 15 units | $225.00 | | | | | | | |
| | | | | | | | |
For Part A - steps 4, 5, 6 and 7 use the data supplied above. | | |
| |
| | | | | | | | |
| | | | | | | | |
See Instuctions for Step 8 in Part B Balance Sheet | | | | | | | |
| | | | | | | | |
See Instuctions for Step 9 in Part C Depreciation | | | | | | | |
| | | | | | | | |
See Instuctions for Step 10 in Part D Break Even point analysis | | | | | |
| | | | | | | | |
After completing the 10 steps you are done. Email this Excel back to me. Dr. Dantonio Periodic Inventory project | | | | | | | | Instructions: Complete this project by using the numbers provided. |
| | Question : What effect does the Inventory method have on the net | | Follow the steps (do not alter the 3 Income statements below) | | Income, EPS, taxes and Remaining Inventory? | | Go to Step 1 - Calculate the revenue (note change on income statement) | | Answer: | | For Step 2 - Calculate the cost in Dollars and value of existing Inventory | | | | for the "carried from 2013" inventory. | | | | For Step 3 - Calculate the cost in dollars and value of existing Inventory | | | | for each purchase using the price for that date. | | | | For Step 4 - Enter units sold for each method (they are the same) | | | | For Step 5 - Use the completed Inventory Carryover and Purchases | | | | Table to calculate the COGS using FIFO and LIFO. | | | | For Step 6 - Use the completed Inventory Carryover and Purchases | | | | | | | | Table to calculate the average cost and the COGS using AVERAGE. | | | | | | | | For Step 7 - Calculate the value of the remaining Inventory. | | | | | | | | | | | | | | | | | | | | | | | | | | 2014 | | | | | | | | | | | Revenue | | | | | | | | | | | | | | | Tech Master Inc | FIFO | | | | | | | | | Income Statement | | | | | | | | | | For year ended 31 December 2014 | | | | | | | | | | | | | | | | | | | | | Revenue | | | | | | | | | | | Cost of Goods Sold | | | | | | | | | | | Gross Margin | #VALUE! | | | | | | | | | | Depreciation Expense | | $300,000.00 | | | | | | | | | Salaries Expense | | $1,200,000.00 | | | | | | | | | Interest Expense | | $4,000.00 | | | | | | | | | Advertising Expense | | $75.00 | | | | | | | | | Total Operating Expenses | $1,504,075.00 | | | | | | | | | | Earnings before Income tax | #VALUE! | | | | | | | | | | Tax | #VALUE! | | | | | | | | | | Net Income | #VALUE! | | | | | | | | | | | | | | | | | | | | | EPS = | #VALUE! | | | | | Inventory carryover and Purchases Table | | | | | | | | | | | | | Date | Units | Purchase | Cost in | Value of total | Tech Master Inc | LIFO | | | | | Purchased/carryover | Price | Dollars | Inventory | Income Statement | | | | | Carried From 2013 | 4000 | $45.00 | | | For year ended 31 December 2014 | | | | | 1-Jan | 500 | | | | | | | | | | 15-Jan | 500 | | | | Revenue | | | | | | 1-Feb | 500 | | | | Cost of Goods Sold | | | | | | 15-Feb | 500 | | | | Gross Margin | #VALUE! | | | | | 1-Mar | 500 | | | | Depreciation Expense | | $300,000.00 | | | | 15-Mar | 500 | | | | Salaries Expense | | $1,200,000.00 | | | | 1-Apr | 500 | | | | Interest Expense | | $4,000.00 | | | | 15-Apr | 500 | | | | Advertising Expense | | $75.00 | | | | 1-May | 500 | | | | Total Operating Expenses | $1,504,075.00 | | | | | 15-May | 500 | | | | Earnings before Income tax | #VALUE! | | | | | 1-Jun | 500 | | | | Tax | #VALUE! | | | | | 15-Jun | 500 | | | | Net Income | #VALUE! | | | | | 1-Jul | 500 | | | | | | | | | | 15-Jul | 500 | | | | EPS= | #VALUE! | | | | | 1-Aug | 500 | | | | | | | | | | 15-Aug | 500 | | | | | | | | | | 1-Sep | 500 | | | | | | | | | | 15-Sep | 500 | | | | Tech Master Inc | AVERAGE | | | | 1-Oct | 500 | | | | Income Statement | | | | | | 15-Oct | 500 | | | | For year ended 31 December 2014 | | | | | 1-Nov | 500 | | | | | | | | | | 15-Nov | 500 | | | | Revenue | | | | | | 1-Dec | 500 | | | | Cost of Goods Sold | | | | | | 15-Dec | 500 | | | | Gross Margin | #VALUE! | | | | | | | | | | Depreciation Expense | | $300,000.00 | | | | | | | | | Salaries Expense | | $1,200,000.00 | | | | | | | | | Interest Expense | | $4,000.00 | | | | | | | | | Advertising Expense | | $75.00 | | | | Total Units | | Goods | | | Total Operating Expenses | $1,504,075.00 | | | | | Available for | | available | | | Earnings before Income tax | #VALUE! | | | | | Sale | | for sale | | | Tax | #VALUE! | | | | | | | | | | Net Income | #VALUE! | | | | | | | | | | | | | | | | | | | | | EPS= | #VALUE! | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Tech Master Inc. | FIFO | | | | | | | | | | | | | | | | | As of 31 Dec 2014 | | | | | | | | | | | | | | | | | | | | | | Current Liabilities: | | | | | | | | | | | Accounts Payable | 4,590.00 | | | | | | | | | | Total Current Liabilities | 4,590.00 | | | | | | | | | | | | | | | | | | | | 2,777,793.25 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Notes Payable | 4,000.00 | | | | | | | | | | Total Liabilities | | 8,590.00 | | | | | | | | | | | | | | | | | | | | Shareholders' Equity | | | | | | | | | | 8,336.00 | Common stock | 10,000.00 | | | | | | | | | | Retained Earnings | 0.00 | | | | | | | | | 812.00 | Total shareholders' equity | | 10,000.00 | | | Question : What effect does the Inventory method have on the three | | | | | | | Balance sheets here? | | | | | | | Answer: | | | | | | | | $2,786,941.25 | Total Liab and Shareholders' Equity | | $18,590.00 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Tech Master Inc. | LIFO | | | | | | | | | | | | | As of 31 Dec 2014 | | | | | | | | | | | | | | | | | | | | | | Current Liabilities: | | | | | | | | | | | Accounts Payable | 4,590.00 | | | | | | | | | | Total Current Liabilities | 4,590.00 | | | | | | | | | | | | | | | | | | | | 3,005,293.25 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Notes Payable | 4,000.00 | | | | | | | | | | Total Liabilities | | 8,590.00 | | | | | | | | | | | | | | | | | | | | Shareholders' Equity | | | | | | | | | | 8,336.00 | Common stock | 10,000.00 | | | | | | | | | | Retained Earnings | 0.00 | | | | | | | | | 812.00 | Total shareholders' equity | | 10,000.00 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | $3,014,441.25 | Total Liab and Shareholders' Equity | | $18,590.00 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Tech Master Inc. | AVERAGE | | | | | | | | | | | | | | | | | As of 31 Dec 2014 | | | | | | | | | | | | | | | | | | | | | | Current Liabilities: | | | | | | | | | | | Accounts Payable | 4,590.00 | | | | | | | | | | Total Current Liabilities | 4,590.00 | | | | | | | | | | | | | | | | | | | | 2,876,668.25 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Notes Payable | 4,000.00 | | | | | | | | | | Total Liabilities | | 8,590.00 | | | | | | | | | | | | | | | | | | | | Shareholders' Equity | | | | | | | | | | 8,336.00 | Common stock | 10,000.00 | | | | | | | | | | Retained Earnings | 0.00 | | | | | | | | | 812.00 | Total shareholders' equity | | 10,000.00 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | $2,885,816.25 | Total Liab and Shareholders' Equity | | $18,590.00 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Depreciation Method Analysis | | | | | Instructions: Complete this chart by using a starting value of $3,000,000. | | | | | Salvage value is $400,000 and asset will last 20 years. | | | | | Calculate the Straight line and Declining Balance method for each year. | | | | | | | | | | | | | | | | | | Tech Master Inc. | Declining Balance method | Straight line depreciation | Year | Start year | Depreciation | End Year | Year | Start year | Depreciation | End Year | 1 | $3,000,000.00 | | | 1 | $3,000,000.00 | | #VALUE! | 2 | | | | 2 | | | | 3 | | | | 3 | | | | 4 | | | | 4 | | | | 5 | | | | 5 | | | | 6 | | | | 6 | | | | 7 | | | | 7 | | | | 8 | | | | 8 | | | | 9 | | | | 9 | | | | 10 | | | | 10 | | | | 11 | | | | 11 | | | | 12 | | | | 12 | | | | 13 | | | | 13 | | | | 14 | | | | 14 | | | | 15 | | | | 15 | | | | 16 | | | | 16 | | | | 17 | | | | 17 | | | | 18 | | | | 18 | | | | 19 | | | | 19 | | | | 20 | | | | 20 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Question : What effect does the Depreciation Method have on the Depreciation Expense | | | | and the value of the asset and accumulated Depreciation? | | | | Answer: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Question : What effect would the Depreciation Method have the Net Income of this | | | | Firm in year 10? | | | | Answer: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | BEP = FC / (Sales price - variable cost) | | | | | | | | | (BEP) Break Even Point analysis | | FC = Fixed cost | | | | | | | | | | | Instructions: Complete this project by using BEP formula TC = FC + (VC X # of units) | | VC = Variable cost | | | Question : What is the break even point for each option? | | | | Fill out the chart below and answer the question under the chart. | | Units = the number sold | | | | | | | Fixed costs Option A = 3,000,000, option B = 0 and Option C = 600,000 | | TC = Total cost | | | | Answer: | | | | Variable costs Option A = $55, Option B = $255, Option C = $155 | | | | | | | | | | Start units with 100 then add 100 for each line (eg. 100, 200, 300 etc to 30,000 units) |
| | | | | | | | | | Sales price = $ 500 | | | | | | | | | | | | | | | | | | | | | | Units | Option A | Option B | Option C | | | | | | | | | | 100 | | | | | | | | | | | | | 200 | | | | | | | | | | | | | 300 | | | | | | | | | | | | | | | | | 400 | | | | | | | | | | | | | | | | | 500 | | | | | | | | | | Question : What is the lowest total cost option for 5800 units? 6000 units? | | | 600 | | | | | | | | | | How about 23,500 units and 24,500 units? | | | 700 | | | | | | | | | | Answer: | | | 800 | | | | | | | | | | | | | 900 | | | | | | | | | | | | | 1000 | | | | | | | | | | | | | 1100 | | | | | | | | | | | | | 1200 | | | | | | | | | | | | | 1300 | | | | | | | | | | | | | 1400 | | | | | | | | | | | | | 1500 | | | | | | | | | | | | | | | | | 1600 | | | | | | | | | | | | | | | | | 1700 | | | | | | | | | | | | | | | | | 1800 | | | | | | | | | | | | | | | | | 1900 | | | | | | | | | | | | | | | | | 2000 | | | | | | | | | | | | | | | | | 2100 | | | | | | | | | | | | | | | | | 2200 | | | | | | | | | | | | | | | | | 2300 | | | | | | | | | | | | | | | | | 2400 | | | | | | | | | | | | | | | | | 2500 | | | | | | | | | | | | | | | | | 2600 | | | | | | | | | | | | | | | | | 2700 | | | | | | | | | | | | | | | | | 2800 | | | | | | | | | | | | | | | | | 2900 | | | | | | | | | | | | | | | | | 3000 | | | | | | | | | | | | | | | | | 3100 | | | | | | | | | | | | | | | | | 3200 | | | | | | | | | | | | | | | | | 3300 | | | | | | | | | | | | | | | | | 3400 | | | | | | | | | | | | | | | | | 3500 | | | | | | | | | | | | | | | | | 3600 | | | | | | | | | | | | | | | | | 3700 | | | | | | | | | | | | | | | | | 3800 | | | | | | | | | | | | | | | | | 3900 | | | | | | | | | | | | | | | | | 4000 | | | | | | | | | | | | | | | | | 4100 | | | | | | | | | | | | | | | | | 4200 | | | | | | | | | | | | | | | | | 4300 | | | | | | | | | | | | | | | | | 4400 | | | | | | | | | | | | | | | | | 4500 | | | | | | | | | | | | | | | | | 4600 | | | | | | | | | | | | | | | | | | | |
| | | | | | | | |