PLEASE HELP!! I know it is a lot but I will appreciate it SO MUCH.

Assignment:

$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.

Month Sales $[$in thousands$]$ Month Sales $[$in thousands$]$

January $$160$ July $$185$

February $$175$ August $$160$

March $$150$ September $$170$

April $$145$ October $$190$

May $$180$ November $$260$

June $$190$ December $$270$

$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 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\mathrm{.}75=$ $$99,099$

$1$ Purchasing Manager $4,356$ x $$17\mathrm{.}25=$ $$75,141$

$2$ Full$-$Time Sales Clerks $4,356$ x $$11\mathrm{.}00$ x $2=$ $$95,832$

$2$ Part$-$Time Sales Clerks $2,178$ x $$9\mathrm{.}75$ x $2=$ $$42,471$

$1$ Part$-$Time Purchasing Clerk $2,178$ x $$12\mathrm{.}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.

$5.$ Brilliant will be issued a $$500,000,15-$year loan, issued at $8\mathrm{.}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 $$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 $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\mathrm{.}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\mathrm{.}5\%$ of sales.