cf_cash_budgeting_template.xlsx

Part A

ACC5610 & ACC-FPX5610: Budgeting Planning and Control
Assessment 4: Cash Budgeting Worksheet
Part A									Input values
Spicer Corporation would like to prepare a cash budget in order to determine the company's financing needs for the
upcoming year. The beginning cash balance is $161,250.									Company name	Spicer Corporation
The sales for the upcoming year is as follows:									Year end	December 31, 20XX
Spicer Corporation									Spicer Corporation
Sales Budget for the Year Ending December 31, 20XX									Sales Budget for the Year Ending December 31, 20XX
	First Quarter		Second Quarter		Third Quarter		Fourth Quarter				1st Qtr	2nd Qtr	3rd Qtr	4th Qtr
Projected number of units to be sold		1,525		1,675		1,775		1,550	Projected number of units to be sold		1,525	1,675	1,775	1,550
Sales price per unit		$ 275		$ 275		$ 275		$ 275	Sales price per unit		$ 275	$ 275	$ 275	$ 275
Projected revenue		$ 419,375		$ 460,625		$ 488,125		$ 426,250	Projected revenue		$ 419,375	$ 460,625	$ 488,125	$ 426,250
All sales are on account. It is reported that 65% of the accounts receivable is collected in the quarter of the sale and									Spicer Corporation
35% in the quarter after the sale. The beginning accounts receivable is $142,000.									Operating Expenses for the Year Ending December 31, 20XX
All the purchases are made on account. The company pays 100 percent of the accounts payable each quarter. The											1st Qtr	2nd Qtr	3rd Qtr	4th Qtr
purchases are related to the inventory needed to sell each month. On an average, the inventory costs $195 per unit									Salaries and wages		$ 55,625	$ 55,450	$ 54,675	$ 56,285
to produce. It is estimated that enough inventory is purchased each quarter to cover the projected quarterly sales only.									Advertising		1,200	1,500	900	2,000
The beginning accounts payable is $0.									Utilities		2,700	2,700	2,700	2,700
									Rent		6,000	6,000	6,000	6,000
Additional quarterly expenses include the following:									Total		$ 65,525	$ 65,650	$ 64,275	$ 66,985
Spicer Corporation
Operating Expenses for the Year Ending December 31, 20XX									Beginning cash balance			$161,250
									Beginning accounts receivable balance			$142,000
	First Quarter		Second Quarter		Third Quarter		Fourth Quarter		% of accounts receivables collected in the			65%
Salaries and wages		$ 55,625		$ 55,450		$ 54,675		$ 56,285	quarter of the sale
Advertising		1,200		1,500		900		2,000	% of accounts receivables collected in the			35%
Utilities		2,700		2,700		2,700		2,700	quarter after the sale
Rent		6,000		6,000		6,000		6,000	Inventory cost per unit			$195
Total		$ 65,525		$ 65,650		$ 64,275		$ 66,985	Note payable			$165,000
									Annual interest rate on note payable			6%
On the balance sheet, Spicer Corporation reports a note payable of $165,000. The principal plus interest is due on									Cost of new machine			$50,000
April 30. Until then, interest payments are made each month. The note has an annual interest rate of 6%.									Minimum cash balance			$50,000
									Line of credit borrowings/repayment			$10,000
It is projected that in October of the current year, Spicer Corporation will need to purchase a new machine in order to									Minimum surplus for repayment of line of credit			$5,000
replace an outdated machine. The new machine will cost $50,000. The purchase is expected to be made with cash.									Annual interest rate on line of credit			4%
									Amount of line of credit (in millions of dollars)			$1
Spicer Corporation currently has a line of credit of $1 million that can be used to cover any deficiencies. The line of credit									Month in which new machine is purchased			October
has a 4% annual interest rate. Interest payments must be made at the end of the quarter when there is a balance									Interest due date			30-Apr
outstanding. Borrowings must be made in increments of $10,000. It is a standard practice that the line of credit is paid
when there is a surplus of the minimum cash balance in increments of $5,000.
Part A: Complete the following.
Assume that the management wants to maintain a minimum cash balance of $50,000. Prepare a cash budget for
Spicer Corporation for the upcoming year, including a cash receipts schedule.
Spicer Corporation
Cash Budget for the Year Ending December 31, 20XX
	Jan-Mar		Apr-June		Jul-Sept		Oct-Dec
	First Quarter		Second Quarter		Third Quarter		Fourth Quarter
Spicer Corporation
Budgeted Accounts Receivables and Cash Collections From Customers for the Year Ending December 31, 20XX
	First Quarter		Second Quarter		Third Quarter		Fourth Quarter

Part B

	Part B
	Part B: Complete the following.
	After completing Spicer's cash budget, use the memo template in Course Resources to draft a memo to management explaining the general purpose of a cash budget and its relationship to operational goals. As part of the memo, identify at least 3 key aspects of the current cash budget that management should note. For each key aspect, be sure to discuss how the overall cash budget will be impacted if there is a change in the expectation.

cf_cash_budgeting_template.xlsx

Part A

Part B

Our Services

Our Advantages

Ready to Score Higher Grades?