If you type in ang numbers in the solution and/or do not use this worksheet as your data worksheet. I will take off 50% of the total points for this part, since we use Excel so that we can update budgets or do what if analysis without retyping numbers. You should use this worksheet as your data field and only use cell references and formulas in your budgets. Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retupe the budgets if you have used cell references and formulas throughout. First Store prepares budgets quarterly. The following information is available for use in planning the second quarter budgets for 2021. First Store Balance Sheet March 31, 2021 Assets Liabilities $ Cash Accounts receivable Inventory Prepaid Insurance Net Fixtures 25,480 5,000 1,000 25,000 29,400 1,800 25,000 Accounts payable Dividends payable Total Liabilities $ 30,480 Stockholders' Equity Common Stock Retained Earnings Total liabilities and Stockholders' equity $ 15,720 36,000 Total Assets 82.200 51720 82.200 $ Sales Revenue Actual and forecasted sales for selected months in 2021 are as follows: Month January February March April June July 60,000 50,000 40,000 50,000 60,000 70,000 90,000 Monthly operating expenses are as follows, but note you will need to compute the monthly insurance expense: Wages and salaries $ Depreciation Utilities Rent Prepaid insurance was made for the upcoming 25,000 200 1,000 2,000 6 Months 5,000 Cash dividends are declared during the third month of each quarter and are paid during the first month of the following quarter: Operating expenses, except insurance and depreciation are paid monthly. Cost of goods sold is equal to 49% of sales revenue in month of sale Ending Inventories are sufficient for 120% 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% collected during month of sale 40% collected during the next month after sale 10% collected the second month after sale The company requires a minimum cash balance of $ 1,000 at beginning of each month If there is a cash deficiency the company will take out short-tem financing so that the ending balance at the end of the month will meet the company requirement for a minimum cash balance. New Loans will be repaid if excess cash is over the required minimum cash balance in increments of $1,000 at an annual interestral 12% or monthly 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 is sufficient funds to pay off more than $1,000. 1.00% 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 rov. Vhen 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, 2021 along with a quarter column. 2. Prepare a cash receipts schedule for each month of the second quarter ending June 30, 2021 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, 2021 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, 2021. Include budgeted borrowings and repayments if applicable. 5. Prepare a budgeted income statement for each month of the second quarter ending June 30, 2021 along with a quarter column. 6. Prepare a budgeted balance sheet as of June 30, 2021. 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, 2021? 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. OverviewProjects Part 1 Part1BudgetSolution Part 2 Part 3 Part 4 Sheet1