Derby Company prepares monthly cash budgets. Relevant data from operating budgets for 2020 are: -use the following the complete the cash budget

Derby Company prepares monthly cash budgets. Relevant data from operating budgets for 2020 are: 5 Sales 6 Direct Material Purchases 7 Direct Labor 8 Manufacturing Overhead 9 Selling and Administrative Expenses 10 Depreciation 11 Interest Received 12 Sale of Investment Securities 13 Purchase of Land 14 Required Ending Cash Balance November $ 201,300 $ 101,100 $ 80,000 $ 62,000 $ 83,000 $ 80,000 $ 6,200 $ 5,000 $ - $ 60,000 December $ 292,375 $ 91,776 $ 90,000 $ 65,000 $ 85,000 $ 80,000 $ 6,000 $ 5,200 $ - $ 60,000 January February $ 351,240 $ 401,213 $ 111,375 $ 119,773 $ 85,000 $ 115,000 $ 60,000 $ 75,000 $ 75,000 $ 80,000 $ 80,000 $ 80,000 $ 6,300 $ 6,100 $ 3,000 $ 2,000 18-May-27$ 15,000 $ 40,000 $ 40,000 NOTE: All items, except DM purchases, are completely paid in the month incurred. 16 Reference for "$0" $0 Sales Collection Percentages 19 Month of sale 20 First Month Following the Sale 21 Second Month Following the Sale 55% 30% 15% DM Payment Percentages 24 Month of purchase 30% 25 Month following the purchase 70% 26 The following was used to complete the Cash Budget 29 Beginning Cash Balance 30 Borrowings 31 Collections from Customers 32 Depreciation 33 Direct Labor 34 Direct Materials 35 Interest Received 36 Manufacturing Overhead 37 Purchase of Land 38 Repayments 39 (Required) Ending Cash Balance 40 Sale of Investment Securities 41 Selling and Adminstrative Expenses 42 A INSTRUCTIONS: Part 1 Use formulas, cell referencing, rounding function and the information on the "Given Info" worksheet to complete the "Schedule of Expected Collections from Customers" and the "Schedule of Expected Payments for DM purchases." Make sure that you cell reference the collection percentages. Do not hardcode a percentage (i.e. =55%*A1). When you need a "$0", please make sure that you cell reference the appropriate cell on the Given Info tab. DO NOT hardcode the $0. INSTRUCTIONS: PART 2 The Cash Budget is partly filled in and automatically completed as the Cash Collections and Cash Payments are completed, except for the blue, purple, and pink cells. Start at row 17 and complete all cells (that are not gray) down through cells K33 and L33. Then complete cells N37 and 037 using IF Statements, as follows: Use the =IF function in cell N37 to display "Repay" or "Borrow" based on the data in cells K33 and K39. Use the =IF function in cell 037 to display "Repay" or "Borrow" based on the data in cells L33 and L39. Note: You must use cell referencing as part of your =IF statements, except you can type the words "Repay" and "Borrow." If you hardcode numbers, your =IF statement will be incorrect. After you have determined whether to repay or borrow using IF statements, then complete cells K36-37, and then cells L36-37. Derby Company Cash Budget For the Two Months Ending February 29, 2020 January $60,000 February $40,000 In the light blue cells, cell reference the "Given Info" tab. Do NOT copy or type the numbers! Add: Cash Receipts Schedule of Expected Collections from Customers. Cash Collected in: Sales January February November December January February Total Interest Received Sale of Investment Securities Total Receipts Total Available Cash $6,300 $3,000 $9,300 $6,100 $2,000 $8,100 Less: Cash Disbursements (Payments) For the PURPLE cells - Make sure that the purple cells do NOT reference the "Given Info" tab, but instead use formulas or numbers from cells on this tab. Schedule of Expected Payments for DM Purchases Cash Paid for DM in: Purchases January February December January February Total Direct Labor Manufacturing Overhead Selling and Adminstrative Expenses Purchase of Land Total Disbursements $85,000 $60,000 $75,000 $10,000 $115,000 $75,000 $80,000 $15,000 Excess (deficiency) of available cash over cash disbursements Does Derby Co. need to Borrow money or should it Repay debt? (Short Term) Financing Borrowings Repayments January NOTE: Any cash in excess of the required ending cash balance should be used to pay off a $200,000 long term loan. (This loan is not shown as part of the Given Info.) February (Required) Ending Cash Balance $40,000 $40,000 For the Pink cells - Either reference the "Given Info