Please only answer question 4 (cash budget). Thank you.

Chapter 8- In Class Budgeting Example using Excel Students will submit their Excel file with Steps 1-5 completed via elearning no later than the start of class on Monday. Be sure to use formulas for Whor-1f type analysis (only given items should be hard coded into the file) Izzy's Ice Creams Izzy's Ice Creams is a neighborhood favorite for over 10 years. Izzy's prepares their own ice creams in house and since the pandemic, has only sold pints from the neighborhood location. 1. Sales Budget Based on historical averages and trends seen, the estimated sales are shown below by quarter. The average price per pint last year was $6/pint and there is not any expectation for a change in the pricing Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Total Sales in Units 40,000 60,000 70,000 80,000 250,00 For cash collections, on average 60% of credit sales are collected in the quarter sold and the remaining 40% is collected in the following quarter. Accounts receivable at the beginning of the quarter was $180,000 2. Production Budget Izzy's has invested in the newest technology for freezer and shorter term storage, so spoilage has not been an issue. They prefer to maintain 10% of the next quarter's sales in ending inventory. The beginning inventory on January 1 was 4,000 units. The Quarter 1 for the next year is estimated to have a Sales in Units of 44,000 Za. Direct Materials The Direct Materials budget is an estimate of raw materials needed to achieve the desired level of production. Assume 2 pounds of raw materials are required to produce one pint of product. The beginning raw materials inventory had 16,500 pounds available on January 1. The average cost per pound of inventory is $1.00 per pound The company wants to have 20% of raw materials on hand at the beginning of the quarter. Desired ending inventory hand should be 20,000 pounds. On average, 70% of purchases are paid in the quarter purchased and the remaining 30% in the following quarter Accounts payable at the beginning of the quarter was $30,000. 2b. Direct Labor The Direct Labor budget is an estimated of direct labor hours and related costs to achieve the desired level of production. It takes 10 direct labor hours (or 6 minutes) to produce 1 pint of ice cream The average fully loaded hourly rate is $13. 2c. Manufacturing Overhead Budget The Manufacturing Overhead budget is an estimate of all product costs (other than DM and DL) to achieve the desired level of production. (Notice how they are separated into variable and fixed costs.) Indirect materials per unit are $0.15 per unit. Indirect labor per unit is $.35 per unit. Fixed Overhead costs are $35,000 per quarter (and include salaries, rent and depreciation expenses). Depreciation expense is $10,000 per quarter. 3. Selling general and administrative Budget The SG&A budget is an estimate of all operating costs other than production Total SG&A budget (including salaries, rent, advertising, depreciation and other) is $60,000 per quarter, of which $3,000 is depreciation expense. 4 - Complete the Cash Budget The cash budget is divided into four sections: 1.Cash receipts section lists all cash inflows excluding cash received from financing 2.Cash disbursements section consists of all cash payments excluding repayments of principal and interest; 3.Cash excess or deficiency section determines if the company will need to borrow money or if it will be able to repay funds previously borrowed; and 4.Financing section details the borrowings and repayments projected to take place during the budget period Maintains a minimum cash balance of $30,000 and had no outstanding borrowings The company's beginning cash balance for the upcoming fiscal year is $30,000 The company requires a minimum cash balance of $30,000 and may borrow in increments of $5,000 as needed from a local bank at a quarterly simple interest rate of 2%. The company may borrow any amount at the beginning of any quarter and may repay its loans, or any part of its loans, at the end of any quarter. Interest payments are due on any principal at the time it is repaid Pays a cash dividend of $50,000 in Quarter 2 and Quarter 4 Purchases $20,000 of equipment in Quarter 1, $30,000 in Quarter 2 and $25,000 in Quarter 4 (purchases paid in cash) Assume equivalent assets were retired, so there is no impact on depreciation expenses. 5 - Complete the Budgeted Income Statement Izzy's produces accrual based (GAAP) financial statements. 4 Saw | Tamil Kaa 348 ) OS * ) Parlie allalaalais . a ta Mairi l | aasi taste paran E ) Cast trai N. INrailer tags Muring the with TO 21 1. +rat Sale (at are ) at a MUN pura M