Question

2. Using the information in Problem 9-46 (given below) construct an Excel spreadsheet to create a sales budget and a production budget. Use the format from your textbook, page 423 and figures 9-3 and 9-4. (20 Points) 9-46. Creating a Sales Budget and Production Budget Using Excel. Review the information for Kitchen Products, Inc., in Exercise 33. Prepare an Excel spreadsheet similar to Figure 9.3 and Figure 9.4 showing Kitchen Product's sales budget and production budget 9-33 Sales and Production Budgets. Kitchen Products, Inc., produces microwave ovens. Unit sales last year, ending December 31, are as follows: First quarter 40,000 Second quarter 50,000 Third quarter 52,000 Fourth quarter 48,000 Unit sales are expected to increase 10 percent this coming year over the same quarter last year. Average sales price per microwave oven will remain at $200. Assume finished goods inventory is maintained at a level equal to 5 percent of the next quarter's sales. Finished goods inventory at the end of the fourth quarter budget period is estimated to be 2,300 units.

Answer 1

Sales budget is used to estimate sales revenue for coming year.
Sales budget for Kitchen products for each quarter is shown below
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Sales (units)	44000	55000	57200	52800	209000
	40000*1.1	50000*1.1	52000*1.1	48000*1.1
Sale price per unit	$200	$200	$200	$200
Budgeted sales revenue	$8,800,000	$11,000,000	$11,440,000	$10,560,000	$41,800,000
Production budget is prepared to estimate the number of units to be produced for coming year
Production budget for each quarter of coming year is shown below
	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Sales (units)	44,000	55,000	57,200	52,800	209,000
Add: Desired ending inventory	2,750	2,860	2,640	2,300	2,300
	55000*5%	57200*5%	52800*5%
Total units required	46,750	57,860	59,840	55,100	211,300
Less: Beginning inventory	2,200	2,750	2,860	2,640	2,200
	44000*5%
Budgeted production	44,550	55,110	56,980	52,460	209,100