final finance problem i need this tomorrow

the first two attachments are examples the last one is the excel spreadsheet that needs to be field out

for the homework below

   

Complete the following problems. For assistance, you may want to refer to these examples. The Word document has instructions on using the Excel spreadsheet.

 Week 11 Example Problems

Week 11 Example Problems.xls

Required:Download the

Week 11 Problems Excel spreadsheet

to use in completing your problems.

1. Given the following information:

75 percent of sales are for credit, and collections occur after thirty days.A $100,000 Treasury bill matures in March.Monthly fixed disbursements are $13,000.Variable disbursements are 62 percent of sales and occur one month prior to sales.A tax payment of $13,500 is due in February.The initial cash is $20,000.The minimum required cash balance is $5,000.Variable cash disbursements for April are $30,000.SalesJanuary0February60,000March80,000April100,000Construct the firm’s cash budget for the given months.

1. Given the following information:SalesJune$200,000July200,000August200,000September300,000October500,000November200,00070% of the sales are for credit and are collected one month after the sale. Other receipts:  $50,000 in OctoberVariable disbursements: 60% of sales each monthOther disbursements: $10,000 a month$80,000 for taxes in August$400,000 for debt repayment in NovemberBeginning cash: $50,000Desired cash: $10,000

Prepare a monthly cash budget for this firm.

Week11 Example Problems

1. Given the following information:

a. 50 percent of sales are for credit, and collections occur after thirty days.

b. A $1

50,000

Treasury bill matures in

March

.

c. Monthly fixed disbursements are $15,000.

d.

Variable disbursements

are 60 percent of sales and occur one month prior to sales.

e. A tax payment of $25,000 is due in

February

.

f. The initial cash is $10,000.

g. The minimum required cash balance is $5,000.

h. Variable cash disbursements are given for

April

.

Complete the following to construct the firms cash budget for the given months.

January	February	March	April
	Sales	$50,000	$65,000	$75,000
Cash Sales
Collections
Other receipts
Total cash receipts
Variable disbursements		35,000
Fixed disbursements
Other disbursements
Total cash disbursements
Net change during the month
Beginning cash
Ending cash
Required cash
Excess cash to invest
Cash borrowed

Solution:

Please download week 11 Example Problems Excel template for the solution to this problem. This problem is located in the Problem 1 tab.

The requirement to cover expenses before sales and the collection of accounts receivable means the firm initially has a cash outflow, which requires temporary borrowing. The redemption of the Treasury bill and the collection of the accounts receivable are used to retire the short-term loans, so the firm has excess cash to invest short-term at the end of the budget period.

2. Given the following information:

Sales

100,000

June	$ 100,000
July	500,000
August		100,000
September	50,000
October
November	1,000,000

a. 40% of the sales are for credit and are collected one month after the sale.

b. Other receipts: $100,000 in October

c. Variable disbursements: 80% of sales each month

d. Other disbursements: $10,000 a month

e. $80,000 for taxes in August

f. Beginning cash: $50,000

g. Desired cash: $10,000

Prepare a monthly cash budget for the firm.

Solution:

Please download the week 11 Example Problems Excel template for the solution to this problem. This problem is located in the Problem 2 tab.

The cash budget shows that the firm needs cash during July. Once July has passed, the firm’s cash position improves so the firm can easily retire any short-term loan taken out to cover July’s cash shortage. Then, even though it has a great sales month in November, cash needs are high, so it has to borrow again.

Adapted from:

Mayo, H. (2007). Basic finance: An introduction to financial institutions, investments & management. United States: Thomson South-Western.

Problem 1

25,000 32,500

25,000

15,000 15,000 15,000

25,000

45,000

50,000

(35,000) (89,000) 58,500

5,000 5,000 5,000 5,000

																								January	February	March	April
	Sales					50,000	6				5,000	75,000
Cash Sales (collect 50% in current month)				25,000		32,500	37,500
Collections (collect 50% from prior month)
Other receipts			150,000
	Total cash receipts	207,500		70,000
Variable disbursements (60% of sales, one month prior)				30,000	39,000		45,000	35,000
	Fixed disbursements				15,000
	Other disbursements
	Total cash disbursements	79,000								60,000
	Net change during the month	(45,000)	(54,000)	147,500					20,000
	Beginning cash														10,000		(35,000)		(89,000)		58,500
	Ending cash	78,500
	Required cash
	Excess cash to invest	53,500	73,500
	Cash borrowed							40,000	94,000

Problem 2

Sales

$100,000

$100,000

60,000

60,000 30,000 60,000

40,000

40,000 20,000 40,000

100,000

Total cash receipts 60,000

70,000

80,000

80,000 40,000 80,000

Fixed disbursements 10,000 10,000 10,000 10,000 10,000 10,000
Other disbursements 80,000
Total cash disbursements

50,000 90,000

Net change during the month

90,000 20,000 90,000

Beginning cash 50,000 20,000

40,000 60,000 150,000

Ending cash 20,000 (50,000) 40,000 60,000 150,000

Required cash 10,000 10,000 10,000 10,000 10,000 10,000
Excess cash to invest 10,000 30,000 50,000

Cash borrowed 60,000 30,000

June	July	August	September	October	November
		$	100,000	$500,000	$50,000	$1,000,000
Cash sales (60% collected in current month)	300,000	600,000
Collections (40% collected in following month)	200,000
Other Receipst
340,000	260,000	1				80,000	640,000
Variable disbursements (80% of sales)	400,000	800,000
			90,000	410,000	170,000	810,000
(30,000)	(70,000)	(170,000)
	(50,000)
(20,000)
140,000

Problem 1

		January	February	March	April
	Sales
Cash Sales
	Collections
Other receipts
	Total cash receipts
	Variable disbursements
	Fixed disbursements
	Other disbursements
	Total cash disbursements
	Net change during the month
	Beginning cash
	Ending cash
	Required cash
	Excess cash to invest
	Cash borrowed

Problem 2

Sales

Collections

Total cash receipts
Variable disbursements
Fixed disbursements
Other disbursements
Total cash disbursements
Net change during the month
Beginning cash
Ending cash
Required cash
Excess cash to invest
Cash borrowed

June	July	August	September	October	November
Cash sales
Other Receipts