Corning Incorporated sells its product for $24 per unit. Its actual and projected sales follow: Units Dollars January (actual) 18,500 $444,000 February (actual) 23,000 552,000 March (budgeted) 19,800 475,200 April (budgeted) 18,950 454,800 May (budgeted) 22,000 528,000 Here is added information about Corning’s operations: All sales are on credit. Recent experience show that 35% of sales are collected in the month of the sale, 45% in the month following the sale, 17% in the second month after the sale, and 3% prove to be uncollectible. The product’s purchase price is $15 per unit. All payments are payable within 21 days. Thus 30% of purchases in any given month are paid for in that month, with the remaining 70% paid for in the following month. The company has a policy to maintain an ending inventory of 20% of the next month’s projected sales plus a safety stock of 100 units. The January 31 and February 28 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $2,456,000 and are paid evenly throughout the year in cash. The company’s minimum cash balance at month-end is $50,000. This minimum is maintained, if necessary, by borrowing cash from the bank. If the balance exceeds $50,000, the company repays as much of the loan balance as it can without going below the minimum. This loan carries an annual interest rate of 6% (0.5% per month). At February 28, the loan balance is $14,000, and the company’s cash balance is $50,000. In excel prepare a cash budget for March and April, including any loan activity and interest expense. Compute the loan balance at the end of each month. Can you show your workings?
Corning Incorporated sells its product for $24 per unit. Its actual and projected sales follow:
|
|
Units |
Dollars |
|
January (actual) |
18,500 |
$444,000 |
|
February (actual) |
23,000 |
552,000 |
|
March (budgeted) |
19,800 |
475,200 |
|
April (budgeted) |
18,950 |
454,800 |
|
May (budgeted) |
22,000 |
528,000 |
Here is added information about Corning’s operations:
All sales are on credit. Recent experience show that 35% of sales are collected in the month of the sale, 45% in the month following the sale, 17% in the second month after the sale, and 3% prove to be uncollectible. The product’s purchase price is $15 per unit. All payments are payable within 21 days. Thus 30% of purchases in any given month are paid for in that month, with the remaining 70% paid for in the following month. The company has a policy to maintain an ending inventory of 20% of the next month’s projected sales plus a safety stock of 100 units. The January 31 and February 28 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $2,456,000 and are paid evenly throughout the year in cash. The company’s minimum cash balance at month-end is $50,000. This minimum is maintained, if necessary, by borrowing cash from the bank. If the balance exceeds $50,000, the company repays as much of the loan balance as it can without going below the minimum. This loan carries an annual interest rate of 6% (0.5% per month). At February 28, the loan balance is $14,000, and the company’s cash balance is $50,000.
In excel prepare a
Can you show your workings?
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
