Question

For this assignment you will utilize the excel template to create cash budgets using the example presented in the Chapter 16 PowerPoint. Your assignment will consist of the following:

1) You will create a sheet that prepares the monthly cash budget using the data below as well as the other data provided in the spreadsheet. (44.25 pts)

	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17
Anticipated level of sales	$15,000	$20,000	$30,000	$50,000	$40,000	$20,000	$10,000	$5,000	$15,000	$25,000	$40,000	$35,000	$15,000
Estimated labor and raw materials	$3,000	$25,000	$38,000	$30,000	$10,000	$5,000	$3,000	$12,000	$14,000	$31,000	$29,000	$14,000	$7,000

2) You will create a sheet that reflects the following changes: management believes that it can increase cash sales from 30% to 60% while reducing the desired level of cash to $4,000 for all months. (21.75 pts)

3) You will create a separate sheet to complete the following:

a) Provide an analysis of the results from the original cash budget created during step 1. The analysis will include a discussion of the anticipated sales in relation to the estimated labor and materials, as well as the overall month to month surplus cash or loan needed and what appears to be attributing to the surplus or need. The analysis should also include a discussion of the implications for the business as presented in the results.(6 pts)

b) If management increases cash sales from 30% to 60%, what type of investment policy does this action typically reflect? What reasons would management have for increasing the anticipated cash sales? Explain and support your response to each question. (4 pts)

c) Using the data, explain how the changes to cash sales and target cash balance would impact the need for external funds each month. What are the potential advantages and disadvantages of implementing the actions without adjusting overall sales? Your response should include a discussion of the goals of cash management, the goal of each action individually, as well as the combined effect on the external funds needed. (4 pts)

			Monthly Cash Budget Example
			Input Data
			Anticipated level of sales
			Estimated labor and raw materials
			Cash sales	30%
			Collections during month after sale	90%
			Collections during second month after sale	10%
			Other cash receipt - Treasuries (Jun-16)	$10,000
			Other cash receipt - Tax refund (Dec-16)	$1,000
			Target cash balance (May thru Jan)	$10,000
			Target cash balance (Feb thru April)	$6,000
			Interest, rent and administrative expenses	$3,000
			Other cash disbursement - income tax payments (Sep-16)	$2,500
			Other cash disbursement - retire debt issue (Aug-16)	$1,000
			Other cash disbursement - retire loan (Jan-17)	$25,000
			Cash on hand (May 1, 2016)	$12,000
			Prepare a monthly cash budget for the next thirteen months.
			Anticipated Sales
			Sales (gross)
			Cash sales
			Collections
			During 1st month after sale
			During 2nd month after sale
			Other cash receipts
			Total Cash Receipts
			Disbursements
			Variable: Labor and raw materials
			Fixed: inteerst, rent, and admin expenses
			Other cash disbursements
			Total cash disbursements
			Cash Surplus (or Loan Requirement)
			Cash gain (or loss) during the month
			Cash position at beginning of month
			Cash position at end of month
			Target cash balance

Answer 1

Solution Question No.1

Monthy cash flow from May 2016 to May 2017 May-17 Jul-16 Oct-16 Feb-17 Apr-17 May-16 Jun-16 Aug-16 Sep-16 Nov-16 Dec-16 Jan-17 Mar-17 Cash on hand (opening balance of the month) Cash inflow (Receipts) Cash Receipts 6,000 6,000 12,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 6,000 6,000 4,500 9,000 15,000 12,000 6,000 3,000 1,500 4,500 7,500 12,000 10,500 4,500 Collections from trade debtors 31,500 25,200 12,600 6,300 3,150 25,200 22,050 9,450 9,450 12,600 18,900 9,450 15,750 Collection from trade debtors 1,050 1,400 1,050 1,400 2,100 3,500 2,800 700 350 1,750 2,800 2,450 Cash receipt from treasury 10,000 Cash receipt from tax refund 1,000 Receipt through Bank Loans Total cash receipts Cash outflow (Payments) 27,700 11,700 8,650 5,700 13,950 29,650 41,000 48,600 40,700 21,400 10,700 15,000 42,000 30,000 38,950 35,350 16,400 Interest, rent & Administration exp 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 Income tax payments 2,500 Retire debt issues payments 1,000 Other cash disbursement - retire loan 25,000 7,000 disbursement towards labor and raw materials 3,000 12,000 14,000 25,000 38,000 30,000 10,000 5,000 3,000 14,000 31,000 29,000 Bank loans payments 25,200 6,950 9,950 1,650 14,600 13,400 4,700 18,350 Total cash Payments 15,000 15,950 29,650 48,600 10,700 34,000 10,000 41,000 40,700 21,400 42,000 38,950 35,350 Cash on hand (closing balance of the month) 6,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10.000 6,000 6,000 12,400