An Interactive Case Study On The Budgeting Process Salem Valveand Pump Company (SVP) Salem Valve and Pump Company was establishedby John Botu in 2002 after returning from the Gulf War and retiringas an officer in the Air Force. John bought an existing machineshop that made three main parts for water purification systems:Valves, Pumps, and Flow Controllers. He quickly formed apartnership with C. W. Smith, a well-known manufacturer of brassfittings for boats. Smith was quick to analyze the nature ofproblems other manufacturers were having with water purificationvalves. Since the tolerances needed were small, maintaining themrequired great labor skill and expensive machine controls. Withinweeks of forming the company, Smith and his shop crew weremanufacturing valves that met or exceeded the neededspecifications. Botu negotiated a contract with a largeinternational purification equipment manufacturer, and revenuessoon were pouring into the new company. Knowing that the samemanufacturing techniques could also apply to pumps and flowcontrollers, SVP created an engineering department to design newproducts for those markets. SVP specialized in bronze to exploitSmith’s special knowledge about working with that material. In thenext five years, SVP became the leading supplier of bronze valves,pumps and flow controllers. Raw forgings and castings purchasedfrom foundries were precisely machined and assembled in SVP’s newmodern manufacturing facility. The same CNC and tooling machineswere used for all three product lines. Runs were scheduled to matchcustomer shipping requirements to eliminate finished goodsinventory. The raw material suppliers (foundries) had agreed tojust-in-time deliveries, and products were packed and shipped ascompleted. The company held small inventories of raw materials andfinished goods to serve as safety stock to fill customer emergencyneeds. SVP has a competitive advantage over most of itscompetition. The company is located in a small town in Ohio thathas good access to skilled labor and raw materials suppliers. Theplant is located along a rail road spur, has good access to majorhighways, and to water transportation via the Great Lakes or theOhio River. The CFO of SVP, John Paul Morgan, is responsible forthe day-to-day financial and office support staff for SVP. Eachyear JP prepares a detailed budget based on the marketing andmanufacturing operations staff predictions about next year’sexpected sales, cost of goods sold, and required productionrequirements. Manufacturing Overhead and Selling and Administrativecosts are projected by JP and his staff. The time period for thiscase is the budget period for 2019. JP has compiled the tables onyour Excel worksheet after lengthy discussions with marketing,operations, engineering, and his administrative staff. You will beasked to complete the Excel worksheets given to you. These Excelworksheets will follow the schedules in Chapter 6 of your text bookand will allow you to use the given data to construct all of thesupporting schedules and financial statements required for the 2019Budget. Remember Excel is a tool to assist you in buildingworksheets. Please do not use Excel like a typewriter!!! Allworksheets after the “Given Data Worksheet” should reference cellsin other worksheets or be part of a formula. This case will sharpenyour Excel skills needed in the workforce today. You will graded onthe correct answer, use of formulas, print format, and providing aprintout of the formulas used in each worksheet turned in. As partof this Case, you will be given scenarios based on the data in yourworksheets to complete Test questions as well as answer questionsfor your written report. Worksheets will be gathered at variouspoints during the Fall Term and graded so that students will knowthe solutions to those parts to continue to the other worksheets.While the company has hundreds of different products in each of itsthree lines, this case will use only the average costs for eachline to simplify the budgeting process. Each of the products withineach line is manufactured in a similar manner so costs are going tobe consistent between each product within the line of products. TheExcel worksheet will show the given data to assist you in buildingthe supporting worksheets and financial statements. You will begiven quarterly data and will be required to build your supportingworksheets that show each quarter and a total for the entire yearof 2019.

Handout # 1 1. Using the given data worksheet, complete thefirst four worksheet tabs provided. The worksheets are the SalesProjections, Production Schedule, Direct Material Budget, andDirect Labor Budget. 2. You have enough data in the Given Worksheetto complete these schedules.

Salem Valve and PumpCompany
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 Goods12/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 eachcasting 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 ProjectedInventory Levels of Finished goods is 20% of next quarters sales inunits.						20%
Safety Stock for FountryCastings is 50% of next quarter required needs.						50%
Safety Stock for Coatings ingallons is 40% of next quarter required needs.						40%
Selling and Administrative Expenses
Per Month
Variable Marketing Costs	$              3.00	per unit sold
Fixed Selling andAdministrative 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 Sellingand                                      Administative Expenses	$        247,613
Income Tax Rate	35%
Bad Debt Expense	0.5%	Of sales