Quiz-3 Note: All Calculations should be in Excel and please submit an Excel file A sales budget is given below for one of the products manufactured by the Key Co.: January 21,000 February 36,000 March 61,000 April 41,000 May 31,000 June 25,000 The inventory of finished goods at the end of each month should equal 20% of the next month's sales. However, on December 31, the finished goods inventory totalled only 4,000 units. Each unit of product requires three specialized electrical switches. Since the production of these specialized switches by Key's suppliers is sometimes irregular, the company has a policy of maintaining an ending inventory at the end of each month equal to 30% of the next month's production needs. This requirement had been met on January 1 of the current year. The cost of each electrical switch is $5. The company maintains a separate bank account to recover cost of switches and to pay for the switches. Cash is deposited in this bank account at second last day of every month equivalent to cost of switches related to that months sales. Example: in November previous year, Key Co sold 20,000 units. Cost of switches for November sales was $300,000 (20,000 units x 3 switch/unit x $5/switch) and it was deposited in the bank account on Nov 29. The company pays for the switches in the month of purchase. All payments are made on the last day of the month. If there is any shortage of fund in the account, the company borrows it from the bank. The company policy is to maintain a minimum balance of $100,000 in the bank account at the end of each month, but it is expected that on Jan 1, the balance in this account will be $85,000. Repayment of the loan on this account is made from a separate bank account of the company. Required: 1. Prepare a budget showing the quantity of switches to be purchased each month for January, February, and March, and in total for the quarter. 2. Prepare a cash collection schedule for the bank account related to switches for January, February and March. 3. Prepare a cash disbursement schedule for the switch purchases for January, February and March 4. Prepare a cash budget showing Excess/Deficiency and Borrowing (if any) related to the switch purchase for the bank account