1. Brilliant Styles is a family-run jewelry store. In order to get approved for a loan to finance their capital investment, they must provide projected financial data to the bank. Using the information below, prepare the required documents they need for submission to the bank. To consider their loan request, the bank requires (so you are required to complete): a. One year of projected monthly cash budgets b. One year-end pro forma income statement c. One year-end pro forma balance sheet 2. You should use spreadsheet modeling for this assignment. All cells in the cash budgets and pro formas should contain formulas (cell references) based on input/worksheet information. Modeling is worth 10% of your total grade on this assignment. 3. You may work in groups of no more than three (preferably two). Groups have already been formed on Canvas. 4. You must submit an Excel spreadsheet (not a Google doc). General Information: 1. Assume it is a sole proprietorship, so there are no corporate taxes. The income of the business is taxed as personal income to the business owner. 2. The physical location of the business is inside a shopping mall. The company does not pay property taxes or any other associated fees except for monthly rent. Financial Information: 1. Sales forecasts for 2024 are listed below. 2. Assume sales are forecast to decrease by 5% for 2025 due to declining economic conditions. 3. An equity (owner) contribution of $100,000 cash was made to start up the jewelry store. 4. Employee Related Expenses: a. Assume that the jewelry store is open 12 hours each day X363 days [4,356 hours]. Brilliant also employs individuals to work part-time at 6 hours each day X363 days each year [2,178 hours]. Assume the total salaries and wages are paid on an equal monthly basis. b. In addition to the salaries shown above, Salesclerks are paid 1% commission on collectible sales. The commissions are paid two months after the revenue is earned. c. Other employee related expenses are 10% of monthly salaries, wages, and commissions. 5. Brilliant will be issued a $500,000,15-year loan, issued at 8.65% interest that requires monthly payments. The loan will be disbursed in December (the month before the store opens), and payments are expected to begin in January of 2024 . The loan proceeds will be used for: Rental space remodel Initial inventory purchase $180,000 $320,000 6. The rental space remodel is attributed to furniture and fixtures that are being depreciated on a 10year, straight-line basis with no salvage value. These items and the inventory will be purchased in December before the store opens in January. Other Information: 1. Brilliant wishes to maintain a $30,000 minimum cash balance. They do not plan to borrow if they do not meet their minimum, but would like to know if they are running a deficit or surplus of cash each month. 2. Advertising and marketing related expenses are 6% of sales. 3. COGS (Inventory) is 50% of sales and inventory is purchased one month in advance of the sales. The company pays for inventory two months after purchased. 4. Insurance expense is 2% of sales. 5. Administrative expenses are 1.75% of sales 6. Utilities and telephone are included in the monthly rent of $8,000. 7. Bank charges are 1% of sales. 8. Brilliant has a Net 60 -day credit policy. Approximately 40% of their customers pay on credit and the remainder pay in cash. Assume 50% of the customers who purchase on credit will pay one month later, 49% will pay two months later, and 1% of credit customers will not pay at all (bad debt). 9. Additionally, operating expenses are 7.5% of sales