Brittney's Boutique Brittney Doby started her own local boutique in Waco, Texas after working for another boutique owner for several years and developing her own traveling custom-clothing business. Brittney leased a space in downtown Waco beginning on November 1, 2016. She named her store Brittney's Boutique and actually opened for business on November 20, 2016. During November, Brittney conducted the following activities: 1. Signed a 36 month lease agreement for $3,000 per month (fixed cost) and paid a lease deposit of $3,000 along with the first month's rent. Rent is due on the first day of each month 2. Paid a utility deposit for electricity, $1,000 3. Paid a utility deposit for water & gas, $500 4. Spent $27,000 on leasehold improvements; amortized over 15 years 5. Spent $12,000 on furniture & fixtures, 10 year useful life 6. Invested $25,000 of her own money into the business 7. Borrowed $50,000 from Central National Bank to be paid back over 60 months at 7% interest (The monthly payment of principal and interest totals $990.06. Assume the first monthly payment is due at the end of November. Prepare an amortization schedule to determine monthly interest payments.) 8. Purchased and used $1,000 worth of supplies in each of her first 3 months of operations (paid for at the time of purchase) 9. Annual advertising campaign costing $24,000 will be paid in equal monthly payments. 10. Purchased a computer and point-of-sale software in November for $3,000. The computer was financed interest free for 12 months with even monthly payments. The computer is expected to last 3 years. 11. Had gross sales of $30,000, $90,000, and $40,000 respectively during her first 3 months of operations. Customers pay for purchases using cash 40% of the time and credit/debit cards for the other 60%. Brittney is charged 1.5% for a processing fee (variable cost) on all credit/ debit card transactions. Credit card sales are collected in the month of the sales and in the month following the sale. 12. Cost of goods sold is 48% of sales revenue. 75% of inventory purchases are paid for in the month of the purchase and the other 25% are paid for in the following month. 13. The desired ending inventory for each month is 75% of the following month's cost of goods sold. Sales for February are expected to be the same as January. 14. Employee salaries of $3,500 (fixed cost) are paid each month. 15. Insurance is paid monthly, $700 (fixed cost). 16. Miscellaneous monthly utilities such as water, gas, and electricity are approximately $650 per month. (In reality, these would likely be mixed costs, as they contain both fixed and variable cost components. However, assume for this problem, that these costs are all fixed.) Brittney's Boutique Please prepare the following, all in Excel, and upload them to Canvas in one Excel file, with six separate tabs, in order, with each labeled #1 - #6. Only one Excel file per group should be uploaded. To submit Brittney's Boutique, please have the designated "Uploader" for your group do the following within Canvas: Assignments tab > Brittney's Boutique > Submit Assignment > Choose File to upload In the comments section, include all group members' first and last names. In order to receive full credit, tabs must link together and calculations must be done within Excel (i.e. auto-sum formulas). 1. Prepare a traditional format income statement for each month from November-January and for the total of the first quarter (Nov-Jan). Include amortization schedule discussed in #7. List all operating (SG&A) expenses separately. 2. Prepare a schedule of expected cash collections for each month from November-January and for the total of the first first quarter. 3. Prepare an inventory purchases budget for each month from November-January and for the total of the first first quarter. 4. Prepare a schedule of expected cash disbursements budget for inventory purchases for November January and for the total of the first quarter. 5. Prepare a cash budget (not a schedule of cash flows) for November-January and for the total of the first quarter. Do not combine disbursements together; rather, list all cash disbursements as separate line items. Prepare a contribution margin format income statement (by identifying each item on the income statement in #1 as variable or fixed) for each month from November - January and for the total of the first quarter. Then, calculate: a. the contribution margin ratio, b. November break-even point in sales dollars, and C. November margin of safety in dollars