Please show excel formula references/formulas

First Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2020. Balance Sheet March 31, 2020 Assets Cash Accounts receivable Inventory Prepaid Insurance Net Fixtures Total Assets 2,000 25,000 29,400 2,000 25,000 83,400 Liabilities 25,480 6,000 31,480 Accounts payable Dividends payable Total Liabilities Stockholders' Equity Common Stock Retained Earnings Total liabilities and Stockholders' equity $ 14,920 37,000 51,920 83,400 $ Sales Revenue Month January February March April May June 60,000 50,000 40,000 50,000 60,000 70,000 90,000 25,000 Wages and salaries Depreciation Utilities Rent Prepaid insurance is for following months 1,000 2,000 4 Months Cash dividends are declared during the third month of each quarter and are paid during the first month of the following quarter = $6,000. Cash dividends are declared during the third month of each quarter and are paid during the first month of the following quarter. = 6,000 Operating expenses, except insurance and depreciation are paid monthly. Cost of goods sold is equal to Ending Inventories are sufficient for 49% 120% of sales revenue in month of sale of next month's cost of sales Purchases during any given month for merchandise are paid in full during the following month. All sales are on account. 50% 40% 10% collected during month of sale collected during the next month after sale collected the second month after sale $1,000 $ 2,000 at an annual interest rate of 12% or monthly of 1.00% at beginning of each month New Loans will be repaid if excess cash is over the required minimum cash balance in increments of The company requires a minimum cash balance of At the time the principal is repaid, interest is paid on the portion of principal that is repaid. All borrowing is at the beginning of the month, and all repayment is at the end of the month. Therefore, interest is incurred the month of the borrowing and thereafter until paid at the end of the month when there are sufficient funds. Required: Prepare and answer the following budgets and questions on the Part1Budget Solution Worksheet. Make sure you use cell referencing and/or formulas. Please note that the quarter total column does not necessarily mean you total a row. When there are beginning and ending balances you need to show the beginning or ending balance for the quarter. For example, please review Exhibit 22.5 purchase budget page 22-10 the desired ending inventory and beginning inventory quarter total. Round to the nearest dollar. 1. Prepare a purchase budget for each month of the second quarter ending June 30, 2020 along with a quarter column. 2. Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2020 along with a quarter column. Do not include borrowings. 3. Prepare a cash disbursements schedule for each month of the second quarter ending June 30, 2020 long with quarter column. Do not include repayments of borrowings. 4. Prepare a cash budget for each month of the second quarter ending June 30, 2020. Include budgeted borrowings and repayments if applicable. 5. Prepare a budgeted income statement for each month of the second quarter ending June 30, 2020 along with a quarter column. 6. Prepare a budgeted balance sheet as of June 30, 2020. 7. What if the company is able to decrease the annual interest rate on loans to 10%. Which budgets will change and what will be the new budgeted interest expense for the quarter ending June 30, 2020? You should only have to change the annual interest rate on this worksheet and all the appropriate budgets will change on the solution worksheet if you have set up your cell references correctly. Please make sure you return the annual interest rate back to the original percentage before you submit your solution. Budget #1 First Store Purchase Budget For the quarter ending June 30, 2020 Quarter Total April May June 50,000 Budgeted Sales (As provided on Part 1) Current cost of goods sold Desired ending inventory Total Inventory needs Less beginning inventory Budgeted purchases Budget #2 First Store Schedule of Budgeted Cash Collections For the quarter ending June 30, 2020 April May Quarter Total June 50% of current month's sales 40% of last month's sales 10% of sales two months prior Total cash receipts Budget #3 First Store Schedule of Cash Disbursements For the quarter ending June 30, 2020 Quarter Total April May June Purchases of merchandise prior month Current cash operating expenses Cash Dividends Total cash disbursements Budget #4 First Store Cash Budget For the quarter ending June 30, 2020 Quarter May June Total April Cash balance, beginning Total Cash receipts (see budget #2) Total Cash disbursements (see Budget #3) Excess(deficiency) cash available over disbursements Short-Term Financing: New Loans Repayments Interest Cash balance, ending Budget #5 First Store Budgeted Income Statement For the quarter ending June 30, 2020 Quarter June Total April May Sales Cost of Sales Gross Profit Expenses: Wages and salaries expense Depreciation expense Utilities expense Rent Expense Insurance Expense Interest expense Total Expenses Net income Budget #6 First Store Budgeted Balance Sheet June 30, 2020 Assets Cash Accounts receivable Inventory Prepaid Insurance Net Fixtures Liabilities Accounts payable Dividends payable Loan payable Interest payable Total Liabilities Stockholders' Equity Common Stock Retained Earnings Total liabilities and Stockholders' equity Total Assets