1. 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 isyour expected total cash collection in (future) Q4 (in $ million)?
2. Add a worksheet "Disbursements" and some rows showing sales (Q1 to Q1),purchases(Q4 to Q4), beginning payablebalances, current purchases paid, wages & other expenses, interest and dividends, capital expenditure and total cash disbursements forQ1 to Q4.What isyour expected total cash disbursement in (future) Q4 (in $ million)?
3. 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 isyour expected cash balance at the end of Q4 (in $ million)?
4. Company policy is to borrow just enough every quarterto 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 $ million)?
5. The company also has a policy of repaying short-term debt as much as cash flows allow.Insertanotherrow for debt repaymentbetween debt issuance and ending cash balance, and add two rows for beginning debt and ending debt. What is the ending cash balancein Q4 (in $ million)?

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): 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 90% 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 $70 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%