Financial accounting, I need to use the case study to complete the accounting ledger.

ACCT 321 Intermediate Accounting for Finance Working Capital Case Study [30 points] Your job is to make ABC cash flow in years 2 and 3 (positive ending cash balances); which means you must balance ABC's cash conversion cycle days with ABC's increase in sales volume for years 2 and 3. YOU MUST DETERMINE ABC'S SALES for years 2 and 3 and the RELEVANT days in accounts receivables, days in inventory and days in accounts payable in order for ABC to cash flow. You will probably have to try several different combinations until ABC cash flows. Other than that, use the other information given in the case below. ABC Company is a start-up medical equipment manufacturing company. ABC developed a blood testing device that accurately diagnoses the Ebola virus in 15 minutes or less. The financing of ABC consisted of founders' equity investment totaling $369,074 and a $4,000,000 bank loan. In year 1 ABC generated a small net profit of $4,718 on total sales of $3,000,000, a gross margin of 35% and operating expenses of 25%. Also in year 1 ABC generated a cash deficiency totaling $4,369,074, which means they spent all of their financing in year 1. ABC's year 1 cash conversion cycle totaled 51 days, which consisted of days in accounts receivable of 45 days, days in inventory of 51 days and days in accounts payable of 45 days. Unfortunately, the founders have no more cash to invest in the company and the company does not qualify for additional debt financing. ABC believes it can reasonably achieve a 37% gross margin for years 2 and 3 and reduce operating expenses to 19% and 14% of sales for years 2 and 3, respectively. Furthermore, in order to increase sales ABC must invest $75,000 and $950,000 in capital expenditures in years 2 and 3, respectively. Also, ABC must pay its debt principal and interest obligations in years 2 and 3. Attached are schedules of ABC's additional information, balance sheets, income statements and cash flows. Based on the given information you must determine ABC's increase in sales and days in accounts receivable, days in inventory and days in accounts payable. Based on those days you must also compute ABC's accounts receivable, inventory and accounts payable for years 2 and 3 in order to compute ABC's cash flows. Additionally, based on your results, you must complete ABC's balance sheets, income statements and cash flows for years 2 and 3. I recommend you begin with the attached excel spreadsheets, but develop the appropriate formulas to automatically change the balance sheet, income statement and cash flows when you change your inputs. For example, you should design your spreadsheet so that when you change days, excel will compute accounts receivable, inventory and accounts payable on the balance sheet and cash flows. Furthermore, I suggest you design your spreadsheet so that when you change the percentage increase in sales it will also automatically update the sales line in the income statement and net income or loss line on the cash flows (this is what we did in our entire semester balance sheet, income statement and statement of cash flow assignments earlier in the semester). The attached schedules can be used as a guide to design your spreadsheets. No handwritten answers will be accepted. Working Capital Case Study Additional Information Line 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Information Days in accounts receivable Days in accounts payable Days in inventory Sales change Operating expenses Margin Income tax rate Days in year Property plant and equipment gross addition Financing cost Initial debt Debt amortization (months) Interest rate Monthly payment ACCT 321 Intermediate Accounting for Finance Year 1 Year 2 Year 3 45 45 51 100.00% 25% 35% 40% 365 $ 3,500,000 $ 100,000 $ 4,000,000 19% 37% 40% 365 $ 75,000 120 6.75% $ (45,929.65) Page 1 of 4 14% 37% 40% 365 $ 950,000 Working Capital Case Study Balance Sheets Line 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Balance Sheets Assets Cash Accounts receivable Inventory Prepaid expenses Current assets Property, plant and equipment, net of depreciation Other assets Financing costs, net of amortization Total assets Liabilities Accounts payable Accrued expenses Current portion of longterm debt Income taxes payable (receivable) Current liabilities ACCT 321 Intermediate Accounting for Finance Year 1 $ $ 369,863 $ 272,466 $ 15,000 $ 657,329 $ 3,460,000 $ 150,000 $ 90,000 $ 4,357,329 $ 240,411 $ 30,000 $ 310,213 $ 3,145 $ 583,769 Year 2 Year 3 $ 15,000 $ 15,000 $ 3,490,000 $ 165,000 $ 80,000 $ 4,365,000 $ 140,000 $ 70,000 $ 47,000 $ 331,812 $ 52,000 $ 354,916 Longterm debt Total liabilities Shareholders' equity Common stock (10,000 shares outstanding; $1 par value) Additional paidin capital Retained earnings (deficit) Total shareholders' equity $ 3,399,768 $ 3,983,537 $ 3,067,956 $ 2,713,040 $ 10,000 $ 359,074 $ 4,718 $ 373,792 $ 10,000 $ 10,000 Total liabilities and shareholders' equity $ 4,357,329 Page 2 of 4 Working Capital Case Study Income Statements Line 1 2 3 4 5 6 7 8 9 10 11 12 ACCT 321 Intermediate Accounting for Finance Income Statements Sales Cost of sales Gross profit Year 1 $ 3,000,000 $ 1,950,000 $ 1,050,000 Operating expenses Amortization Depreciation Income (loss) from operations Interest expense Other income (expense) Income (loss) before income taxes Income taxes or (refund) Net income (loss) $ 750,000 $ 10,000 $ 40,000 $ 250,000 $ 261,137 $ 19,000 $ 7,863 $ 3,145 $ 4,718 Page 3 of 4 Year 2 Year 3 $ 10,000 $ 45,000 $ 10,000 $ 75,000 $ 240,943 $ 8,000 $ 219,344 $ (17,000) Working Capital Case Study Cash Flows Line 1 2 3 4 ACCT 321 Intermediate Accounting for Finance 10 Cash Flows Net income (loss) Amortization Depreciation Cash from (used) in operations CAPEX Property plant and equipment Other assets Financing costs Total CAPEX Increase (decrease) in noncash and nondebt related working capital [schedule below] Cash flow (deficiency) [lines 4,8,9] 11 12 Operating cash cycle Cash conversion cycle 13 14 15 16 Funding of deficiency Longterm debt Common stock Additional paidin capital Totaling funding $ 4,000,000 $ 10,000 $ 359,074 $ 4,369,074 17 18 19 20 21 Ending cash Excess funding [line 16 + line 10] Payment of longterm debt Net cash (deficiency) Beginning cash Ending cash $ 290,019 $ (290,019) $ $ $ 22 23 24 25 26 27 28 Working capital schedule Current assets Less cash Noncash current assets Current liabilities Less current portion of longterm debt Nondebt current liabilities Increase (decrease) in working capital Year 1 $ 657,329 $ $ 657,329 $ 583,769 $ 310,213 $ 273,556 $ 383,773 5 6 7 8 9 Year 1 $ 4,718 $ 10,000 $ 40,000 $ 54,718 $ 3,500,000 $ 150,000 $ 100,000 $ 3,750,000 Year 2 Year 3 $ 10,000 $ 45,000 $ 10,000 $ 75,000 $ 75,000 $ 15,000 $ $ 90,000 $ 950,000 $ (25,000) $ $ 925,000 $ (310,213) $ (331,812) $ 383,773 $ (4,079,055) 96 51 $ Year 2 Year 2 Year 3 A decrease () in working capital from the prior year is a source of cash and an increase () in working capital from the prior year is a use of cash. This is due to the combined changes in the current assets and current liabilities that comprise working capital see the note on the statement of changes in cash flow for a fuller explanation. Page 4 of 4 Year 3 Line 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Information Days in accounts receivable Days in accounts payable Days in inventory Sales change Operating expenses Margin Income tax rate Days in year Year 1 Year 2 Year 3 45 45 51 100.00% 25% 35% 40% 365 Property plant and equipment gross addition Financing cost Initial debt $ 3,500,000 $ 100,000 $ 4,000,000 Debt amortization (months) Interest rate Monthly payment 120 6.75% $ (45,929.65) 19% 37% 40% 365 14% 37% 40% 365 $ 75,000 $ 950,000 Line 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Balance Sheets Assets Cash Accounts receivable Inventory Prepaid expenses Current assets Year 1 $ $ $ $ $ 369,863 272,466 15,000 657,329 $ $ $ $ 3,460,000 150,000 90,000 4,357,329 $ $ $ $ $ 240,411 30,000 310,213 3,145 583,769 $ $ Common stock (10,000 shares outstanding; $1 par value) Additional paid-in capital Retained earnings (deficit) Total shareholders' equity Total liabilities and shareholders' equity Property, plant and equipment, net of depreciation Other assets Financing costs, net of amortization Total assets Liabilities Accounts payable Accrued expenses Current portion of long-term debt Income taxes payable (receivable) Current liabilities Long-term debt Total liabilities Shareholders' equity Year 2 Year 3 $ 15,000 ### $ $ $ 3,490,000 165,000 80,000 $ 4,365,000 $ 140,000 $ 70,000 $ $ 47,000 331,812 3,399,768 3,983,537 $ 3,067,956 $ 2,713,040 $ $ $ $ 10,000 359,074 4,718 373,792 $ 10,000 ### $ 4,357,329 $ $ 52,000 354,916 Line 1 2 3 Income Statements Sales Cost of sales Gross profit Year 1 $ 3,000,000 $ 1,950,000 $ 1,050,000 4 5 6 7 8 9 Operating expenses Amortization Depreciation Income (loss) from operations Interest expense Other income (expense) $ $ $ $ $ $ 750,000 10,000 40,000 250,000 261,137 19,000 Income (loss) before income taxes Income taxes or (refund) Net income (loss) $ $ $ 7,863 3,145 4,718 10 11 12 Year 2 Year 3 $ $ 10,000 45,000 $ $ 10,000 75,000 $ $ 240,943 8,000 $ $ 219,344 (17,000) Line 1 2 3 4 5 6 7 8 9 Cash Flows Net income (loss) Amortization Depreciation Cash from (used) in operations CAPEX Property plant and equipment Other assets Financing costs Total CAPEX Year 1 $ 4,718 $ 10,000 $ 40,000 $ 54,718 $ 3,500,000 $ 150,000 $ 100,000 $ 3,750,000 10 Increase (decrease) in non-cash and non-debt related working capital [schedule below] Cash flow (deficiency) [lines 4,8,9] 11 12 Operating cash cycle Cash conversion cycle 13 14 15 16 Funding of deficiency Long-term debt Common stock Additional paid-in capital Totaling funding Ending cash Excess funding [line 16 + line 10] Payment of long-term debt Net cash (deficiency) Beginning cash Ending cash $ $ $ $ $ 290,019 (290,019) - 22 23 24 25 26 27 28 Working capital schedule Current assets Less cash Non-cash current assets Current liabilities Less current portion of long-term debt Non-debt current liabilities Increase (decrease) in working capital $ $ $ $ $ $ $ Year 1 657,329 657,329 583,769 310,213 273,556 383,773 Year 3 $ ### 45,000 $ $ 10,000 75,000 $ $ $ $ 75,000 15,000 90,000 $ $ $ $ 950,000 (25,000) 925,000 $ (310,213) $ (331,812) $ 4,000,000 $ 10,000 $ 359,074 $ 4,369,074 17 18 19 20 21 Year 2 $ 383,773 $ (4,079,055) 96 51 ### Year 2 Year 2 Year 3 A decrease () in working capital from the prior year is a source of cash and an increase () in working capital from the prior year is a use of cash. This is due to the combined changes in the current assets and current liabilities that comprise working capital - see the note on the statement of changes in cash flow for a fuller explanation. Year 3