80.2K

Verified Solution

Link Copied!

Active Life Inc., a sports equipment retailer, needs to prepare a cash budget for the first quarter of 2018. The financial staff at Active Life has forecasted the following sales figures: January February March April May $100,000 $150,000 $300,000 $250,000 $150,000 Actual sales in October, November, and December 2017 were $125,000, $146,000, and $125,000, respectively. Cash sales are 40% of the total, and the rest are on credit. Under the current credit policy the firm expects to collect 60% of credit sales the following month, 30% two months after, and the remainder in the third month after the sale. Each month, the firm makes inventory purchases equal to 45% of the of the next month's sales. The firm pays for 40% of its inventory purchases in the same month and 60% in the following month; nevertheless, the firm enjoys a 2% discount if it pays during the same month as the purchase. Estimated disbursements include monthly wages and other expenses representing 25% of the same month's sales; a major capital outlay of $30,000 expected in January; a dividend payment of $25,000 in February; $40,000 of long-term debt maturing in March; and a tax payment of $60,000 in April. The interest rate on its short-term borrowing is 7%. It has a required minimum cash balance of $10,000 every month, and has an ending cash balance of $30,000 for December 2017. a) Using the above information, create a cash budget for January to June 2018. The cash budget should account for short-term borrowing and payback of outstanding loans. b) Using Excel's outline feature, group the worksheet area at the top of the cash budget so that the preliminary calculations can be easily hidden or unhidden. XV fx Active Life Inc. Cash Budget For the Period January to April 2018 November December January || er April May 5 Sales 6 Collections: 7 Cash 8 First Month 9 Second Month 10 Third Month 11 Total Collections 12 Purchases 13 Payments: 14 During Month 15 Month After 16 Total Payments 17 Collections 18 Less disbursements: 19 Inventory Payments 20 Short-term Interest 21 Wages and other exp. 22 Capital outlay 23 Dividend (Common) 24 Long-term debt 25 Taxes 26 Total Disbursements 27 Beginning Cash Balance 28 Net Additions to Cash 29 Unadjusted Cash Balance 30 Add: Current Borrowing 31 Ending Cash Balance 32 Cumulative Borrowing 33 34 Minimum Cash Balance 35 Discount for Paying in Same Month 36 Annual Interest Rate 37 Monthly Interest Rate 38 Total Interest Cost 39 Beginning receivables 40 Beginning payables Problem 3 Complete the Excel Sheet and show formulas that were used

Answer & Explanation Solved by verified expert

See Answer