ACCounting-Cash Budgets determining the cash surplus and shortage. FIN 5130

Cash Budgets determining the cash surplus and shortage. FIN 5130Quiz 5 Extra CreditInstructor: Dr. PalkarLate assignments will not be accepted so please plan accordingly. You need to showyour working notes for credit. You must submit your work using excel files (with. xls or . xlsx for credit). You must upload your files on Blackboard under theAssignment Dropbox for credit. This assignment will require you to prepare the cash budget and determine the cashsurplus and shortage each month. The management estimates total sales for the period January through July based on actualsales from the immediate past quarter. The following assumptions are made: Sales: Past and ExpectedHistoricalForecastOctober$300,000 JanuaryNovember $350,000 FebruaryDecember $400,000 MarchAprilMayJuneJuly$150,000$200,000$200,000$300,000$250,000$200,000$300,000a. Sales are 75% on credit basis and 25% on cash basis. 60% of credit sales arecollection one month after sale, 30% in the second month, and 10% in the thirdmonth. Bad debts are insignificant. b. Purchases are 80% of sales and are paid as follows: 50% of sales are paid in the samemonth and the remaining 50% are paid in the following month. c. Wages and salaries are as follows: JanuaryFebruaryMarchAprilMayJuned. e. f. g. h. $30,000$40,000$50,000$50,000$40,000$35,000Rent is $2,000 per monthInterest on $500,000 of 16% bonds is due on the calendar quarter. A tax prepayment of $50,000 is due in April. A capital addition of $30,000 is planned in June. The company has a cash balance of $100,000 on January 1st, which is the minimumbalance maintained each month. 1. Prepare cash budget for Jan to June. 2. Determine the cash surplus and shortages for each month. Page 1 of 1