Question

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 $140,000 in July 2018 and then the sales grew by 2% each month in the first three months (i.e., from August to October 2018) and by 5% in the next two months (i.e., in November and December 2018). The sales are expected to grow by 1% 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 70% 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 $30,000 will be purchased in April 2019. 50% of the amount due 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 on the loan amount 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 $3,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 short-term borrowing options 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

	July	August	September	October	November	December
sales = Last month sales*(1+growth rate)	140000	142800	145656	148569.12	155997.576	163797.4548
	Jan	Feb	Mar	April	May	June
Forecasted sales = December sale*(1+growth rate)^n growth rate =1%	165435.4293	167089.7836	168760.6815	170448.2883	172152.7712	173874.2989
	Jan	Feb	Mar	Apr	May	Jun
beginning cash balance
Cash collection
In the month of sales-35% of sales	57902.40027	58481.42427	59066.23852	59656.9009	60253.46991	60856.00461
In the following month -33% of sales	54053.16008	54593.69168	55139.6286	55691.02489	56247.93514	56810.41449
After 2 month -31% of sales	48359.24856	50777.21099	51284.9831	51797.83293	52315.81126	52838.96937
total cash inflow	160314.8089	163852.3269	165490.8502	167145.7587	168817.2163	170505.3885
total cash outflow
Purchase of material-70% of month forecasted sales	115804.8005	116962.8485	118132.477	119313.8018	120506.9398	121712.0092
wages and salaries	25000	25000	25000	25000	25000	25000
other administrative expenses	15000	15000	15000	15000	15000	15000
payment for electric device	15000				15000
payment of interest						3125
payment of rent			3500			3500
total of cash outflow	170804.8005	156962.8485	161632.477	159313.8018	175506.9398	168337.0092
Excess or deficit of cash	-10489.99163	6889.478398	3858.373182	7831.956914	-6689.72352	2168.379248
expected change in cash	-10489.99163	-3600.513229	257.8599528	8089.816867	1400.09335	3568.472598
Borrow or Invest	Borrow	Borrow	Invest	Invest	Invest	Invest
company should borrow in the month of January and february (10489.99 & 3600.51 respectively) and should invest in the month of March,april, may & june (257.85,8089,1400 &3568 respectively)
2-	Company may borrow it from the short term sources of funds like bankoverdraft, cash credit, trade finance, by issuance of commercial papers, promissory notes etc.