An Interactive Case Study On The Budgeting Process Salem Valve and Pump Company
ID: 2332553 • Letter: A
Question
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 Smith’s 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 SVP’s 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 year’s 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 salesExplanation / Answer
Dear student,
Please provide the worksheet tab which you have to complete with this information.
in my opinion the probable ques asked in worksheet will be related to sales budget, Production Budget, Raw Material Purchase Pudget and profitability statement.
1) Production Budget = Forcasted Unit Sales+ Invetory at End(Planned) = Total Production required - Opening inventory
2) Raw Material Purchase Budget = Raw Material Required For The Production(Production Required units * Per Unit raw material required) + Planned Inventory at end = total raw material required - beginning inventory
3) Profitability statement :- in this you have given with veriable cost of products use that and for fixed cost use that cost driver rate and multiply with the no of activities.
Example:- Machine Overhead = Machine hr Rate (Overhead Rate ) * No of machine hrs required for the product.
If you provide me the worksheets to be complete i can help you in more better way.
Wish you Success!!!!!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.