Super Scooper Budget Case- Bill owns Super Scooper, the local ice cream shop. He wants your help as he sets up his budget for next spring: April, May and June 2021. Here is what he expects to start out with as he starts April: $6,500 3,990 14,400 24,890 Pro Forma Balance Sheet 31-Mar-21 Assets Bill charges his customers $5 per ice cream and customers Cash generally pay cash when they get the ice cream. Here are the Inventory anticipated sales in units for each month:- Equipment 18,000 Accumulated Depreciation 3,600 Budgeted # of ice | Total Assets Month cream solde 1,700 Liabilites & Equity May 2,200 Liabilities June 2,800 Utilities Payable Unearned Revenue Each May 1", the Ray the Real-estate agent sponsors a special Total Liabilities event where ice cream is served. Ray will prepay for this event Equity before March 31st (hint: see the Balance Sheet). The 100 cones Common Stock for the May 1st event are included in the 2,200 monthly total. Retained Eamings Total Equity The cost to buy the ice cream and cone is $1.90 each. Total Liabilities & Equity April 140 500 640 20,000 4,250 24,250 $24,890 The yearly insurance will need to be paid for at the beginning of April. This 12 month policy will cost $2,400. Monthly utilities are budgeted as follows; April $180, May $200, June $230. Utility bills are paid on the 15th of the next month (the month after they are used). Ray will need to re-stock his inventory of ice cream and cones. He plans to buy $4,750 of ice cream at the beginning of May and $5,700 in June. His supplier requires that he pays for 20% of the ice cream in the month of purchase and the remainder is paid in the next month. Two employees work at the store and get paid $2000 each per month. As it will get busier in June, Bill plans to hire an additioal part time employee for $500 in that month. Wages are paid in the month worked. Monthly rent is $1800 and is paid at the 1st of each month. Depreciation on the existing equipment is $150 per month. The store will also need a new freezer in June. The cost of the freezer will be $2,504. They plan to use it for 4 years, then sell it for $200.- Required: In excel, create an operating budget (budgeted income statement) and a cash budget for April, May and June. Also make a budgeted balance sheet for June 30, 2021. Follow the following format: Note, I know this assignment will be challenging and I encourage you to collaborate with your friends. Collaboration is when all people participate equally to solve a problem. Do not copy the work of others. I will know and you put your friends in an uncomfortable and dangerous position. A 5% assignment is not worth that risk as I will report all cases to Student affairs and it will go on your permanent record. Using Excel, follow this format (you don't need to use these celours): Make sure to link all cells April May June Total Hint: You should have 5 operating expenses Sales Cost of Goods Sold Gross Profit Total Operating expense Net Income Cash Budget April May June Beginning Cash Cash Receipts Total Cash Available Cash Disbursements Total Cash Disbursements Ending Cash Pro Forma Balance Sheet 30-Jun-21 Assets Hint: You should have 4 assets and a contra asset. There are 2 liabilities Total Assets Liabilities & Equity Liabilities Total Liabilities Equity Total Equity Total Liabilities & Equity $1,162 Check Figures: Total budgeted Net Income for the 3 months Ended June 30, 2021 Projected Cash at the end of October $ 10,286 Total Assets $30,202 t t