MUST USE CELL REFERENCE
A B E F G H H 1 J 3 You need to use cell references in the development of your budgets. 4 You must use this worksheet to reference the data that is being inputted onto the budgets on the budget worksheet 5 If you type in any 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. 6 You should use this worksheet as your data field and only use cell references and formulas in your budgets. 7 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 B The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you 9 have used cell references and formulas throughout 10 11 First Store prepares budgets quarterly. The following information is available for use in planning the second 12 quarter budgets for 2022. 13 14 First Store 15 Balance Sheet 16 March 31, 2022 17 18 Assets Liabilities 19 Cash S 3,000 Accounts payable 26,520 20 Accounts receivable 25,000 Dividends payable 4,000 21 Inventory 28,600 Total Liabilities S 30,520 22 Prepaid Insurance 1,800 23 Net Fixtures 24,000 Stockholders' Equity 24 Common Stock $ 15,880 25 Retained Earnings 36,000 51,880 26 Total Assets S 82,400 Total liabilities and Stockholders' equity S 82,400 27 28 Actual and forecasted sales for selected months in 2022 are as follows: 29 Month Sales Revenue 30 January S 60,000 31 February 50,000 32 March 40.000 33 April 50,000 34 May 60,000 35 June 70,000 36 July 90,000 37 38 Monthly operating expenses are as follows, but note you will need to compute the monthly insurance expense: 39 Wages and salaries S S 23,000 40 Depreciation 300 Utilities 1,500 42 Rent 2,500 43 Prepaid insurance was made for the upcoming 6 Months G H A B D E F 44 45 Cash dividends are declared during the third month of each quarter and are paid during the first month of the following quarter: 4,000 46 Operating expenses, except insurance and depreciation are paid monthly. 47 48 Cost of goods sold is equal to 52% of sales revenue in month of sale 49 Ending Inventories are sufficient for 110% of next month's cost of sales 50 Purchases during any given month for merchandise are paid in full during the following month. 51 All sales are on account. 50% collected during month of sale 52 40% collected during the next month after sale 53 10% collected the second month after sale 54 The company requires a minimum cash balance of $ 3,000 at beginning of each month 55 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. 56 New Loans will be repaid if excess cash is over the required minimum cash balance in increments of $1,000 at an annual interest rate of 57 At the time the principal is repaid, interest is paid on the portion of principal that is repaid. 58 All borrowing is at the beginning of the month, and all repayment is at the end of the month. 59 Therefore, interest is incurred on the principal loan balance 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. 12% or monthly of 1.00% an B D E F A 35 36 Budget #4 37 38 First Store Cash Budget For the quarter ending June 30, 2022 Quarter May June Total 3,000 $ 3,000 $ 3,000 $ 3,000 46,000 54,000 64,000 164,000 57,520 58,720 63,920 180, 160 39 40 April $ 41 42 43 $ 8,520 $ 1,720 $ 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 (3,080) $ 13,160 44 45 46 47 48 49 50 Budget #5 51 52 First Store Budgeted come Statement For the quarter ending June 30, 2022 Quarter May June Total April 53 54 55 56 57 58 59 60 61 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 62 63 64 65 66 67 bo Include 3. Prepare a cash disbursements Schedule for each ILIT UI e second quarter ending une 30, along with quarter Colu 69 4. Prepare a cash budget for each month of the second quarter ending June 30, 2022. Include budgeted borrowings and repayments if applicable. 70 5. Prepare a budgeted income statement for each month of the second quarter ending June 30, 2022 along with a quarter column
