Please provide excel worksheets with and without formulas. I will not be able to compelte this problem without them. if provided a thumbs up will be given.
Problem 9 Intro It is the beginning of January. Actual sales for the previous quarter (Q4) and estimated sales for the next five quarters are as follows (in $ million) Quarter Q4 Q1 Q2 Q3 Q4 Q1 Sales 61 63 68 70 73.2 75 You collect 30% of sales in the quarter of the sale, and the remainder in the next quarter. You expect to always spend 40% of the following quarter's sales on purchases of components from suppliers. You pay 60% of purchases in the quarter of the purchase, and the reminader in the next quarter. Wages and other expenses add up to 35% of each quarter's sales. You have to pay $4 million in interest and dividends each quarter, and plan to spend $60 million on new machinery in Q2. You start the year with $3 million in cash. Any additional short-term borrowing will be done at a quarterly interest rate of 1% Part 1 Attempt 1/10 for 10 pts. Add a worksheet "Collections" to the spreadsheet and some rows showing sales (Q4 to 24), beginning receivable balances, current sales collected and total cash collected for Q1 to Q4. What is your expected total cash collection in (future) Q4 (in $ million)? 70.96 Correct B D E F 1 Cash collections G N 3 Current sales collected 0.3 of current sales 4 Beginning receivables 0.7 of previous sales = 1-B3 5 6 Quarter 7 Sales 8 Beginning receivables 9 Current sales collected 10 Total cash collections Q4 Q1 Q2 Q3 Q4 61 63 68 70 73 2 in $ million 42.744.147 6 49 ESB$4 EZ 18.9 20.4 21 21.96 =SBS3F7 61.664.5 68.6 70.96 ESUM(F8 F9) Total cash collected in Q4 will be $70.96 million), Part 2 Attempt 1/10 for 10 pts. Add a worksheet "Disbursements" and some rows showing sales (Q1 to Q1), purchases (Q4 to 24), beginning payable balances, current purchases paid, wages & other expenses, interest and dividends, change in gross fixed assets and total cash disbursements for Q1 to Q4. What is your expected total cash disbursement in (future) 04 (in $ million)? 58.90 Correct A B D E LL G 1 Cash disbursements 2 Current purchases 3 paid 0.6 of current purchases 4 Beginning payables 0.4 =1-B3 of previous purchases of next sales 04 5 Purchases Wages & other 6 expenses 7 Interest & dividends 0.35 of current sales 4 8 Q4 Q1 Q2 Q3 Q4 Q1 68 70 73.2 75 28 29.28 30 $B$5G10 10.88 112 | 1171 =$B$41E 11 63 27 2 10.08 25.2 16.32 16 8 17 57 9 Quarter 10 Sales 11 Purchases 12 Beginning payables Current purchases 13 paid 14 Wages & other expenses 15 Interest payments 16 AGross fixed assets Total cash 17 disbursements 181 SB$3 F11 22 05 23.8 245 25.62 SB56 F10 les 4 4 4 41 $B$7 0 60 0 52.45 115 48 57 27 59 33 ESUM 7F12 F16) Total cash paid out in Q4 will be $59.33 (million), Part 3 Attempt 2/10 for 10 pts Add another worksheet to your spreadsheet and label it "Cash budget" Add rows for beginning cash balance, cash collections, cash disbursements and ending cash balance for Q1 to 04. What is your expected cash balance at the end of Q4 (in 5 million)? 1+ decimals Submit Part 4 Attempt 1/10 for 10 pts. Company policy is to borrow just enough every quarter to prevent the cash balance from becoming negative. Rename "Ending cash balance" to "Intermediate cash balance on worksheet "Cash budget" and add four rows for short-term debt issuance, ending debt, additional interest payments and ending cash balance. What is the ending cash balance in Q4 (in 5 million)? 1+ decimals Submit Part 5 - Attempt 1/10 for 10 pts. The company also has a policy of repaying short-term debt as much as cash flows allow. Insert another row for debt repayment between debt issuance and ending cash balance, and add two rows for beginning debt and ending debt. What is the ending cash balance in Q4 (in 5 million)? 0+ decimals Submit
