The owners of Exotic Imports, Inc. were worried about the cash position of the company after the first three months of operations. They believed that cash flows would be favorable after June. The company planned to borrow any amount needed to carry it through the first quarter as soon as a minimum cash balance of $10,000 is reached. A line of credit was arranged at the bank.
The budget was based on the following assumptions:
Sales for the first six months are expected to be as follows:
April May June
$40,000 July
$60,000 August $120,000 September
$200,000 $250,000 $100,000
-
The gross margin on sales was expected to be 60%.
-
The company planned to carry an inventory equal to expected sales for the next month.
-
Purchases were to be paid in the following month.
-
Variable selling expenses were expected to equal 20% of sales.
-
Fixed selling and administrative expenses were expected to be $30,000 per month, including
$1,000 of depreciation.
-
All of the payments for the variable selling and fixed selling and administrative expenses
expenses were to be paid in the month of expense.
-
All of the sales were expected to be credit sales with 70% collected in the month of sale and
30% in the following month.
After reviewing the results of the budgets for April, May and June that are given in the MMg TP2 21S spreadsheet, the owners are still uncertain about the liquidity position of the company. They have asked you to continue the budget for the month of July. You are asked to ignore interest expense in your calculations.
REQUIRED: Download the spreadsheet for MMg TP2 21S. Fill in the cells highlighted in yellow.
-
Prepare a profit plan for July.
-
Prepare a cash budget for July.
-
Prepare a projected statement of financial position at July 31, 20X0.
-
Compute the break-even point in sales dollars for each month, for both accrual and cash-basis accounting.
-
Does it appear that by July the liquidity problem will be solved and that the firm will be profitable?
Req.3 July April May June 28,000 42,000 12,000 54,000 84,000 18,000 102,000 28,000 (16,000) (24,000) (40,000) (24,000) (48,000) 24,000 (48,000) (40,000) (12,000) (29,000) (81,000) (48,000) (80,000) 48,000 (80,000) (48,000) (24,000) (29,000) (101,000) (8,000) (29,000) (37,000) 3 Exotic Imports, Inc. 4 Cash Budget 5 For the months of 20X0 6 9 Collection in month of sale 10 Collection in following month 12 Cash revenues 13 14 Cost of goods sold 15 Ending inventory 16 Beginning inventory 17 Purchases 18 Payments for inventory 19 Selling expenses (variable) 20 Selling and administrative (fixed) 21 Cash expenses 22 23 Cash basis operating income 24 25 Beginning cash balance 26 Subtotal 27 Minimum cash balance 28 Needed borrowing 29 Available for repayment 30 Repayment of borrowing 32 Ending cash balance 33 34 Req. 5b Cash-basis fixed costs 35 Req. 5c Cash-basis contribution margin 36 Req. 5d Cash-basis contribution margin ratio 37 Req. 5e Break-even $ on a cash basis 38 (9,000) (27,000) 1,000 30,000 21,000 10,000 21,000 (6,000) 11,000 10,000 10,000 10,000 16,000 (1,000) (1,000) $ 21,000 $ 10,000 $ 10,000 3 Exotic Imports, Inc. 4 Balance Sheet 5 As of the dates given, 20X0 Req.4 July 31 April 1 April 30 May 31 June 30 $ 30,000 $ 21,000 $ 10,000 $ 10,000 12,000 18,000 36,000 24,000 48,000 30,000 57,000 76,000 46,000 70,000 70,000 70,000 70,000 (1,000) (2,000) (3,000) 70,000 69,000 68,000 67,000 $ 100,000 $ 126,000 $ 144,000 $ 113,000 7 3 Cash 3 Accounts receivable LO Inventory -1 Total current assets -2 Buildings and equipment, gross _3 Accumulated depreciation 4 Buildings and equipment, net 5 Total assets .6 -7 Accounts payable _9 Note payable 1 Total current liabilities -2 Capital stock -3 Retained earnings -4 Total shareholders' equity -5 Total liabilities and owners' equity 26 $ $ 40,000 $ 48,000 $ 16,000 40,000 64,000 100,000 100,000 100,000 (14,000) (20,000) 100,000 86,000 80,000 $ 100,000 $ 126,000 $ 144,000 15,000 15,000 100,000 (2,000) 98,000 113,000 Req. 2 July April May June 40,000 $ 60,000 $ 120,000 (16,000) (8,000) (24,000) 16,000 $ (24,000) (12,000) (36,000) 24,000 $ (48,000) (24,000) (72,000) 48,000 $ 2 3 Exotic Imports, Inc. 4 Income Statements 5 For the months of 20X0 6 7 Sales 8 Variable costs: 9 Cost of goods sold 10 Selling expenses 11 Total variable costs 12 Contribution margin 13 Fixed costs: 14 Selling and administrative 15 Depreciation 17 Total fixed costs 18 Operating income 19 20 Contribution margin ratio 21 22 Req. 5a. Break-even $ 23 24 Req. 6 Analysis: (29,000) (1,000) (30,000) (14,000) $ (29,000) (1,000) (30,000) (6,000) $ (29,000) (1,000) (30,000) 18,000 $ 25 26 Req.3 July April May June 28,000 42,000 12,000 54,000 84,000 18,000 102,000 28,000 (16,000) (24,000) (40,000) (24,000) (48,000) 24,000 (48,000) (40,000) (12,000) (29,000) (81,000) (48,000) (80,000) 48,000 (80,000) (48,000) (24,000) (29,000) (101,000) (8,000) (29,000) (37,000) 3 Exotic Imports, Inc. 4 Cash Budget 5 For the months of 20X0 6 9 Collection in month of sale 10 Collection in following month 12 Cash revenues 13 14 Cost of goods sold 15 Ending inventory 16 Beginning inventory 17 Purchases 18 Payments for inventory 19 Selling expenses (variable) 20 Selling and administrative (fixed) 21 Cash expenses 22 23 Cash basis operating income 24 25 Beginning cash balance 26 Subtotal 27 Minimum cash balance 28 Needed borrowing 29 Available for repayment 30 Repayment of borrowing 32 Ending cash balance 33 34 Req. 5b Cash-basis fixed costs 35 Req. 5c Cash-basis contribution margin 36 Req. 5d Cash-basis contribution margin ratio 37 Req. 5e Break-even $ on a cash basis 38 (9,000) (27,000) 1,000 30,000 21,000 10,000 21,000 (6,000) 11,000 10,000 10,000 10,000 16,000 (1,000) (1,000) $ 21,000 $ 10,000 $ 10,000 3 Exotic Imports, Inc. 4 Balance Sheet 5 As of the dates given, 20X0 Req.4 July 31 April 1 April 30 May 31 June 30 $ 30,000 $ 21,000 $ 10,000 $ 10,000 12,000 18,000 36,000 24,000 48,000 30,000 57,000 76,000 46,000 70,000 70,000 70,000 70,000 (1,000) (2,000) (3,000) 70,000 69,000 68,000 67,000 $ 100,000 $ 126,000 $ 144,000 $ 113,000 7 3 Cash 3 Accounts receivable LO Inventory -1 Total current assets -2 Buildings and equipment, gross _3 Accumulated depreciation 4 Buildings and equipment, net 5 Total assets .6 -7 Accounts payable _9 Note payable 1 Total current liabilities -2 Capital stock -3 Retained earnings -4 Total shareholders' equity -5 Total liabilities and owners' equity 26 $ $ 40,000 $ 48,000 $ 16,000 40,000 64,000 100,000 100,000 100,000 (14,000) (20,000) 100,000 86,000 80,000 $ 100,000 $ 126,000 $ 144,000 15,000 15,000 100,000 (2,000) 98,000 113,000 Req. 2 July April May June 40,000 $ 60,000 $ 120,000 (16,000) (8,000) (24,000) 16,000 $ (24,000) (12,000) (36,000) 24,000 $ (48,000) (24,000) (72,000) 48,000 $ 2 3 Exotic Imports, Inc. 4 Income Statements 5 For the months of 20X0 6 7 Sales 8 Variable costs: 9 Cost of goods sold 10 Selling expenses 11 Total variable costs 12 Contribution margin 13 Fixed costs: 14 Selling and administrative 15 Depreciation 17 Total fixed costs 18 Operating income 19 20 Contribution margin ratio 21 22 Req. 5a. Break-even $ 23 24 Req. 6 Analysis: (29,000) (1,000) (30,000) (14,000) $ (29,000) (1,000) (30,000) (6,000) $ (29,000) (1,000) (30,000) 18,000 $ 25 26
