[excel]Accounting for Liabilities, Equity and Expense

Part I is already have solution,

please check the ‘Part 1_Solution.xlsx’

What you need to finish is Part II.

Part I: Accounting for Revenue, Expense, and Assets

CNE Inc. sells computer network equipment. The yearly part of 2012 turned out to be highly profitable for CNE with record breaking sales of 1,000 units from January through March. Exhibit A shows the breakout of the number of units sold to CNE’s primary customers including when each customer purchased product, when the customer received product, the number of units purchased and the price per unit. To help expedite sales CNE offered 2/10, n/30 credit terms to all of it’s customers.

• Acore paid for their purchases within 10 days of receiving their product.

• Xplex and Integrative Design paid cash for their purchase at the time they received the product.

• Digital Solutions paid 50% of its balance within 30 days. Unfortunately, in May you discovered that Digital Solutions went bankrupt and does not have the financial means to pay its remaining accounts receivable balance.

The total raw material and labor cost for manufacturing 1,000 units is $200,000 and $50,000 respectively. Exhibit B shows a breakout by month of the raw material cost and the number of units manufactured.
To remain competitive CNE needs to expand its output by automating its production process. Senior management is forecasting a major increase in demand for its product at 12,500 units per year over the next 7 years. To meet this demand CNE spent $500,000 for new robotic equipment in April and spent another $100,000 to have the equipment installed in May. The robotic equipment has a useful life up to 10 years and it can typically produce 100,000 units before the equipment breaks down and needs to be replaced. The salvage value of this equipment is estimated at $50,000.
Assumptions:
1. All purchases for raw material and capital equipment are paid for in cash at the time of purchase.
2. All units are manufactured in the same month of the raw material purchase.
3. Direct Labor is paid for in cash at the end of each month.
4. Assume 500 units were manufactured in May at a cost of $230 per unit (this includes direct labor)

5. COGS is valued using FIFO.

6. Beginning balance is given in “Begin Balance” file.
Your Task:

• Create CNE’s general journal for the months of January through May as it impacts revenue, expense and assets.

• Move the general journal into ledger account, and conduct trial balance.

• Conduct schedule for inventory and COGS

This is the part should be done ↓

Part II: Accounting for Liabilities, Equity and Expense

As the first half of 2012 came to a close CNE was in position to be a leader in the sale of high end computer networking equipment for broadcast media companies. As demand was forecasted to increase to 12,500 units per year over the next 7 years the company invested $600,000 in new equipment to increase the capacity of its manufacturing facility. It financed this new equipment purchase through a combination of debt and equity. In April, CNE received a $300,000 loan from a commercial bank at 7% interest. Starting on May 1st principal and interest payments of 3,500 are to be paid on a monthly basis over 10 years. Also in April, CNE issued $300,000 of common stock to a private venture capital firm. 50,000 shares were issued at a par value of $3 per share.
To support sales growth, CNE extended generous accounts receivable terms to its customers. As a result, CNE’s cash cycle lengthen where inventory and A/R days outstanding reached a combined 90 days in the first half of 2012. To generate more operating cash flow, the company decided to rely more heavily on accounts payable. In June of 2012 all purchases for raw materials were switched from a cash basis to A/P with 2/10, n/30 terms. Based on early payment discounts from its suppliers CNE adopted a policy of paying within the 10 day window. Figure 1 on the following page shows the schedule of the company’s raw material purchases by month and when CNE paid for each purchase.
As the second quarter of 2012 came to a close profits were at a record high. CNE declared a cash and stock dividend at the end of June. The cash dividend was set at $.25 cents per share and the stock dividend was set at 10% of total outstanding stock with a par value of $3 per share. Total outstanding stock at the end of June was 100,000 shares. The dividends were distributed in September. The stock was trading at $6 per share at the time the dividends were declared and distributed.
Assumptions:

1. For this exercise ignore calculating and journalizing the current portion of long-term debt.

2. CNE has a fiscal year end on September 30th.

3. Do not worry about journalizing accounting entries for fixed assets or depreciation expense on fixed assets. You will need to account for Cash and Inventory.

4. Beginning balance is given in “Begin Balance” file.

Your Task:

• Based on the information provided create CNE’s general journal for the months of April through September as it impacts expense, liabilities and equity.

• Conduct schedule for principle and interest calculation.

• Using the new journal entries to update the ledger and trial balance from part I. Then use the adjusted trial balance to conduct balance sheet and income statement.

• Using the ratios we learned from this class, and evaluate the financial performance of CNE. Do you think the company is doing well?
  
  

Figure I: