Reference no: EM132703668

The finance manager wants to create cash budget for the July, 2020 through December, 2020 period. The finance manager has received the following information from the marketing and operations managers:

• The Sales were $140,000 in January, 2020 and then the sales grew by 2% each month in the first three months (i.e., from February to April 2020) and by 5% in the next two months (i.e., in May and June 2020). The sales are expected to grow by 1% each month thereafter.
• 45% of the Sales are collected in the same month. 30% of the sales are collected in the following month. 24% of the sales are collected after two months and the remainder are not collected.
• The Purchases are 80% 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 October 2020. 50% of the amount due will be paid immediately and the balance will be paid in November, 2020.
• The company had previously taken a loan of $200,000. The annual interest rate on the loan amount is 4%. The interest is paid once a year in 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).

1. Determine the total cash inflows for each month from July, 2020 to December, 2020. Show your work in Excel.

2. Determine the total cash outflows for each month from July, 2020 to December, 2020. Show your work in Excel.

3. Determine the expected change in cash for each month from July, 2020 to December, 2020. Show your work in Excel.

4. Describe in some of the short-term borrowing options that the company may adopt.