all questions please. with format
all questions please
24 25 26 27 Sport Equipment, Inc. Forecasted Ending Cash Balance September 2018 to January 2019 Aug-18 Sep-18 Oct-18 given 230,000 240,000 185,000 28 Nov-18 310,000 Dec-18 450,000 Jan-19 310,000 Feb-19 270,000 29 Expected Sales 30 31 32 Cash Collections: Cash Sales Credit Sales Total Collections 70.0% 30.0% 33 168,000 69,000 237,000 129,500 72,000 201,500 217,000 55,500 272,500 315,000 93,000 408,000 217,000 135,000 352,000 34 35 36 37 138,750 40,000 232,500 40,000 337,500 40,000 232,500 40,000 202,500 40,000 38 Cash Disbursements: Purchases - all cash Fixed and Admin Wages Bonuses in December and June Sales Commissions Interest on Short-term Borrowing Total Disbursements 75% 40,000 70,000 19% 39 40 43,700 45,600 35,150 58,900 85,500 41 42 43 44 45 46 47 Beginning Cash Balance +Collections - Disbursemesnts Unadjusted Cash Balance Monthly Short-term Borrowing Ending Cash Balance 60,000 Cumulative Short-term Borrowing (Loan Balance) 0 Minimum Cash Balance 50,000 Interest Rate on Short-term Borrowing 11% annual 48 49 50 51 A B C D E F G H j K L M 2 1 Complete the simple cash budget given below by using the assumptions in points 1 and 2 below. Then answer the questions in point 3. All yellow cells in the model must be filled in USING FORMULAS. The cells cannot contain any typed in numbers! Assume you want to use the model for a What-If Scenario Analysis. 3 4 5 Hint: Calculate the Interest on Short-term Borrowing in row 41 only after you calculate everything else in the model. 6 7 1 8 9 10 11 12 2 30pts Ending cash balance in August was $60,000. Calculate unadjusted cash balance. The firm requires minium cash balance of $50,000. Any shortfall in cash will be covered by short-term borrowing. Calculate how much the firm needs to borrow every month to assure that the cash balance does not fall below $50,000. Consider only borrowing and not the repayment, assume that any short-term borrowing will be repaid later, i.e., excess cash balances will roll over to the next month Calculate ending cash balance in each month. 5pts The firm will be charged 11% annual interest rate on its short-term borrowing. Calculate monthly interest expense. The firm had no short-term borrowing at the end of August. 10pts Based on your completed cash budget model, answer the following three questions: What is the projected amount of interest that the firm has to pay in the period Sept 2018 - Jan 2019? How much cash is the company expected to have at the end of January? Can the company repay the short-term loan at the end of January? Answer Yes/No and why. 15pts Conduct a Scenario Analysis to determine the impact on Final Ending Cash Balance by changing Sales commission to 10% and 25% 13 14 3 15 16 17 18 4 24 25 26 27 Sport Equipment, Inc. Forecasted Ending Cash Balance September 2018 to January 2019 Aug-18 Sep-18 Oct-18 given 230,000 240,000 185,000 28 Nov-18 310,000 Dec-18 450,000 Jan-19 310,000 Feb-19 270,000 29 Expected Sales 30 31 32 Cash Collections: Cash Sales Credit Sales Total Collections 70.0% 30.0% 33 168,000 69,000 237,000 129,500 72,000 201,500 217,000 55,500 272,500 315,000 93,000 408,000 217,000 135,000 352,000 34 35 36 37 138,750 40,000 232,500 40,000 337,500 40,000 232,500 40,000 202,500 40,000 38 Cash Disbursements: Purchases - all cash Fixed and Admin Wages Bonuses in December and June Sales Commissions Interest on Short-term Borrowing Total Disbursements 75% 40,000 70,000 19% 39 40 43,700 45,600 35,150 58,900 85,500 41 42 43 44 45 46 47 Beginning Cash Balance +Collections - Disbursemesnts Unadjusted Cash Balance Monthly Short-term Borrowing Ending Cash Balance 60,000 Cumulative Short-term Borrowing (Loan Balance) 0 Minimum Cash Balance 50,000 Interest Rate on Short-term Borrowing 11% annual 48 49 50 51 A B C D E F G H j K L M 2 1 Complete the simple cash budget given below by using the assumptions in points 1 and 2 below. Then answer the questions in point 3. All yellow cells in the model must be filled in USING FORMULAS. The cells cannot contain any typed in numbers! Assume you want to use the model for a What-If Scenario Analysis. 3 4 5 Hint: Calculate the Interest on Short-term Borrowing in row 41 only after you calculate everything else in the model. 6 7 1 8 9 10 11 12 2 30pts Ending cash balance in August was $60,000. Calculate unadjusted cash balance. The firm requires minium cash balance of $50,000. Any shortfall in cash will be covered by short-term borrowing. Calculate how much the firm needs to borrow every month to assure that the cash balance does not fall below $50,000. Consider only borrowing and not the repayment, assume that any short-term borrowing will be repaid later, i.e., excess cash balances will roll over to the next month Calculate ending cash balance in each month. 5pts The firm will be charged 11% annual interest rate on its short-term borrowing. Calculate monthly interest expense. The firm had no short-term borrowing at the end of August. 10pts Based on your completed cash budget model, answer the following three questions: What is the projected amount of interest that the firm has to pay in the period Sept 2018 - Jan 2019? How much cash is the company expected to have at the end of January? Can the company repay the short-term loan at the end of January? Answer Yes/No and why. 15pts Conduct a Scenario Analysis to determine the impact on Final Ending Cash Balance by changing Sales commission to 10% and 25% 13 14 3 15 16 17 18 4
