For this assignment you will utilize the attached 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. (57 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. (19 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.(10 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. (5 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. (9 pts)

All data and calculations must reference a cell from the input data or other cells calculated within the cash budget section. Any data manually entered will receive a score of zero.

**PLEASE SHOW EXCEL CELLS THAT ARE HIGHLIGHTED/ANY FORMULAS USED

A1 xVfc Monthly Cash Budget Example 1 Monthly Cash Budget Example Input Data Anticipated level of sales Estimated labor and raw materials 4 30% 90% 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 Cash sales Collections during month after sale Collections during second month after sale 12 15 16 18 20 21 Prepare a monthly cash budget for the next thirteen months 24 Anticipated Sales Sales (gross) Cash sales 26 27 28 29 30 31 Collections During 1st month after sale During 2nd month after sale Other cash receipts 32 Total Cash Receipts 35 36 37 38 39 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 41 42 45 46 47 48 Surplus cash or loan needed A1 xVfc Monthly Cash Budget Example 1 Monthly Cash Budget Example Input Data Anticipated level of sales Estimated labor and raw materials 4 30% 90% 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 Cash sales Collections during month after sale Collections during second month after sale 12 15 16 18 20 21 Prepare a monthly cash budget for the next thirteen months 24 Anticipated Sales Sales (gross) Cash sales 26 27 28 29 30 31 Collections During 1st month after sale During 2nd month after sale Other cash receipts 32 Total Cash Receipts 35 36 37 38 39 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 41 42 45 46 47 48 Surplus cash or loan needed