Assignment: Intermediate Accounting for Finance Working Capital Case Study
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.
Attachment:- Working_Capital_Case.pdf