Carefully study the following cash budget and determine the amount to be borrowed or repaid during...

Transcribed Image Text:

Carefully study the following cash budget and determine the amount to be borrowed or repaid during each quarter, so that the company never ends a quarter with less than $50,000 cash on hand, and always repays as much debt as possible. There is no debt as of the beginning of the first quarter, and interest costs are assumed not significant in this analysis. Use the pick lists associated with the boxed areas near the bottom of the spreadsheet to determine the right amounts to borrow and repay each quarter. Correct choices will result in the boxed areas turning green. Cash budget First Quarter Second Quarter Third Quarter Fourth Quarter Beginning cash $ 10,000 $ 52,500 $ 59,500 $ 62,500 Customer receipts 140,000 160,000 200.000 250,000 Available cash $ 150,000 212,500 $ 259,500 312,500 Less: disbursements Direct materials $ 28,000 $ 30,000 $ 41,000 $ 48,000 Direct labor 14,000 16,000 20,000 25,000 Factory overhead 13,500 14,000 15,000 17,000 SG&A 31,000 31,000 33,000 33,000 Other expense and tax 11,000 12,000 13,000 15,000 Equipment purchases 250,000 225,000 Total disbursements $ 97,500 $ 353,000 $ 122,000 $ 363,000 Cash surplus (deficit) $ 52,500 $ (140,500) $ 137,500 $ (50,500) Financing: Planned borrowing $ $ 200,000 $ $ 125,000 Question 1 Itranscript Planned repayment $ $ $ Ending cash balance $ 52,500 $ 59,500 $ (75,000) $ 62,500 $ 74,500 1 pts Alternatively, how much will be the minimum borrowing for the second quarter necessary to satisfy the $50,000 minimum cash required balance? Question 2 transcript Alternatively, how much will be the Maximum loan pay-off amount for the third quarter necessary to satisfy the $50,000 minimum cash required balance? Question 3 transcript 1 pts 1 pts Alternatively, how much will be the minimum borrowing for the fourth quarter necessary to satisfy the $50,000 minimum cash required balance? Question 4 transcript 1 pts Based on the Excel Cash Budget you prepared with all green cells on, what is the loan balance at the end of fourth quarter? 250,000 Carefully study the following cash budget and determine the amount to be borrowed or repaid during each quarter, so that the company never ends a quarter with less than $50,000 cash on hand, and always repays as much debt as possible. There is no debt as of the beginning of the first quarter, and interest costs are assumed not significant in this analysis. Use the pick lists associated with the boxed areas near the bottom of the spreadsheet to determine the right amounts to borrow and repay each quarter. Correct choices will result in the boxed areas turning green. Cash budget First Quarter Second Quarter Third Quarter Fourth Quarter Beginning cash $ 10,000 $ 52,500 $ 59,500 $ 62,500 Customer receipts 140,000 160,000 200.000 250,000 Available cash $ 150,000 212,500 $ 259,500 312,500 Less: disbursements Direct materials $ 28,000 $ 30,000 $ 41,000 $ 48,000 Direct labor 14,000 16,000 20,000 25,000 Factory overhead 13,500 14,000 15,000 17,000 SG&A 31,000 31,000 33,000 33,000 Other expense and tax 11,000 12,000 13,000 15,000 Equipment purchases 250,000 225,000 Total disbursements $ 97,500 $ 353,000 $ 122,000 $ 363,000 Cash surplus (deficit) $ 52,500 $ (140,500) $ 137,500 $ (50,500) Financing: Planned borrowing $ $ 200,000 $ $ 125,000 Question 1 Itranscript Planned repayment $ $ $ Ending cash balance $ 52,500 $ 59,500 $ (75,000) $ 62,500 $ 74,500 1 pts Alternatively, how much will be the minimum borrowing for the second quarter necessary to satisfy the $50,000 minimum cash required balance? Question 2 transcript Alternatively, how much will be the Maximum loan pay-off amount for the third quarter necessary to satisfy the $50,000 minimum cash required balance? Question 3 transcript 1 pts 1 pts Alternatively, how much will be the minimum borrowing for the fourth quarter necessary to satisfy the $50,000 minimum cash required balance? Question 4 transcript 1 pts Based on the Excel Cash Budget you prepared with all green cells on, what is the loan balance at the end of fourth quarter? 250,000