Ans:
b. based on the below cash budget company will have $25,000 cash which is the minimum cash they need to maintain hence it will to have borrow $20,000 from the other sources to repay the $20,000 notes due in June.
Cash budget as per the base case:
Base case | Jan | Feb | Mar | Apr | May | June |
Credit sales | 100,000 | 120,000 | 150,000 | 300,000 | 275,000 | 200,000 |
Cash sales | 20,000 | 74,000 | 120,000 | 171,000 | 250,000 | 267,500 |
Suppliers payment (at 65% of credit sales) made in the previous month |
-78,000 | -97,500 | -195,000 | -178,750 | -130,000 | -130,000 |
Rent | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 |
Other expenditure | -20,000 | -20,000 | -20,000 | -20,000 | -20,000 | -20,000 |
Tax paid | -22,500 | -22,500 | ||||
Interest paid | 0 | -850 | -1,385 | -2,660 | -3,038 | -2,138 |
Cash flow (before borrowing) | -88,000 | -53,500 | -127,500 | -37,750 | 90,000 | 85,000 |
Additional borrowing / repayment | 85,000 | 53,500 | 127,500 | 37,750 | -90,000 | -85,000 |
Net cash flow | -3,000 | 0 | 0 | 0 | 0 | 0 |
Cash balance | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 |
Borrowing at start of month | 0 | 85,000 | 138,500 | 266,000 | 303,750 | 213,750 |
Borrowing at month end | 85,000 | 138,500 | 266,000 | 303,750 | 213,750 | 128,750 |
Excel formula:
Best case with +20% rise in Credit sales:
Best case | Jan | Feb | Mar | Apr | May | June |
Credit sales (+20% increase in credit sales) | 120,000 | 144,000 | 180,000 | 360,000 | 330,000 | 240,000 |
Cash sales | 24,000 | 88,800 | 144,000 | 205,200 | 300,000 | 321,000 |
Suppliers payment (at 65% of credit sales) made in the previous month |
-93,600 | -117,000 | -234,000 | -214,500 | -156,000 | -156,000 |
Rent | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 |
Other expenditure | -20,000 | -20,000 | -20,000 | -20,000 | -20,000 | -20,000 |
Tax paid | -22,500 | -22,500 | ||||
Interest paid | 0 | -966 | -1,548 | -2,973 | -3,366 | -2,226 |
Cash flow (before borrowing) | -99,600 | -58,200 | -142,500 | -39,300 | 114,000 | 112,500 |
Additional borrwing / repayment | 96,600 | 58,200 | 142,500 | 39,300 | -114,000 | -112,500 |
Net cash flow | -3,000 | 0 | 0 | 0 | 0 | 0 |
Cash balance | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 |
Min cash balance | ||||||
Borrowing at start of month | 0 | 96,600 | 154,800 | 297,300 | 336,600 | 222,600 |
Borrowing at month end | 96,600 | 154,800 | 297,300 | 336,600 | 222,600 | 110,100 |
Excel formula:
Bear case with -20% decrease in credit sales:
Bear case | Jan | Feb | Mar | Apr | May | June |
Credit sales (-20% variation in credit sales) | 80,000 | 96,000 | 120,000 | 240,000 | 220,000 | 160,000 |
Cash sales | 16,000 | 59,200 | 96,000 | 136,800 | 200,000 | 214,000 |
Suppliers payment (at 65% of credit sales) made in the previous month |
-62,400 | -78,000 | -156,000 | -143,000 | -104,000 | -104,000 |
Rent | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 |
Other expenditure | -20,000 | -20,000 | -20,000 | -20,000 | -20,000 | -20,000 |
Tax paid | -22,500 | -22,500 | ||||
Interest paid | 0 | -734 | -1,222 | -2,347 | -2,709 | -2,049 |
Cash flow (before borrowing) | -76,400 | -48,800 | -112,500 | -36,200 | 66,000 | 57,500 |
Additional borrwing / repayment | 73,400 | 48,800 | 112,500 | 36,200 | -66,000 | -57,500 |
Net cash flow | -3,000 | 0 | 0 | 0 | 0 | 0 |
Cash balance | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 | 25,000 |
Min cash balance | ||||||
Borrowing at start of month | 0 | 73,400 | 122,200 | 234,700 | 270,900 | 204,900 |
Borrowing at month end | 73,400 | 122,200 | 234,700 | 270,900 | 204,900 | 147,400 |
Excel formula:
Preparation of a Cash Budget) Lewis Printing has projected its sales for the first 8 $200,000 January $100,000 april 300,000 july 180,000 February120,000 May 275,000 August June 200,000 March 1...
(Preparation of a cash budget) Lewis Printing has projected its sales for the first 8 months of 2016 as follows: January $140,000 April $300,000 July $200,000 February $100,000 May $255,000 August $200,000 March $170,000 June $180,000 Lewis collects 30 percent of its sales in the month of thesale, 40 percent in the month following the sale, and the remaining 30 percent 2 months following the sale. During November and December of 2015, Lewis's sales were...
Could you please solve it with the formula in Excel for each step? Thank you 17-12. (Preparing a cash budget) Harrison Printing has projected its sales for the first eight months of 2017 as follows: January $100,000 May $275,000 February 120.000 June 200,000 March 150,000 July 200,000 April 300.000 August 180.000 Harrison collects 20 percent of its sales in the month of the sale, 50 percent in the month following the sale, and the remaining 30 percent two months following...
(Cash budget) The Sharpe Corporation's projected sales for the first 8 months of 2016 are shown in the following table: January $170,000 May $280,000 February $100,000 June $250,000 March $115,000 July $205,000 April $220,000 August $130,000 Of Sharpe's sales, 30 percent is for cash, another 40 percent is collected in the month following the sales, and 30 percent is collected in the second month following sales. November and December sales for 2015 were $200,000 and $155,000, respectively. Sharpe...
January February March April May June Cash Credit Sales Sales $ 80,000 $180,000 $ 85,000 $200,000 $ 48,000 $160,000 $ 43,000 $128,000 $ 53,000 $230,000 $110,000 $220,000 The company is in the process of preparing a cash budget and must determine the expected cash collections by month. To this end, the following information has been assembled: Collections on sales: 50% in month of sale 40% in month following sale 10% in second month following sale The accounts receivable balance on...
Cash budget-Basic Grenoble Enterprises had sales of $49,600 in March and $60,500 in April. Forecast sales for May, June, and July are $70,300, $80,200, and $99,600, respectively. The firm has a cash balance of $4,600 on May 1 and wishes to maintain a minimum cash balance of $4,600. Given the following data, prepare and interpret a cash budget for the months of May, June, and July. (1) The firm makes 25% of sales for cash, 62% are collected in the...
Prepare the cash budget for the months of april may and june The following actual and budgeted sales relate to AgriTech Company, a distributor of agricultural products located somewhere in the Midwest. March (actual).... April May. June.. July............................. $50,000 60,000 72,000 90,000 48,000 The company's top executives intend to have 25% of sales as gross margin. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The company's inventory manager informs that...
Cash budget-Basic Grenoble Enterprises had sales of $49,900 in March and $59,800 in April. Forecast sales for May, June, and July are $69,900, $80,200, and $99,700, respectively. The firm has a cash balance of $5,500 on May 1 and wishes to maintain a minimum cash balance of $5,500. Given the following data, prepare and interpret a cash budget for the months of May, June, and July. (1) The firm makes 18% of sales for cash, 56% are collected in the...
Casa Development, Inc. has budgeted sales revenues as follows: January February March April May June Budgeted Sales Revenues $55,000 75,000 90,000 80,000 60,000 35,000 Past experience has indicated that 80% of sales each month are on credit and that collection of credit sales occurs as follows: 60% in the month of sale, 30% in the month following the sale, and 5% in the second month following the sale. The other 5% is uncollectible. Prepare a schedule which shows expected cash...
Cash budget-Basic Grenoble Enterprises had sales of $50,400 in March and $60,200 in April. Forecast sales for May, June, and July are $70,200, $80,100, and $100,400, respectively. The firm has a cash balance of $5,300 on May 1 and wishes to maintain a minimum cash balance of $5,300. Given the following data, prepare and interpret a cash budget for the months of May, June, and July. (1) The firm makes 22% of sales for cash, 61% are collected in the...
The treasure of Unisyms Company has accumulated the following budget information for the first two months of the coming year:March AprilSales $450,000 $520,000Manufacturing $290,000 $350,000Selling &Admin. expenses $41,000 $46,4000Capital additions $250,000 -------The company expects to sell about 35% of its merchandise for cash of sales on account 80% are expected to be collected in full in the month of the sale andremainder in the month following the sale. One-fourth of the manufacturing costs are expected to be paid in the...