Question

Question 1: - Cash Budget:

Estimated sales	January	February	March	April	May
Total Coffee sales (units)	15,000	16,000	17,500	18,000	14,500

Your friend, MATT, has been running a successful coffee shop for the last couple of years. He has asked you to put together a cash budget. His regular accountant was too busy to help, but told MATT his depreciation expense was going to be $1,800 per year, using the straight-line method. He has supplied you with the following information (above) to help you put together his cash budget.

The revenue for a coffee is $5 for a large, $4 for a medium and $3 for a small. 50% of his sales are Large coffees, 25% medium and 25% smalls. MATT has a deal with an accounting company and half of the coffees sold are on account, the other half pay cash. The accounting company pays for the coffees the month after the sale. Assume credit sales are half of the total coffee sales.

Material and Labour information MATT has provided:

	Large	Medium	Small
Labour hours	0.1	0.05	0.05
Coffee pods	3	2	1
Cost of a Coffee pod	$0.90

Labour is paid at $20/hour. The Coffee pods need to be ordered a month in advance. He requires 10% of next months total coffee pods to be on hand. The beginning coffee pod inventory, February 1, was 3,000 pods. 50% of the Coffee pods are paid in the month they are ordered, 50% are paid the following month.

Other information Chris has provided:

	February	March	April	May
Rent	2,500	2,500	2,500	2,500
Dividend	3,000			15,000
Office and Admin	3,500	3,500	3,500	3,500

NOTE: Chris requires a minimum bank balance of $5,000 at the end of the month and the ending January bank balance is $10,000. Chris has access to a line of credit of $500,000 at an interest rate of 30%. Borrowings are made at the beginning of the month and repayments are made at the end of the month.

REQUIRED:

Using the Excel file included with the exam, for the months of February, March and April prepare

• Sales Budget, with a cash receipts.
  • What is the A/R at the end of the month?
• Materials Budget, with cash disbursements.
  • What is the A/P at the end of the month?
• Direct Labour Budget
• Cash budget

Answer 1

a	Sales Budget
	Months	Jan	Feb	Mar	April	May
	Total Coffee sales Units	15000	16000	17500	18000	14500
A	Large Coffee sales (50%) units	7500	8000	8750	9000	7250
B	Medium Cofee sales (25%) units	3750	4000	4375	4500	3625
C	Small Coffee sales (25%) units	3750	4000	4375	4500	3625
D	Revenue from Large Units @5/unit=A*5	$      37,500	$      40,000	$      43,750	$      45,000	$      36,250
E	Revenue from Medium Units @4/unit=B*4	$      15,000	$      16,000	$      17,500	$      18,000	$      14,500
F	Revenue from Small Units @3/unit=C*3	$      11,250	$      12,000	$      13,125	$      13,500	$      10,875
G	Total Revenue=D+E+F	$      63,750	$      68,000	$      74,375	$      76,500	$      61,625

b	Cash Receipts
H	Cash Sales ( @50% of total sales )=50%*G	$   31,875.0	$   34,000.0	$   37,187.5	$   38,250.0	$   30,812.5
I	Cash Receipts from Credit sale with a month's delay for (remaining 50% credit sale )		$   31,875.0	$   34,000.0	$   37,187.5	$   38,250.0
J	Total Cash Receipt=H+I		$   65,875.0	$   71,187.5	$   75,437.5

c	The AR at the end of April will be $38250

	Materials Budget
	Months	Jan	Feb	Mar	April	May
	Total Coffee sales Units	15000	16000	17500	18000	14500
A	Large Coffee sales (50%) units	7500	8000	8750	9000	7250
B	Medium Cofee sales (25%) units	3750	4000	4375	4500	3625
C	Small Coffee sales (25%) units	3750	4000	4375	4500	3625
D	No of Coffee pod srequired for large @3/unit=A*3	22500	24000	26250	27000	21750
E	No of Coffee pod srequired for medium @2/unit=B*2	7500	8000	8750	9000	7250
F	No of Coffee pod srequired for small @1/unit=C*1	3750	4000	4375	4500	3625
G	Total Coffee pods Required	33750	36000	39375	40500	32625
H	Pods required in units ( =100% or next month's requirement)	36000	39375	40500	32625	0
J	Closing stock required=10% of next month sale	3600	3937.5	4050	3262.5	0
I	Coffe pods opening stock	3000	3600	3937.5	4050
K	Pods to be purchased=H+J-I	36600	39712.5	40612.5	31837.5	0

	Cash Disbursement for Materials	Jan	Feb	Mar	April	May
L	Cost of each coffe pod	$           0.90	$           0.90	$           0.90	$           0.90	$           0.90
M	Cost of Purchasing Coffe pods =L*K	$ 32,940.00	$ 35,741.25	$ 36,551.25	$ 28,653.75
N	Paymnet for cash purchases (50% of purchase)=M*50%	$ 16,470.00	$ 17,870.63	$ 18,275.63	$ 14,326.88	$               -
O	Paymnet for credit purchase with one months delay		$ 16,470.00	$ 17,870.63	$ 18,275.63	$ 14,326.88
P	Total Payment to be made for materials=M+N		$ 34,340.63	$ 36,146.25	$ 32,602.50

d	AP at the end of April is $14326.88

	Direct Labor Budget	Jan	Feb	Mar	April	May
	Total Coffee sales Units	15000	16000	17500	18000	14500
A	Large Coffee sales (50%) units	7500	8000	8750	9000	7250
B	Medium Cofee sales (25%) units	3750	4000	4375	4500	3625
C	Small Coffee sales (25%) units	3750	4000	4375	4500	3625
D	Labor Hours for Large @0.1 Hr/unit= A80.1	750	800	875	900	725
E	Labor Hours for medium @0.0.05 Hr/unit=B*0.05	187.5	200	218.75	225	181.25
F	Labor Hours for small @0.0.05 Hr/unit=C*0.05	187.5	200	218.75	225	181.25
G	Total Labor Hours=D+E+F	1125	1200	1312.5	1350	1087.5
H	Cash Payment required for Labor @$20/Hr =G*20	$      22,500	$      24,000	$      26,250	$      27,000	$      21,750

	Cash Budget	Jan	Feb	Mar	April
A	Cash Receipts from sales		$   65,875.0	$   71,187.5	$   75,437.5
	Cash Disbursements
	Cash disbursement for Materials		$      34,341	$      36,146	$      32,603
	Cash disbursement for labor		$      24,000	$      26,250	$      27,000
	Cash disbursement for Rent		$         2,500	$         2,500	$         2,500
	Cash disbursement for dividend pay		$         3,000
	Cash Disbursement for office & admin exp		$         3,500	$         3,500	$         3,500
B	Total Cash disbursement		$      67,341	$      68,396	$      65,603
	Net Cash Position=A-B		$   (1,465.6)	$     2,791.3	$     9,835.0
	Opening Balance Bank		$      10,000	$   8,534.38	$ 11,325.63
	Closing Blalance		$   8,534.38	$ 11,325.63	$ 21,160.63