Question

The following table includes quarterly working capital levels for your firm for the next year.

	Quarters
?($000)????????	1	2	3	4
Cash	104	104	104	104
Accounts Receivable	198	97	106	610
Inventory	205	504	908	53
Accounts Payable	110	103	99	105

If you choose to enter the year with $397,000 total in cash and maintain a minimum cash balance of $104,000?, what is your maximum?short-term borrowing?

This is the answer but I need someone to show me how to calculate this in excel. I need details for how the "cash at the beginning of the qrt" was calculated.

You must find the total working capital for each quarter and then subtract the permanent working? capital, which is the smallest working capital of the four quarters. This will give you the temporary working capital for each quarter.

The temporary working capital for each quarter are shown? below:

($000)		Q1		Q2		Q3		Q4
Cash	$	104	$	104	$	104	$	104
Accounts receivable		198		97		106		610
Inventory		205		504		908		53
Accounts payable		110		103		99		105
NWC	$	397	$	602	$	1,019	$	662
- Permanent WC needs		(397)		(397)		(397)		(397)
Temporary WC needs	$	0	$	205	$	622	$	265

?Below, we determine the maximum amount of? short-term borrowing needed if the firm enters the year with $397,000 in cash.

($000)		Q1		Q2		Q3		Q4
Cash at beginning of quarter	$	397	$	397	$	192	$	104
Minimum cash balance		104		104		104		104
Temporary working capital needs		0		205		622		265
Change in NWC				205		417		(357)
Financing
Starting available excess cash	$	293	$	293	$	88	$	0
- Increase (decrease) in NWC		0		205		417		(357)
+ Increase (decrease) ST Debt		0		0		329		(329)
= Ending excess cash	$	293	$	88	$	0	$	28
Ending total cash balance		397		192		104		132
Total short term borrowing		0		0		329		0

Answer 1

Net working capital (NWC) = Current assets - Current liablities

or, NWC = (Cash + Accounts receivable + Inventory) - Accounts payable

Using the above formula you can compute the NWC -

?

Now, Permanent working capital is the minimum ?working capital in any quarter, i.e., $397 is the permanent working capital.

Next, Temporary working capital = NWC - Permanent working capital

Coming to the beginning of the year cash, it is given in the question as "if the firm enters the year with $397,000 cash". So, for the first quarter it is 397 (given in the question).

The beginning cash for each quarter = Ending cash of previous quarter

Change in NWC is the excess working capital required in each quarter in comparison to previous quarter.

Hope I cleared most of the issues. Let me known in case you require anything else.