An Interactive Case Study On The Budgeting Process Salem Valve and Pump Company (SVP) Salem Valve and Pump Company was established by John Botu in 2002 after returning from the Gulf War and retiring as an officer in the Air Force. John bought an existing machine shop that made three main parts for water purification systems: Valves, Pumps, and Flow Controllers. He quickly formed a partnership with C. W. Smith, a well-known manufacturer of brass fittings for boats. Smith was quick to analyze the nature of problems other manufacturers were having with water purification valves. Since the tolerances needed were small, maintaining them required great labor skill and expensive machine controls. Within weeks of forming the company, Smith and his shop crew were manufacturing valves that met or exceeded the needed specifications. Botu negotiated a contract with a large international purification equipment manufacturer, and revenues soon were pouring into the new company. Knowing that the same manufacturing techniques could also apply to pumps and flow controllers, SVP created an engineering department to design new products for those markets. SVP specialized in bronze to exploit Smiths special knowledge about working with that material. In the next five years, SVP became the leading supplier of bronze valves, pumps and flow controllers. Raw forgings and castings purchased from foundries were precisely machined and assembled in SVPs new modern manufacturing facility. The same CNC and tooling machines were used for all three product lines. Runs were scheduled to match customer shipping requirements to eliminate finished goods inventory. The raw material suppliers (foundries) had agreed to just-in-time deliveries, and products were packed and shipped as completed. The company held small inventories of raw materials and finished goods to serve as safety stock to fill customer emergency needs. SVP has a competitive advantage over most of its competition. The company is located in a small town in Ohio that has good access to skilled labor and raw materials suppliers. The plant is located along a rail road spur, has good access to major highways, and to water transportation via the Great Lakes or the Ohio River. The CFO of SVP, John Paul Morgan, is responsible for the day-to-day financial and office support staff for SVP. Each year JP prepares a detailed budget based on the marketing and manufacturing operations staff predictions about next years expected sales, cost of goods sold, and required production requirements. Manufacturing Overhead and Selling and Administrative costs are projected by JP and his staff. The time period for this case is the budget period for 2019. JP has compiled the tables on your Excel worksheet after lengthy discussions with marketing, operations, engineering, and his administrative staff. You will be asked to complete the Excel worksheets given to you. These Excel worksheets will follow the schedules in Chapter 6 of your text book and will allow you to use the given data to construct all of the supporting schedules and financial statements required for the 2019 Budget. Remember Excel is a tool to assist you in building worksheets. Please do not use Excel like a typewriter!!! All worksheets after the Given Data Worksheet should reference cells in other worksheets or be part of a formula. This case will sharpen your Excel skills needed in the workforce today. You will graded on the correct answer, use of formulas, print format, and providing a printout of the formulas used in each worksheet turned in. As part of this Case, you will be given scenarios based on the data in your worksheets to complete Test questions as well as answer questions for your written report. Worksheets will be gathered at various points during the Fall Term and graded so that students will know the solutions to those parts to continue to the other worksheets. While the company has hundreds of different products in each of its three lines, this case will use only the average costs for each line to simplify the budgeting process. Each of the products within each line is manufactured in a similar manner so costs are going to be consistent between each product within the line of products. The Excel worksheet will show the given data to assist you in building the supporting worksheets and financial statements. You will be given quarterly data and will be required to build your supporting worksheets that show each quarter and a total for the entire year of 2019.

Handout # 1 1. Using the given data worksheet, complete the first four worksheet tabs provided. The worksheets are the Sales Projections, Production Schedule, Direct Material Budget, and Direct Labor Budget. 2. You have enough data in the Given Worksheet to complete these schedules.

Salem Valve and Pump Company
Budget Data for 2019
Budgeted Sales in Units 2019	1st qtr	2nd qtr	3rd qtr	4th qtr	Total 2019		1st qtr 2020
Valves	21,600	24,000	22,800	23,400	91,800		25,740
Pumps	36,000	40,000	38,000	39,000	153,000		42,900
Flow Controllers	14,400	16,000	15,200	15,600	61,200		17,160
Total Units	72,000	80,000	76,000	78,000	306,000		85,800
Sales Mix Ratio(Solve)	Valves	Pumps	Flow Controllers
Sales Ratio in Units
	Valves	Pumps	Flow Controllers
Sales Price per unit	$ 58.00	$ 100.00	$ 110.00
	Valves	Pumps	Flow Controllers
12/31/18 Ending Inventory Units	4,320	7,200	2,880
Inventory Value Finished Goods 12/31/2018	Valves	Pumps	Flow Controllers	Total FG Inv
$ Value of Ending Inventory	$ 140,400	$ 327,816	$ 211,392	$ 679,608
Standard Raw Materials/unit	Valves	Pumps	Flow Controllers
Foundry Castings # casting used/unit	2	3	5
Coating Materials in gallons	0.1	0.2	0.3
Machine hours per unit	0.25	0.3	0.4
12/31/18 Inventory of Raw Materials	Valves	Pumps	Flow Controllers
Foundry Castings	21,600	54,000	36,000
Coating Materials in gallons	864	2,880	1,728
12/31/18 $ Value of Raw Materials	Valves	Pumps	Flow Controllers
Foundry Castings	$ 108,000	$ 324,000	$ 288,000
Coating Materials in gallons	$ 1,728	$ 5,760	$ 3,456
Standard Costs Direct Materials	Valves	Pumps	Flow Controllers
Foundry Castings $ per each casting used	$ 5.00	$ 6.00	$ 8.00
Coating Materials cost per gallon	$ 2.00	$ 2.00	$ 2.00
Direct Labor Useage per Unit	Valves	Pumps	Flow Controllers
Direct Labor Hours per unit	0.25	0.5	0.75
Direct Labor Rate $	$ 16.00	$ 16.00	$ 16.00
Manufacturing Overhead per Month:
Receiving	$ 20,000	$ 20,000	$ 10,000
Material Handling	180,000	180,000	45,000
Engineering	100,000	100,000	25,000
Packing and Shipping	60,000	60,000	30,000
Maintenance/General Factory Overhead	60,000	60,000	30,000
Machine Depreciation	100,000	100,000	60,000
Total Monthly Overhead	$ 520,000	$ 520,000	$ 200,000
Overhead Rates:	Cost Driver	Budget Cost Driver	Cost Pool	Overhead Rate
Operations Overhead	# units	306,000	$ 4,320,000	$ 14.1176
Machine Overhead	machine hrs	93,330	$ 1,920,000	$ 20.5722
Safety Stock:
Safety Stock for Projected Inventory Levels of Finished goods is 20% of next quarters sales in units.						20%
Safety Stock for Fountry Castings is 50% of next quarter required needs.						50%
Safety Stock for Coatings in gallons is 40% of next quarter required needs.						40%
Selling and Administrative Expenses
Per Month
Variable Marketing Costs	$ 3.00	per unit sold
Fixed Selling and Administrative Expenses:
Salaries and Benefits	$ 125,000
Advertising	20,000
Office Supplies	5,500
Postage	3,500
Printing	8,500
Depreciation	30,000
Telephone	10,000
Utilities	6,500
Other Expenses	38,613
Total Fixed Selling and Administative Expenses	$ 247,613
Income Tax Rate	35%
Bad Debt Expense	0.5%	Of sales