Team Spreadsheet Assignment
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Team Spreadsheet Assignment
Brilliant Styles Jewelry Store
Due Monday, April 14 by 11:59 pm
Assignment:
1. Brilliant Styles is a start-up family-run jewelry store. To get approved for a loan to start their
business and finance their capital investments, 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 must submit an Excel spreadsheet (not a Google doc). Only one team member should submit the spreadsheet.
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 and is not included on the business income statement.
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 2026 are listed below. Because they are a startup, there are no sales prior to
|
January 2026. Month |
Sales |
Month |
Sales |
|
January |
$120,000 |
July |
$155,000 |
|
February |
$145,000 |
August |
$140,000 |
|
March |
$130,000 |
September |
$160,000 |
|
April |
$125,000 |
October |
$180,000 |
|
May |
$150,000 |
November |
$220,000 |
|
June |
$160,000 |
December |
$235,000 |
2. Assume sales are forecast to decrease by 5% in every month for 2027 due to declining economic conditions. Sales will be 5% less than the same month in the prior year.
3. An equity (owner) contribution of $80,000 cash will be made to start up the jewelry store.
4. Employee Related Expenses:
a. Assume that the jewelry store is open 12 hours each day X 363 days [4,356 hours]. Brilliant also employs individuals to work part-time at 6 hours each day X 363 days each year [2,178 hours]. Assume the total salaries and wages are paid on an equal monthly basis.
Owner’s Salary $125,000
1 Floor Manager 4,356 x $22.75 = $99,099
1 Purchasing Manager 4,356 x $17.25 = $75,141
2 Full-Time Sales Clerks 4,356 x $11.00 x 2 = $95,832
2 Part-Time Sales Clerks 2,178 x $9.75 x 2 = $42,471
1 Part-Time Purchasing Clerk 2,178 x $12.50 = $27,225
1 Cleaning Lady $8,000
Total Annual Salaries & Wages $472,768
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; paid as incurred.
5. Brilliant will be issued a $500,000, 15-year loan, issued at 7.75% 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 2026. The loan proceeds will be used for:
Rental space remodel $180,000
Initial inventory purchase $320,000
6. The rental space remodel is attributed to furniture and fixtures that are being depreciated on a 10- year, 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 5% 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.5% of sales.
6. Utilities and telephone are included in the monthly rent of $8,500.
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, 48% will pay two months later, and 2% of credit customers will not pay at all (bad debt).
9. Additionally, operating expenses are 8% of sales, paid as incurred.
2025-04-18
Brilliant Styles Jewelry Store