FINANCE case study

FIN 5130 Instructor: Dr. Palkar Mini - Ca se 2 Page 1 of 1 Late submissions will not be accepted so please plan accordingly. You need to show your working notes for credit. You must submit your work using excel files (with .xls or .xlsx for credit). You must upload your files on Blackboard under the Assignment Dro pbox for credit. This assignment will require you to prepare the cash budget and determine the cash surplus and shortage each month. The management estimates total sales for the period January through July based on actual sales from the immediate past q uarter. The following assumptions are made: Sales: Past and Expected September $300,000 January $150,000 October $250,000 February $100,000 November $300,000 March $200,000 December $200,000 April $200,000 May $225,000 June $250,000 July $300,000 Historical Forecast a. 50 % of the Sales are collected immediately . 25 % of the Sales will be collected one month after the sale. 12 % will be collected two months after the sale. 8% w ill be collected three months after the sale. The remainder will be collected three months after the sale. Bad debts are insignificant. b. Purchases will be $80,000 in January, and will grow by 5% each month. The purchases will be paid in the same month. c. Wage s and salaries of $30,000 will be paid each month. d. Rent paid wi ll be $ 1 5 ,000 per month . e. Interest expense is based on bonds valued $400,000. The annual coupon rate on these bonds is 8%. Interest will be paid every six months, at the end of June and December. f. A tax prepayment of $ 45 ,000 is paid in April. g. Machinery worth $ 1 5 0 ,000 will be purchased in February . h. Cash on hand on January 1 st will amount to $ 1 50 ,000. i. A minimum cash balance of $ 1 5 0,000 each month (i.e., target cash balance