USE THE ABOVE CHART TO HELP SOLVE FOR THE BOTTOM SCREENSHOT PLEASE SHOW WHAT FORMULA WAS USED TO GET EACH ANSWER!!!! THANK YOU

To complete the cash budget, you will have to work on the schedules of expected cash collections and payments first. Please set formulae by referencing to the data in Excel spreadsheets. .

The following table lists the data from the budget of Ritewell Publishers. 55% the companys sales are for cash on the nail; the other 45% are paid for with a one-month delay. The company pays all its credit purchase with a one-month delay. Credit purchases in January were $30,000, and total sales in January were $180,000.

We make these assumptions to simplify the analysis:

* Ritewell Publishers has an open line of credit with its bank, which can be used as needed to bolster the cash position.

* The company desires to maintain a $18,000 minimum cash balance at the end of each month. Therefore, borrowing must be sufficient to cover the cash shortfall and to provide for the minimum cash balance of $18,000.

* All borrowings and repayments must be in multiples of $1,000 amounts, and interest is 6 percent per annum.

* Interest is computed and paid on the principal during the borrowing period. 2

* All borrowings take place at the beginning of a month, and all repayments are made at the end of a month. The company will take a loan at the beginning of February, repaying part of the loan at the end of March and the remaining balance at the end of April. At the end of March, the company will repay the loan as much as possible but also keeps the minimum cash balance of $18,000. Repayment in March is based on available cash from operating activities. You have to figure out the loan amount and repayment amounts.

* The company pays the selling and administrative expenses, income taxes, dividends, and pays for equipment purchase at the end of a month.

Please refer to the example of cash budget in chapter 6 Excel file posted on Blackboard. The interest payment made at the end of March is computed as: Principal of the loan x Interest rate on the loan x borrowing period . The interest payment made at the end of April is computed as: Remaining principal of the loan x Interest rate on the loan x borrowing period . Because the interest rate on the loan is an annual rate, the borrowing period will be adjusted to a fraction of 12 months. Part of the screenshot is attached for your reference. The explanations for most of the items have been inserted as comments in the Excel file.

NA 332 Project 2 dats Microsoft Excel EN Engith (United States Hone Insert Pege Leyout Fernulas Dete Review View Wrap Text Good check cell input Linked Cell 4 Total sales 180,000 $200,000 S 220,000 180,000 Month Purchases of material 6 March April For cash For credit $18,000 70,00080,000 60,000 0,000 40,000 30,000 40,000 30,000 4,000 14,00014,000 3,000 3,000 3,00 Cash balance, beginning Add: Receipts: $191,000 $209,000 0,00030,000 Collections from customers Total cash available (a) 8 Selling and admin. expenses 9 Income taxes 10 Dividends 11 Equipment purchase 100,000 Disbursements: Direct materials Selling and admin. Equipment purchase Dividends Income tax 100,000 $30,000 Schedule of Expected Cash Collections February MarchApril $14,000 17 Accounts receivable, 1/31/2015 18 February sales ($200,000) 9 March sales ($220,000) 20 April sales($180,000) 21 Total cash collections Total disbursements (b) Minimum cash balance Total cash needed [c] Cash surplus (deficit) (a) - [c] Finance: $18,000 $265,000 110,000 90,000 121,000 99,000 99,000 191,000 211,000 198,000 Borrowing Repayment Interest Total effect of financing (d) Cash balance, ending (a)-(b)+(d) Schedule of Expected Cash Payments February March April 30,000 70,000 40,000 27 Accounts payable, 1/31/2015 28 February purchases 29 March purchases 0 April purchases 31 Total cash disbursement 80,000 30,000 60,000 100,000 120,000 90,000