Question

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.

Answer 1

(a) Cash budget from June to October 2015:

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%

(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.