Question

Kilo-cone is an ice-cream store that sells 1kg ice cream cones, and has the following information:

Estimated monthly Sales (in cones)

January to March:       45,000 per month

April to September:   70,000 per month

October to December: 20,000 per month

Ice cream cones sell for $1 each from January 1^st to March 31^st, and $3 each during the rest of the year. Half of the sales are paid in cash, and the other half is on account. 90% of the sales on account are collected in the current quarter, and the remaining 10% of credit sales are collected in the following quarter.

Purchases and Inventory

Each cone requires 1kg of ice cream and 1 cone. Ice cream cost $14 for a 10kg tub and cones cost $10 for a box of 100 cones. All purchases are made on credit. Half of the purchases are paid for in the quarter of the purchase, and the other half is paid in the following quarter. (For simplicity, assume that the inventory has the same proportion of cones and ice-cream on hand.)

At the end of each quarter, they plan on having enough inventory on hand to cover ¼ of the sales for the next quarter.

Expenses and Disbursements

Labour: Salary costs are $52,000 for the year, or $13,000 per quarter. (For simplicity, assume that there are no salary accurals.)

Fixed expenses are expected to be $2,500 per month (Rent of $1,000 and depreciation of $1,500). Rent is paid at the beginning of each month.

The accounts payable at December 31 2019 was paid in full on January 312020.

Dividends

$340,000 of dividends are declared and paid on June 30^th, and $260,000 of dividends are declared and paid on September 30^th.

Line of Credit

The store has a line of credit that they can borrow from time to time. Funds are borrowed/repaid in increments of $1,000. Interest payments are on the 1^st day of the following quarter. For example, interest for the period January 1^st to March 31^st is due April 1^st. Interest is 3% per quarter.

Any extra cash is first used to pay off the line of credit. Assume that advances occur on the first day of each quarter, and repayments occur on the last day of each quarter.

Cash

At the end of each quarter, the minimum cash balance is $1,000.

Beginning Balances at January 1, 2020

Cash                                                              $   1,000

Inventory                                                         5,000

Accounts Receivable                                   5,000

Equipment                                                 180,000

Accumulated Depreciation                 (70,000)

Total Assets                                               121,000

Accounts Payable                                    $22,000

Line of Credit                                                        0

Retained Earnings                                  99,000

Total Liabilities and Equity                 121,000

REQUIRED:

1. Prepare a cash flow schedule by that shows how much cash is on hand at the end of each quarter of 2020. Show all relevant calculations and supporting schedules by fiscal quarter.
2. Prepare a balance sheet at December 31 2020.
3. Prepare an income statement for the year ending December 31 2020.

Answer 1

Sales Budget
	Jan-Mar	Apr-Jun	Jul-Sep	Oct-Dec
	1	2	3	4	Total
Sales budgeted(Cones)	135000	210000	210000	60000	615000
Selling price/cone	1	3	3	3
Total Sales $	135000	630000	630000	180000	1575000
Cash sales(50%)	67500	315000	315000	90000	787500
Credit sales (50%)	67500	315000	315000	90000	787500
Sales collections
Cash sales(50%)	67500	315000	315000	90000	787500
A/cs. Receivable	5000				5000
90% collections	60750	283500	283500	81000	708750
10% collections		6750	31500	31500	69750
Total sales collections	133250	605250	630000	202500	1571000

.

Production budget (in Units)
Sales budgeted(Cones)	135000	210000	210000	60000	615000
Desired ending inventory	52500	52500	15000	33750	33750
Total cones needed	187500	262500	225000	93750	648750
Less: Beg.Inv.($ 5000/$(1.4+0.1))=3333	3333	52500	52500	15000	3333
Production units budgeted	184167	210000	172500	78750	645417
Raw materials requirement & Cost budget
Production units budgeted	184167	210000	172500	78750	645417
Ice cream reqd.at 1 kg./cone	184167	210000	172500	78750	645417
1.$ value at $14/10 kgs=$ 1.4/cone	257833.8	294000	241500	110250	903583.8
Cone reqd. at 1 each	184167	210000	172500	78750	645417
2.$ value at $10/100 cones=$ 0.1/cone	18416.7	21000	17250	7875	64541.7
Total $ value of purchases(1+2)	276250.5	315000	258750	118125	968125.5
Payment for purchases
A/c payable	22000				22000
50% cash	138125.3	157500	129375	59062.5	484062.8
50% credit		138125.3	157500	129375	425000.3
Total amt. For purchases	160125.3	295625.3	286875	188437.5	931063

.

Cash Budget
Beginning balance	1000	1124.75	1459.5	1854.5	1000
Add:Total sales collections	133250	605250	630000	202500	1571000
Total cash available---------1	134250	606374.8	631459.5	204354.5	1572000
Less:Disbursements:
Total pmt. For purchases :	160125.3	295625.3	286875	188437.5	931063
Salary	13000	13000	13000	13000	52000
Rent(1000*3)	3000	3000	3000	3000	12000
Dividends		340000	260000		600000
Total cash disbursements---------2	176125.3	651625.3	562875	204437.5	1595063
Surplus/Deficit----------------1-2	-41875.3	-45250.5	68584.5	-83	-23063
Add: Borrowings	43000	48000		4000	95000
Less: Repayments			64000		64000
Less: Interest		1290	2730	2730	6750
Ending Balance	1124.75	1459.5	1854.5	1187	1187
Loan balance
Qtr.	1	2	3	4	Total
Beginning	0	43000	91000	27000	0
Borrowing at 1st day of qtr.	43000	48000	0	4000	95000
Repayment on last day of Qtr.	0	0	64000	0	64000
Ending balance	43000	91000	27000	31000	31000
Int. at 3% per qtr. paid for the prev. qtr. In the beg.of next qtr.
3% *(Beg. Bal.+Borrowing)		1290	2730	2730	6750
so, O/s interest is					930

.

	Borrowings Explained
Qtr.1	Deficit 41875+1000 min. bal reqd.= 42875 . Nearest '000 is 43000 ,
Qtr.2	Deficit 45251+Interest 1290+ 1000 min. bal. reqd.= 47541, so borrowed 48000
Qtr. 3	Surplus 68585--less int. 2730- 1000 min. bal. Reqd.= 64855, so repaid in '000s --64000
Qtr. 4	Deficit 83+Interest 2730+ 1000 min. bal. reqd.= 3813, so borrowed 4000
	& the loan balance is 43000+48000-64000+4000= 31000
	Int. to be paid in next Qtr(year) Beginning is 31000*3%=930

.

(B) Balance sheet
Cash	1187
Inventory (33750*(1.4+0.1))	50625
Accounts Receivable	9000
Equipment	180000
Accumulated Depreciation(70000+18000)	-88000
Total Assets	152812
Accounts Payable	59062.5
Interest payable	930
Line of Credit(95000-64000)	31000
Retained Earnings(99000+562820-600000)	61820
Total Liabilities and Equity	152813

(C) Income statement
Sales revenue (615000 cones)		1575000
Less: Cost of goods sold
Ice cream(615000*1.4 )	861000
Cones(615000*0.1/cone)	61500	922500
Gross profit		652500
Less: Operating expenses:
Salary	52000
Rent(1000*12)	12000
Depreciation(1500*12)	18000
Interest expense(6750+930)	7680	89680
Net operating income		562820