Question
Kelly's Boutique is contemplating several means of Fnancing their acquisition of $200,000 in special equipment. One alternative is to borrow $200,000 from a local bank
Kelly's Boutique is contemplating several means of Fnancing their acquisition of $200,000 in special equipment. One alternative is to borrow $200,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 $40,000 are expected in the Frst month, with each month thereafter increasing 2 percent. Purchases are based on an expected cost of sales of 55 percent and a required ending inventory of 70 percent of next month's cost of sales. Beginning inventory was $11,000. Sales for January next year are expected to be $50,000. Sales in the previous November and December were $29,000 and $28,000, respectively. Expenses include advertising expense of $900, depreciation expense of $800, interest expense of $1,000, payroll expense of $8,000, supplies expense of $500, and utilities expense of $600 per month throughout the year. All expenses except depreciation are paid in the monthduring which they are incurred. Collections in the month of sale are expected to be 60 percent, collections in the Frst month following a sale 30 percent, and in the second month 10 percent. Payments in the month of purchase are expected to be 75 percent, payments in the Frst month following a purchase 15 percent, and payments in the second month to be 10 percent. Purchases in the previous November and December were $16,000 and $17,000, respectively. Proceeds from the $200,000 loan are expected in June, and $200,000 of equipment will be purchased in July. Monthly payments of $1,400 on the loan also begin in July. The beginning cash balance in January was $15,000.Using the ch6-04 Fle to start your work, create a cash budget that is based on the assumptions listed in the previous paragraph. Use Excel's grouping feature to group operating cash receipts, operating cash payment,cash from (to) operating activities, cash from (to) investing activities, and cash from (to) Fnancing activities and also to group the twelve monthly columns together. (Use equations in all cells, do not just put in numbers within the cell) i need answer with excel use with formulas please
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started