Question

Please help fill in the cells for steps 4, 5, and 6.

Step 4 of 34 - Sales Assumptions D G B E F H 1 Spreadsheet ID 2 1234 Budget Assumptions 4 First Month's Sales in Units 5 Sales Growth Per Month 1 Your turn: Based on this paragraph enter these assumptions: Sales Price per Unit 6 Wally's Widgets is starting operations as of January 1. The company sells a single product and expects first month's sales to be 10,000 units, with sales increasing by 250 units per month thereafter. The sales price for the product is $12.00 7 Gross Margin 8 Commission 9 Ending Inventory 10 Minimum Cash Balance 11 Interest Rate The company has a 50% gross margin on its products, and pays a 10% commission on sales. 12 Collections Paymen 13 14 Month 1 15 Month 2 16 Month 3 17 18 Operating Expenses Per M 19 20 21 22 23 Operating Budget Cash Budget Inputs Supporting Schedules Step 5 of 34 - Inventory, Cash, and Interest A D G K 9 Ending Inventory Your turn: Based on this paragraph enter these assumptions: 10 Minimum Cash Balance 11 Interest Rate Wally's Widgets expects the ending inventory balance at the end of any month to be 60% of the expected sales for the following month. The company wishes to maintain a minimum cash balance of $20,000 at all times. The company has access to a line of credit that incurs an interest rate of 12% per year. 12 Collections Payment 13 14 Month 1 15 Month 2 16 Month 3 17 18 Operating Expenses Per Month 19 20 21 22 23 24 25 26 27 28 29 30 31 Cash Budget Operating Budget Inputs Supporting Schedules

Answer 1

Budget Assumption
First month's Sales in units	10000
Sales Growth per Month	250 units
Sales Price per unit	$12.00
Gross Margin*	$60,000
Commission**	$12,000
Ending Inventory***	6,150 units
Minimum Cash Balance	$20,000
Interest Rate	12%
	Collections	Payments
Month 1	25%	60%
Month 2	50%	40%
Month 3	25%

*Gross Margin = 50% of Sales

= 50% of ($12 * 10,000 units)

= $60,000.

**Commission = 10% of Sales

= 10% of $120,000

= $12,000.

***Ending Inventory = 60% of Expected Sales next month

Expected Sales Next month = 10,000 + 250 units

= 10,250 units.

Ending Inventory = 10,250 * 60% = $6,150.