Please generate one year detailed sales mix, profit loss and cash flow. You will also generate 4 consolidated financial forms projected for 3 years; sales mix forecast, profit and loss statement, cash flow statement, ad balance sheet. These MUST be generated in excel using equations and cell references. There should be 7 sheets in your workbook. Please label them Sales Mix Detail PnL Detail Cash Flow Detail Con Sales Mix Con PnL Con Cash Flow and Con Balance Sheet

As demonstrated in class, you should be able to change the first year entry of sales: # of units or price per unit and the numbers on all other forms should change.

Assume the following business is a privately held corporation. The operating agreement is that the President/Chair receives a 15% monthly distribution based on positive net profit (calculated monthly).

DETAILED FORMS

Sales Mix Forecast

Primary Sales during Year 1 = 5500 units at $360 each

Secondary Sales during Year 1 = 1500 units at $600 each

Assume a season sales profile using the percentages below:

Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
.03	.04	.06	.10	.13	.16	.15	.14	.09	.06	.03	.01

Statement of Profit and (Loss)

Cost of Primary Sales = $50.40 per unit

Cost of Labor: Primary Sales $30 per unit

Cost of Secondary Sales = $48 per unit

Cost of Labor: Secondary Sales $21 per unit

Consider these other variable costs:

Commissions of 15% on all sales

Sales tax of 7% on all sales

Consider the following as fixed expenses (listed as yearly totals)

Payroll	$650,000
Payroll Taxes	$97,805
Benefits	$225,000
Utilities	$2,000
Lease Expense	$30,000
Insurance	$4,000
Marketing/Promotions	$20,000
Depreciation	$1,500

Consider the following non-fixed expenses

Interest Expense (see amortization schedule)

As a corporation, use 34% Corporation Taxes

Statement of Cash Flows

Use opening cash balance of $325,000

Loan Principle (See amortization schedule)

Dividend/Disbursements 15% of net income (Zero for any period with net loss)

Assume no other activity involving capital

CONSOLIDATED FORMS

Sales Mix Forecast

Same Price per unit all 3 years

Primary Sales of Year 2 = 2.5% increase of year 1

Primary Sales of Year 3 = 4% increase of year 2

Secondary sales of Year 2 = 2% increase of year 1

Secondary sales of Year 3 = 2% increase of year 2

Statement of Profit and (Loss)

Cost of Primary Sales = $50.40 per unit

Cost of Labor: Primary Sales = $30 per unit

Cost of Secondary Sales = $48 per unit

Cost of Labor: Secondary Sales $21 per unit

Expenses

Assume all expenses listed above for detailed sheets are the same for all 3 years EXCEPT Payroll Taxes Yr2= $98,944 Yr3= $100,653 (it is nearly impossible to have the same expenses for 3yrs, your group projects cannot make the same assumption)

Depreciation is straight-line, assume start in year 1.

Statement of Cash Flows

Dividend/Disbursements= 15% of net income (base on year-end net Profit for yrs 2 & 3)

Include a fixed asset purchase $75000 in year 3

Balance Sheet

Current assets/Inventory assume $3000 each years

Fixed assets starting fixed assets of $5,200,000

Depreciable assets/Equip. $4500

Accounts payable assume $1000 each year

Long-term liability (see amortization schedule)

AMORTIZATION SCHEDULE

Terms: Balance at start of yr1 = $3,100,000 Rate = 8.4% Interest paid monthly, length of term = 10 years

	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
Principal	$27,484	$27,677	$27,870	$28,066	$28,262	$28,460	$28,659	$28,859	$29,062	$29,265	$29,470	$29,676
Interest	$21,897	$21,704	$21,511	$21,315	$21,119	$20,921	$20,722	$20,522	$20,319	$20,116	$19,911	$19,705
Mortgage Payment	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381	$49,381

Year 2 Principal = $372,738

Year 2 Interest = $219,829

Year 2 Mortgage Payments = $592,567

Year 3 Principal = $405,282

Year 3 Interest = $187,285

Year 3 Mortgage Payment = $592,567