Of Sharpe's sales, 10 percent is for cash, another 60 percent is collected in the month following the sale, and 30 percent are collected in the second month following the sale. November and December sales for 2016 were $220,000 and $175,000, respectively. Sharpes raw materials cost 60 percent of its products final sales price and are purchased two months in advance of sale. The supplier is paid one month after it makes delivery. For example, purchases for April sales are made in February, and payment is made in March. In addition, Sharpe pays $10,000 per month for rent and $20,000 per month for other expenditures. Tax prepayments of $22,500 are made each quarter, beginning in March. The company's cash balance on December 31, 2016, was $22,000; a minimum balance of $15,000 must be maintained at all times. Assume that any short-term financing needed to maintain the cash balance is paid off in the month following the month of financing if sufficient funds are available. Interest on short-term loans (12 percent) is paid monthly. Borrowing to meet estimated monthly cash needs takes place at the beginning of the month. Thus, if the firm expects to need an additional $60,500 in April, these funds will be borrowed at the beginning of April, and the interest of $605 (i.e., .12 1/12 $60,500) owed for April will be paid at the beginning of May. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section.

Start Excel.

In cell F17, by using cell references, calculate the monthly interest rate on short-term loans. (1 pt.)

In cell D23, by using relative and absolute cell references, calculate the total cost of purchases in November. Copy the formula from cell D23 across columns E-K. (1 pt.)

In cell F27, by using relative and absolute cell references, calculate the cash received from sales made during January. Copy the formula from cell F27 across columns G-L. (1 pt.)

In cell F28, by using relative and absolute cell references, calculate the cash received in January from sales made during December. Copy the formula from cell F28 across columns G-L. (1 pt.)

In cell F29, by using relative and absolute cell references, calculate the cash received in January from sales made during November. Copy the formula from cell F29 across columns G-L. (1 pt.)

In cell F30, by using cell references, calculate the total cash receipts in January. Copy the formula from cell F30 across columns G-L. (1 pt.)

In cell F33, by using a cell reference, determine the payments for purchases in January. Copy the formula from cell F33 across columns G-L. Note: The outputs of the expression or function you typed in these cells are expected as positive numbers. (1 pt.)

In cell F37, by using cell references, calculate the total cash disbursements. Copy the formula from cell F37 across columns G-L. Note: The outputs of the expression or function you typed in these cells are expected as positive numbers. (1 pt.)

In cell F39, by using cell references, calculate the net change in cash for January. Copy the formula from cell F39 across columns G-L. (1 pt.)

In cell F40, by using a cell reference, determine the beginning cash balance in January. Copy the formula from cell F40 across columns G-L. (1 pt.)

In cell J41, by using absolute and relative cell references, calculate the interest payment on short-term borrowing in May if the cumulative borrowing in April is equal to the value specified in cell I48. If the cumulative borrowing is equal to zero interest should not be paid and zero value should be shown. Use a conditional statement written in the following way: IF(logical test #1, value if #1 true, value if #1 false), where logical test #1 checks if the value in cell I48 is greater than zero, value if #1 true is the product of the values in cells F17 and I48, value if #1 false is zero value. Note: for logical test #1 and value if #1 true enter the expressions describing the relationships between the cells given above. Equivalent forms will not be graded. Copy the formula from cell J41 to cell K41. Note: The outputs of the expression or function you typed in these cells are expected as positive numbers. (2 pt.)

In cell F42, by using cell references, calculate the ending cash balance before short-term borrowing in January. Copy the formula from cell F42 across columns G-L. (1 pt.)

In cell I44, by using cell references, calculate the new financing needed in April if the ending cash balance before short-term borrowing in this month is equal to the value specified in cell I42. If the ending cash balance is above the company's minimum balance zero value should be shown. Use a conditional statement written in the following way: IF(logical test #1, value if #1 true, value if #1 false), where logical test #1 checks if the value in cell I42 is less than the value in cell F13, value if #1 true is the difference between the values in cells F13 and I42, value if #1 false is zero value. (1 pt.)

In cell F45, by using cell references, calculate the cash balance before repayment in January. Copy the formula from cell F45 across columns G-L. (1 pt.)

In cell J46, by using absolute and relative cell references, calculate the repayment in May if the cumulative borrowing in April and the cash balance before repayment in May are equal to the values specified in cells I48 and J45, respectively. Zero value should be shown if the cumulative borrowing in April is equal to zero or the new financing is needed in May. Otherwise, the part or the whole amount of the borrowed funds should be shown depending on the amount of the cash balance before repayment. Use a nested conditional statement written in the following way: IF(logical test #1, value if #1 true, IF(logical test #2, value if #2 true, IF(logical test #3, value if #3 true, value if #3 false))), where logical test #1 checks if the value in cell I48 is equal to zero, value if #1 true is zero value, logical test #2 checks if the value in cell J44 is greater than zero, value if #2 true is zero value, logical test #3 checks if the difference between the value in cell J45 and values in cells F13 and I48 is greater than or equal to zero, value if #3 true is the reference to cell I48, value if #3 false is the difference between the values in cells J45 and F13. Copy the formula from cell J46 to cell K46. Note: The outputs of the expression or function you typed in these cells are expected as positive numbers. (2 pt.)

In cell F47, by using cell references, calculate the ending cash balance in January. Copy the formula from cell F47 across columns G-L. (1 pt.)

In cell I48, by using cell references, calculate the cumulative borrowing in April. Copy the formula from cell I48 to cell J48. Note: The outputs of the expression or function you typed in these cells are expected as positive numbers. (1 pt.)

In cell D52, determine whether the firm will have ample cash to repay $200,000 of notes payable in July. Note: Select your answer from the drop-down menu. (1 pt.)

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

a. Prepare a cash budget for Sharpe covering the first seven months of 2017. The Sharpe Corporation's projected sales for the first eight months of 2017 are as follows below. November $220,000.00 $54,000.00 December $175,000.00 $72,000.00 Sales (forecasted) Purchases (60% of sales price) January $90,000.00 $81,000.00 February $120,000.00 $144,000.00 March $135,000.00 $180,000.00 April $240,000.00 $162,000.00 May $300,000.00 $135,000.00 June $270,000.00 $90,000.00 July $225,000.00 August $150,000.00 Cash receipts Collections: On month after sale (10%) One month after sale (60%) Second month after sale (30%) Total cash receipts Cash disbursements Payments (one-month lag of purchases made the previous month) Rent Other expenses Taxes Total cash disbursements $10,000.00 $20,000.00 $0.00 $10,000.00 $20,000.00 $0.00 $10,000.00 $20,000.00 $22,500.00 $10,000.00 $20,000.00 $0.00 $10,000.00 $20,000.00 $0.00 $10,000.00 $20,000.00 $22.500.00 $10,000.00 $20,000.00 $0.00 Net change in cash for the period Plus: Beginning cash balance Less: Interest on short-term borrowing Equals: Ending cash balance before short-term borrowing $0.00 $0.00 $0.00 $0.00 $0.00 $15,000.00 $15,000.00 $15,000.00 $15,000.00 $15,000.00 $15,000.00 New financing needed Cash balance before repaym Repayment Ending cash balance Cumulative borrowing $0.00 $0.00 $0.00 ($15,000.00) $0.00 $22,000.00 $0.00 $15,000.00 $30,000.00 $45,000.00 $15,000.00 $30,000.00 b. Sharpe has $200.000 in notes payable due in July that must be repaid or renegotiated for an extension. Will the firm have ample cash to repay the notes? have ample cash to repay the notes in July. The firm