Homework Sourseplease make it 100 correct urgent task 1 Loblaw Manufacturin
please make it 100% correct... urgent task..
1. Loblaw Manufacturing has asked you to create a cash budget in order to determine its borrowing needs for the June to October period. You have gathered the following information.
Month Sales Other Payments
June 2015 $223,600 $104,000
July 184,600 97,500
August 157,300 91,000
September 120,900 65,000
October 98,800 58,500
November 105,300
April and May sales were $149,500 and $175,500, respectively. The firm collects 25% of its sales during the month, 60% the following month, and 15% two months after the sale. Each month it purchases inventory equal to 60% of the next month’s expected sales. The company pays for 40% of its inventory purchases in the same month and 60% in the following month. However, the firm’s suppliers give it a 2% discount if it pays during the same month as the purchase. A minimum cash balance of $25,000 must be maintained each month, and the firm pays 4% annually for short-term borrowing from its bank.
a) Create a cash budget for June to October 2015. The cash budget should account for short-term borrowing and payback of outstanding loans as well as the interest expense. The firm ended May with a $30,000 unadjusted cash balance.
b. Bob Loblaw, the president, is considering stretching out its inventory payments. He believes that it may be less expensive to borrow from suppliers than from the bank. He has asked you to use the Scenario Manager to see what the total interest cost for this time period would be if the company paid for 0%, 10%, 30%, or 40% of its inventory purchases in the same month. The remainder would be paid in the following month. Create a scenario summary, and describe whether the results support Bob’s beliefs.
Solution
(a) Cash budget from June to October 2015:
(b) Decrease in payment of inventory in the first month will reduce the 2% discount and will furthur increase the cumulative borrowing costing. This is bacuase there is a declining trend in the expected sales and hence purchases for the uncoming months.
| 2015 | Percentage | Apr | May | June | July | Aug | Sept | Oct | Nov |
| Sales | 149,500 | 175,500 | 223,600 | 184,600 | 157,300 | 120,900 | 98,800 | 105,300 | |
| Collections- | |||||||||
| % of current month | 25% | 55,900 | 46,150 | 39,325 | 30,225 | 24,700 | |||
| % of previous month | 60% | 105,300 | 134,160 | 110,760 | 94,380 | 72,540 | |||
| % od 2 previous month | 15% | 22,425 | 26,325 | 33,540 | 27,690 | 23,595 | |||
| Total Collections(a) | 183,625 | 206,635 | 183,625 | 152,295 | 120,835 | ||||
| Purchases | 60% | 105,300 | 134,160 | 110,760 | 94,380 | 72,540 | 59,280 | 63,180 | |
| Payments- | |||||||||
| % of current month | 40% | 44304 | 37752 | 29016 | 23712 | 25272 | |||
| %Disount on current month | -2% | -886.08 | -755.04 | -580.32 | -474.24 | -505.44 | |||
| % of previous month | 60% | 80496 | 66456 | 56628 | 43524 | 35568 | |||
| Purchase Payments(b) | 123,914 | 103,453 | 85,064 | 66,762 | 60,335 | ||||
| Other payments(c ) | 104000 | 97500 | 91000 | 65000 | 58500 | ||||
| Total Payments(d = b+c) | 227,914 | 200,953 | 176,064 | 131,762 | 118,835 | ||||
| Beginning Cash Balance(e ) | 25000 | 25,000 | 25,000 | 25,000 | 25,000 | ||||
| Cash from operations (f = a-d) | -44,289 | 5,682 | 7,561 | 20,533 | 2,000 | ||||
| Unadjusted Cash Balance(g = e+f) | -19,289 | 30,682 | 32,561 | 45,534 | 27,000 | ||||
| Minimum cash requirement | 25,000 | ||||||||
| Current Borrowing (h=g + 25000) | 44,289 | ||||||||
| Payback short term loan (i=g - 25000) | -5,682 | -7,561 | -20,534 | -2,000 | |||||
| Ending Cash Balance | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | ||||
| Cumulative borrowing (j = h+i) | 0 | 44,289 | 38,607 | 31,046 | 10,512 | 8,512 | |||
| Cumulative interest(k = j of previous year*0.33%) | 0 | 148 | 129 | 103 | 35 | ||||
| Borrowing rate (Annual) | 4% | ||||||||
| Borrowing rate (Monthly) | 0.33% |
