Question

I want this solution with formuals on excel. Before copy pasting other answers, please keep in mind, SHOW EXCEL FORMULAS. I will make on excel from that formuals. Do not just solve and paste screen shot or image. Please do not waste my question by posting wrong solns. I will report u if your answer did not meet requirement I asked for.

The ABC Company has made the following monthly estimates of cash receipts and cash disbursements when preparing cash budgets for the next twelve months. ABC has beginning cash on hand of $20,000 and wants to maintain this minimum cash level throughout the next year.

Cash            Cash
Month        Receipts        Disbursements
January       $100,000        $130,000   
February    $90,000        $105,000
March           $95,000        $135,000
April            $100,000        $140,000
May            $135,000        $170,000
June           $165,000        $170,000
July            $200,000        $190,000   
August       $245,000        $190,000   
September $245,000        $150,000
October     $180,000        $120,000
November $140,000        $120,000   
December $100,000        $100,000

A.    Will ABC need to borrow cash during the next year?

B. If the company does need cash, in which month will it first need to borrow cash?

C.   What is the highest amount the company will have to borrow in total (the highest amount its loan balance will be during the year)?

D.   Will the company have paid back all of its borrowings by the end of the year?

Do calculations and answers in an Excel spreadsheet/ Excel spreadsheet should contain formulas that automatically calculate the draw down and pay back of the loan.

Answer 1

Answer:

A. Yes ABC need to borrow cash during the next year.

B. ABC Company need to borrow cash during its first month itself, i.e January

C. The Highest amount its loan balance during the year would be $165,000

D. Yes, the company have paid back all of its borrowings by the end of the year.

Workings:

	A	B	C	D	E	F	G	H	I
1	Month	Receipts	Disbursements	Opening Cash balance	(Cash Shortage) / Excess for the month	Amount borrowed to maintain minimum balance	Cumulative Borrowed amount	Amount repaid	Closing Borrowed amount
2	January	$ 100,000	$ 130,000	$                20,000	$                  -10,000	$                     30,000	$                      30,000	$                          -	$               30,000
3	February	$                  90,000	$ 105,000	$                20,000	$                      5,000	$                     15,000	$                      45,000	$                          -	$               45,000
4	March	$                  95,000	$ 135,000	$                20,000	$                  -20,000	$                     40,000	$                      85,000	$                          -	$               85,000
5	April	$ 100,000	$ 140,000	$                20,000	$                  -20,000	$                     40,000	$ 125,000	$                          -	$ 125,000
6	May	$ 135,000	$ 170,000	$                20,000	$                  -15,000	$                     35,000	$ 160,000	$                          -	$ 160,000
7	June	$ 165,000	$ 170,000	$                20,000	$                    15,000	$ 5,000	$ 165,000	$                          -	$ 165,000
8	July	$ 200,000	$ 190,000	$                20,000	$                    30,000	$                               -	$ 165,000	$                 10,000	$ 155,000
9	August	$ 245,000	$ 190,000	$                20,000	$                    75,000	$                               -	$ 155,000	$                 55,000	$ 100,000
10	September	$ 245,000	$ 150,000	$                20,000	$ 115,000	$                               -	$ 100,000	$                 95,000	$                 5,000
11	October	$ 180,000	$ 120,000	$                20,000	$                    80,000	$                               -	$                        5,000	$                   5,000	$                        -
12	November	$ 140,000	$ 120,000	$                75,000	$                    95,000	$                               -	$                               -	$                          -	$                        -
13	December	$ 100,000	$ 100,000	$                95,000	$                    95,000	$                               -	$                               -	$                          -	$                        -

Note : Kindly don't apply formula for first month January opening cash balance, it is provided information $20,000 and Cumulative borrowed amount for January month as $30,000

Excel formulas for February Month :

opening cash balance =+MAX(20000,E2)-H2+MAX(H2-G2,0)

(Cash Shortage) / Excess for the month =+D3+B3-C3

Amount borrowed to maintain minimum balance =MAX(20000-E3,0)

Cumulative Borrowed amount =MAX(+G2+F3-H2,0)

Amount repaid =+MAX(MIN(E3-20000,G3),0)

Closing Borrowed amount =+MAX(G3-H3,0)