Please i need help with my case.

be carefully with the details

Mini-case 2

FIN 5130 Instructor: Dr. Palkar Mini-Case 2

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 Dropbox 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 quarter. The following assumptions are made:

Sales: Past and Expected

Historical Forecast

October $300,000 November $350,000 December $400,000

January February March April May June July

$150,000 $200,000 $200,000 $300,000 $250,000 $200,000 $300,000

1. 25% of the Sales will be collected in the same month of the Sale. 60% of the Sales will be collected one month following the sale. 10% will be collected two months following the sale. The remainder will be collected three months following the sale. Bad debts are insignificant.
2. Purchases are 80% of sales and are paid in the same month.
3. Wages and salaries are as follows: Wages and salaries are paid in the same month.
4. Rent is $10,000 per month. Rent is paid in the same month.
5. The company issued $500,000 bonds, paying annual coupon rate of 5%. Interest on these bonds is paid at the end of each calendar quarter. Hint: Firsts, calculate the coupon interest amount for each quarter. Then, this quarterly interest amount will be paid at the end of each calendar quarter.
6. A tax prepayment of $75,000 is paid in April.
7. Machinery worth $35,000 will be purchased in March.
8. Cash on hand on January 1st will amount to $100,000 and a minimum cash balance of $100,000 each month will be maintained throughout the period.
9. Prepare cash budget from January to June.
10. Determine the cash surplus and shortages for each month from January to June

January February March April May June

$30,000 $40,000 $50,000 $50,000 $40,000 $35,000

Page 1 of 1

FIN 5130 Mini-Case 2 Instructor: Dr. Palkar 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 Dropbox 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 quarter. The following assumptions are made: Sales: Past and Expected Historical Forecast October $300,000 January November $350,000 February December $400,000 March April May June July $150,000 $200,000 $200,000 $300,000 $250,000 $200,000 $300,000 a. 25% of the Sales will be collected in the same month of the Sale. 60% of the Sales will be collected one month following the sale. 10% will be collected two months following the sale. The remainder will be collected three months following the sale. Bad debts are insignificant. b. Purchases are 80% of sales and are paid in the same month. c. Wages and salaries are as follows: January February March April May June d. e. f. g. h. $30,000 $40,000 $50,000 $50,000 $40,000 $35,000 Wages and salaries are paid in the same month. Rent is $10,000 per month. Rent is paid in the same month. The company issued $500,000 bonds, paying annual coupon rate of 5%. Interest on these bonds is paid at the end of each calendar quarter. Hint: Firsts, calculate the coupon interest amount for each quarter. Then, this quarterly interest amount will be paid at the end of each calendar quarter. A tax prepayment of $75,000 is paid in April. Machinery worth $35,000 will be purchased in March. Cash on hand on January 1st will amount to $100,000 and a minimum cash balance of $100,000 each month will be maintained throughout the period. 1. Prepare cash budget from January to June. 2. Determine the cash surplus and shortages for each month from January to June Page 1 of 1