creating a cash budget 461172

Kelly’s Boutique is contemplating several means of financing their acquisition of $100,000 in special equipment. One alternative is to borrow $100,000 from a local bank for 10 years at 12 percent per annum. The bank has asked them to produce a 1-year cash budget broken down by months (January through December). Sales of $30,000 are expected in the first month, with each month thereafter increasing 2 percent. Purchases are based on an expected cost of sales of 55% and a required ending inventory of 70% of next month’s cost of sales. Beginning inventory was $11,000. Sales for January next year are expected to be $40,000. Sales in the previous November and December were $29,000 and $28,000, respectively. Expenses include advertising expense of $1000, depreciation expense of $800, interest expense of $1000, payroll expense of $8000, supplies expense of $500, and utilities expense of $600 per month throughout the year. All expenses except depreciation are paid in the month during which they are incurred. Collections in the month of sale are expected to be 50%, collections in the first month following a sale 40%, and in the second month 10%. Payments in the month of purchase are expected to be 75%, payments in the first month following a purchase 15%, and payments in the second month to be 10%. Purchases in the previous Nov and Dec were $16000 and $17000. Proceeds from the $100000 loan are expected in June, and $100000 of equipment will be purchased in July. The beginning cash balance in January was $22000. Please create a cash budget with formulas.