ABC has cash totaling $$3,525$ at the end of year $1.$ ABC needs to generate additional cash and

your job is to make ABC cash flow in both years $2$ and $3($positive ending cash balances on the

balance sheets$)$; using the cash $3$ factors $($Volume$,$ Margin and Working Capital$).$ YOU MUST

DETERMINE ABC$$S SALES INCREASE $($Volume$)$ for years $2$ and $3$ and the RELEVANT

days in accounts receivables, days in inventory and days in accounts payable $($Working

Capital$)$ in order for ABC to cash flow. You must use the Margin Information given to you in

the case study $($gross profit $\%$ and operating expense $\%).$ You will need to try several different

combinations until ABC cash flows in both years $1$ and $2.$ Other than that, use the other

information given in the case below.

ABC Company is a start$-$up brain and neurological testing company. ABC developed a device

that eliminates $96\mathrm{.}25\%$ of the protein beta$-$amyloid, known as plaques, in the brain. The build up

of these brain plaques are suspected of causing Alzheimer's disease.

In year $1$ ABC initial financing consisted of founders$($shareholders$)$ equity investment totaling

$$369,074$ and a $$4,500,000$ bank loan, totaling $$4,869,074.$

In year $1$ ABC generated a small net profit of $$108$ on total sales of $$3,000,000,$ a gross margin

of $41\%$ and operating expenses of $30\%.$ At the end of year $1$ ABC's cash balance was $$3,525,$

which means they spent most of their initial financing in year $1.$ ABC$$s year $1$ cash conversion

cycle totaled $41$ days, which consisted of days in accounts receivable of $37$ days, days in

inventory of $49$ days and days in accounts payable of $45$ days. The founders $($shareholders$)$ do

not have any more cash to invest in the company, the company does not qualify for additional

debt financing and ABC cannot raise additional equity financing.

ABC believes it can reasonably achieve a $42\%$ 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

$($property$,$ plant and equipment$)$ in years $2$ and $3,$ respectively. Also, ABC must pay the principal

and interest payments on the original $$4,500,000$ debt obligation 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 annual sales increase,

days in accounts receivable, days in inventory and days in accounts payable for years $2$ and $3.$

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 schedule of 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 schedule of 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 schedule of 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$).$

Page $1$ of $2$

ACCT $321$ Intermediate Accounting for Finance

Instructions $-$ Working Capital Case Study $[30$ points$]$

Page $2$ of $2$

You are not required to prepare a statement of cash flows, but I recommend you prepare it

anyway to avoid circular excel references. In other words, the cash line on the balance sheets

should automatically populate with the ending cash line on the statement of cash flows. This way

the schedule of cash flows won$$t have circular references to the balance sheets.

I DO NOT WANT YOUR STATEMENT OF CASH FLOWS. I WANT THE BALANCE

SHEETS, INCOME STATEMENTS, SCHEDULE OF CASH FLOWS AND THE

ADDITIONAL $($INPUT$)$ SHEET. THE ENDING CASH BALANCE ON THE

SCHEDULE OF CASH FLOWS MUST AGREE WITH THE CASH LINE ON THE

BALANCE SHEETS.

No handwritten answers will be accepted.