Excel spreadsheet

Assumptions

Out-West Products, Inc.
Key Assumptions
Unit Sales					Unit Volume Sensitivity -->			0.0%
UNIT SALES		ACTUAL				FORECAST
Month	April	May	June		July	August	September	October
TOTAL	7,650	8,400	9,200		10,200	11,400	12,700	13,200
PID-B	7,650	8,400	7,820		8,466	9,120	9,652	9,900
PID-P	-	-	1,380		1,734	2,280	3,048	3,300
TOTAL	7,650	8,400	9,200		10,200	11,400	12,700	13,200
Sales Mix Sensitivity -->					2%	3%	4%	1%
Sales Mix %		PID-B	85%		83%	80%	76%	75%
		PID-P	15%		17%	20%	24%	25%
Sales Price Sensitivity -->					-2.00%	-2.00%	-2.00%	-2.00%
Unit selling price		PID-B	$ 230		$ 225	$ 221	$ 216	$ 212
		PID-P	$ 380		$ 372	$ 365	$ 358	$ 350
Unit cost		PID-B	$ 155		$ 155	$ 155	$ 155	$ 155
		PID-P	$ 225		$ 225	$ 225	$ 225	$ 225
Weighted Average Sales Price			$ 252.50		$ 250.39	$ 249.70	$ 250.36	$ 246.73
Weighted Average Unit Cost			$ 165.50		$ 166.90	$ 169.00	$ 171.80	$ 172.50
Gross Profit %		PID-B	32.6%		31.2%	29.8%	28.4%	26.9%
		PID-P	40.8%		39.6%	38.3%	37.1%	35.8%
		WTD AVG	34.5%		33.3%	32.3%	31.4%	30.1%
Other Assumptions					Operating expenses
Ending cash balance (per month)			$35,000		Variable:
Ending inventory as percentage					Sales commissions		(% of sales)	5.0%
	of next month's sales		70%		Shipping & handling		(per unit)	$ 20.00
Percentage of purchases paid in:					Fixed:			(per month)
	Current month		60%		Wages and salaries			$300,000
	Following month		40%		Rent			30,000
Percentage of sales collected in:					Utilities			10,000
	Current month		30%		Insurance expired			4,500
	Following month		40%		Depreciation			16,000
	Second following month		30%		Other S&A			40,000
New fixed asset purchases		September	$90,000		Advertising Campaign			- 0
Quarterly dividends			$20,000
Line-of-credit annual interest			8%
Balance Sheet: Actual as of June 30							OTHER INFO
Assets							Scenario II_A_2C
Cash & Equivalents					$40,000		Oper Inc =	-
Accounts Receivable		May	June		2,205,700		LOC =	-
		$ 579,600	$ 1,626,100
Inventory	7,140	units			1,181,670		Sensitivity II_C_2A
Prepaid insurance					20,000		Inv T/O =	0
Fixed assets, net of depreciation					1,480,000
Total Assets					$4,927,370		Sensitivity II_C_2B
							Days Sales =	0.00
Liabilities and Stockholder's Equity							Qual of Inc. =	- 0
Accounts payable, purchases					$970,464		Op Margin =	0.00%
Line-of-credit					- 0		Asset T/O =	- 0
Dividends payable					20,000
Capital stock, no par					1,750,000
Retained earnings					2,186,906
Total liabilities and stockholder's equity					$4,927,370

Schedules

Out-West Products, Inc.
Detailed Operating Schedules
Schedule a: Sales plan				July	August	September	Total
Weighted average sales price				$ 250.39	$ 249.70	$ 250.36
Total Units				10,200	11,400	12,700	34,300
Credit sales, 100%				$2,553,978	$2,846,626	$3,179,535	$8,580,138
NOTE: Actual A/R should be collected in July and Aug.
Schedule b: Cash collections
From current month's sales				$766,193	$853,988	$953,860	$2,574,042
From sales 1 month before				929,200	1,021,591	1,138,650	3,089,441
From sales 2 months before				579,600	696,900	766,193	2,042,693
	Total collections			$2,274,993	$2,572,479	$2,858,704	$7,706,176
Schedule c: Purchases plan in units
Desired units in ending inventory				7,980	8,890	9,240	26,110
Plus units sold				10,200	11,400	12,700	34,300
Total needed				18,180	20,290	21,940	60,410
Less beginning inventory				7,140	7,980	8,890	24,010
	Required purchases			11,040	12,310	13,050	36,400
Schedule d: Purchases plan (dollars)
Weighted average unit cost				$ 166.90	$ 169.00	$ 171.80
Desired ending inventory				$ 1,331,862	$ 1,502,410	$ 1,587,432
Plus cost of goods sold				1,692,384	1,909,842	2,156,968
Total requirements				3,024,246	3,412,252	3,744,400
Less beginning inventory				1,181,670	1,331,862	1,502,410
	Total purchases			1,842,576	2,080,390	2,241,990
Note: Actual A/P should be paid in July only
Schedule e: Cash pmts for purchases
From accounts payable (6/30)				$970,464
	From July Purchases			1,105,546	$737,030
	From August Purchases				1,248,234	$832,156
	From September Purchases					1,345,194
	Total payments			$2,076,010	$1,985,264	$2,177,350

Inc St & Bal Sht

Out-West Products, Inc.
Projected Income Statements and Balance Sheet
Projected Income Statements
			Schedule		July	August	September	Total
Sales			(a)
Less variable expenses
	Cost of goods sold		(d)
	Sales commissions
	Shipping & handling
Total variable expenses
Contribution margin
Less fixed expenses
	Wages and salaries
	Rent
	Utilities
	Insurance expense
	Depreciation expense
	Other Selling & Admin.
	Advertising Campaign
Total fixed expenses
Operating Income
	Less: Interest Expense				3,518	4,629	6,004	14,151
Net Income before Taxes					($3,518)	($4,629)	($6,004)	($14,151)
Projected Balance Sheet								September
Assets
Cash & Equivalents
Accounts receivable
Inventory
Prepaid insurance								6,500
Fixed assets, net of depreciation
Total assets
Liabilities and Stockholders' Equity
Accounts payable, purchases
Interest payable								6,004
Line-of-Credit								900,602
Dividends payable
Capital stock, no par
Retained earnings
Total liabilities and stockholders' equity
								EQUAL

Cash Flow

Out-West Products, Inc.
Projected Statements of Cash Flow
Projected Statements of Cash Flow
Internal Budget Format
			Schedule		July	August	September	Total
Beginning cash balance
	Cash collections		(b)
Total cash available
Cash disbursements
	Inventory purchases		(e)
	Variable operating expenses
	Fixed operating expenses
	Capital Acquisitions
	Interest					3,518	4,629
	Dividends
Total disbursements
Minimum cash balance
Total cash needed
Cash excess (deficit)
Financing
	Borrowing
	Repayments
Total cash from financing
Ending cash balance
	Line-of-Credit Balance			-

Graph

Graph Data
Weighted average computations:
Sales price per unit	$ 250.36
Variable costs per unit
Weighted average unit cost	$ 171.80
Sales commissions	12.52
Shipping and handling	20.00
Total variable costs per unit	$ 204.32
Fixed costs in total	-
CVP Graph Data:
Sales Levels	0	5,000	10,000	15,000	20,000	25,000	30,000
Revenue	$0	$1,251,785	$2,503,571	$3,755,356	$5,007,141	$6,258,927	$7,510,712
Fixed Costs	$0	$0	$0	$0	$0	$0	$0
Total Costs	$0	$1,021,589	$2,043,179	$3,064,768	$4,086,357	$5,107,946	$6,129,536

Graph