Problem 20 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 Sales 41 63 68 70 49.2 You collect 30% of sales in the quarter of the sale, and the remainder in the next quarter. You expect to always spend 30% of the current quarter's sales on purchases of components from suppliers. You pay 80% of purchases in the quarter of the purchase, and the reminader in the next quarter. Wages and other expenses add up to 40% of each quarter's sales. You have to pay $6 million in interest and dividends each quarter. You start the year with $5 million in cash. Part 1 | Attempt 1/10 for 10 pts. Add a worksheet "Collections" to the spreadsheet and some rows showing sales (Q4 to Q4), 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)? 0+ decimals Part 2 Attempt 1/10 for 10 pts. Add a worksheet "Disbursements" and some rows showing sales (Q1 to Q1), purchases (Q4 to Q4), beginning payable balances, current purchases paid, wages & other expenses, interest and dividends, capital expenditure and total cash disbursements for Q1 to Q4. What is your expected total cash disbursement in (future) Q4 (in $ million)? 1+ decimals Submit Part 3 Attempt 1/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 Q4. What is your expected cash balance at the end of Q4 (in $ million)? 1+ decimals Submit