Question

Prepare the cash budget and determine the cash inflows, cash outflows, and the expected change in cash each month.

The management estimates total sales for the period January, 2019 through June 2019 based on actual sales from the immediate past six months. The following assumptions are made:

 The Sales were $125,000 in July 2018 and then the sales grew by 8% each month for the first three months (i.e., August to October 2018) and by 6% for the next two months (i.e., November and December 2018). The sales are expected to grow by 3% each month thereafter.

 35% of the Sales are collected in the same month. 33% of the sales are collected in the following month. 31% of the sales are collected after two months and the remainder are not collected.

 The Purchases are 60% of each month’s sales and paid in the same month.

 Wages and Salaries are $25,000 each month and paid in the same month.

 Other administrative expenses are $15,000 and paid in the same month.

 Depreciation expense is $5,000 each month.

 An electrical device worth $150,000 will be purchased on credit in April 2019. $75,000 will be paid immediately and the balance will be paid in May, 2019.

 The company had previously taken a loan of $125,000. The annual interest rate is 5%. The interest is paid twice a year in June and December each year. Assume that no principal repayments are made in this period, only interest payments are made.

 The company pays rent of $2,500 quarterly (in March, June, September, and December each year).

Questions:

1. Determine the total cash inflows, the total cash outflows, and the expected change in cash for each month from January to July, 2019. Show your work in excel using excel functions. Based the findings, explain in your own words whether the company should borrow/invest and how much and in which months. [4.50 points]

2. Describe in your own words some of the credit monitoring techniques that the company may adopt. Please write your answer in another tab on the same excel file, no need to upload another Word Document file or type your answer elsewhere. [0.50 points]

Answer 1

	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19
expected Sales = current year sales*(1+growth rate)	125000	125000*1.08	125000*1.08^2	125000*1.08^3	157464*1.06^1	157464*1.06^2	176926.6*1.03^1	176926.6*1.03^2	176926.6*1.03^3	176926.6*1.03^4	176926.6*1.03^5	176926.6*1.03^6	176926.6*1.03^7
expected sales	125000	135000	145800	157464	166911.84	176926.6	182234.4	187701.4	193332.5	199132.4	205106.4	211259.6	217597.4
	Month	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19
	sales for month	166911.84	176926.55	182234.4	187701.4299	193332.5	199132.4	205106.4	211259.6	217597.4
	total cash collection
	35% in the month of sale = 35% of month sales			63782.04	65695.50048	67666.37	69696.36	71787.25	73940.86	76159.09
	33% in the following month of sales			58385.76	60137.35134	61941.47	63799.72	65713.71	67685.12	69715.67
	31% 2 month after sales			51742.67	54847.23062	56492.66	58187.44	59933.07	61731.06	63582.99
	total cash collection			173910.5	180680.0824	186100.5	191683.5	197434	203357	209457.8
	total cash outflow
	purchase-60% of sales			109340.6	112620.858	115999.5	119479.5	123063.9	126755.8	130558.4
	wages and salaries			25000	25000	25000	25000	25000	25000	25000
	other administrative expenses			15000	15000	15000	15000	15000	15000	15000
	payment towards electrical device						75000	75000
	payment towards interest on loan = 125000*5%*1/2								3125
	rent payment					2500			2500
	total cash outflow			149340.6	152620.858	158499.5	234479.5	238063.9	172380.8	170558.4
	excess or shortage of cash = total cash collection-total cash outflow			24569.83	28059.22448	27601.02	-42796	-40629.8	30976.27	38899.31
	change in cash for each month			Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19
	total cash collection			173910.5	180680.0824	186100.5	191683.5	197434	203357	209457.8
	total cash outflow			149340.6	152620.858	158499.5	234479.5	238063.9	172380.8	170558.4
	excess cash amount to be borrowed or invested			24569.83	28059.22448	27601.02	-42796	-40629.8	30976.27	38899.31
	Invest or Borrow			Invest	Invest	Invest	Borrow	Borrow	Invest	Invest
	company should Investment in the month of January, February,March, June and july while will borrow in the month of April and May as cash outflow is more in comparison to cash collection.Note It is assumed that excess or shortage of funds are invested or borrowed at the end of the month as there is no specification of minimum cash balance so entire amount of excess cash are invested and shortage are borrowed and keep the minimum cash balance as nil
2-	some of the credit monitoring techniques which are followed by companies are (1) monthly cash budget (2) Inspection of stock and book debt statement (3) aging schedule of receivables (4) periodically monitoring reports (5) annual review of accounts