Help Please

A strategy to reduce short-term borrowing is to find a best schedule for the capital expenditure, which is originally scheduled to spend in November 2017. Because the schedule is flexible, we may put this capital expenditure in any month from October 2017 to March 2018. Use the Excel Scenario Manager function to decide the best schedule for the capital expenditure if we want to keep the total amount of borrowing at the lowest level.

Cash Budget Beginning Cash Balance Add: Cash collection Total Cash available Less: Cash Disbursement Payment to inventory Wages Interest Payment Principal Payment Dividend Taxes Capital expenditure Total Cash disbursements Unadjusted cash flows Financing: Borrowing Repayment Total Financing Ending Cash Balance Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 38000 572200 610200 55000 588800 643800 55000 610000 665000 55000 626400 681400 55000 642800 697800 55000 660200 715200 389096 142000 15000 400384 143750 15000 25000 414800 149500 15000 425952 154500 15000 437104 158000 15000 25000 448936 162500 15000 28000 28000 25000 28000 574096 36104 300000 884134 240334 604300 60700 623452 57948 635104 62696 654436 60764 18896 295334 18896 55000 295334 55000 5700 -5700 55000 2948 -2948 55000 -7696 -7696 55000 5764 5764 55000 Cash Budget Beginning Cash Balance Add: Cash collection Total Cash available Less: Cash Disbursement Payment to inventory Wages Interest Payment Principal Payment Dividend Taxes Capital expenditure Total Cash disbursements Unadjusted cash flows Financing: Borrowing Repayment Total Financing Ending Cash Balance Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 38000 572200 610200 55000 588800 643800 55000 610000 665000 55000 626400 681400 55000 642800 697800 55000 660200 715200 389096 142000 15000 400384 143750 15000 25000 414800 149500 15000 425952 154500 15000 437104 158000 15000 25000 448936 162500 15000 28000 28000 25000 28000 574096 36104 300000 884134 240334 604300 60700 623452 57948 635104 62696 654436 60764 18896 295334 18896 55000 295334 55000 5700 -5700 55000 2948 -2948 55000 -7696 -7696 55000 5764 5764 55000