Question

In: Accounting

An Interactive Case Study On The Budgeting Process Salem Valve and Pump Company (SVP) Salem Valve...

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 sales

Solutions

Expert Solution

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 Solutions

Explain How fuel system components works: Booster pump- Jet Pump- Surge tank- Crossfeed valve- Jettison valve-...
Explain How fuel system components works: Booster pump- Jet Pump- Surge tank- Crossfeed valve- Jettison valve- Capcitance fuel gauge-
Explain How fuel system components works: Booster pump- Jet Pump- Surge tank- Crossfeed valve- Jettison valve-...
Explain How fuel system components works: Booster pump- Jet Pump- Surge tank- Crossfeed valve- Jettison valve- Capcitance fuel gauge-
case Study: Mr. Boggy has an IV running on an IV “Smart” pump. The IV pump...
case Study: Mr. Boggy has an IV running on an IV “Smart” pump. The IV pump continuously measures the volume infused, the medication administered, alarms when there is an obstruction or the medication is complete. The total volumes infused and the name and dose of medication administered are automatically wirelessly uploaded into a queue in the patient electronic medical record. The nurse must log in and acknowledge the information before the final entry. Required Elements for writing are as follows:...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department with second-degree burn caused by a direct contact to boiling water. The right thigh was the most affected part of his body where he sustained severe redness and multiple blisters. Salem complained of severe pain and his skin was very wet and angry-looking. Salem is known to have asthma since his childhood and has been diagnosed with cystic fibrosis for 2 years. He has...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department with second-degree burn caused by a direct contact to boiling water. The right thigh was the most affected part of his body where he sustained severe redness and multiple blisters. Salem complained of severe pain and his skin was very wet and angry-looking. Salem is known to have asthma since his childhood and has been diagnosed with cystic fibrosis for 2 years. He has...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department with second-degree burn caused by a direct contact to boiling water. The right thigh was the most affected part of his body where he sustained severe redness and multiple blisters. Salem complained of severe pain and his skin was very wet and angry-looking. Salem is known to have asthma since his childhood and has been diagnosed with cystic fibrosis for 2 years. He has...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department...
Case Study Salem is a 57 year-old male who has been brought to the Emergency Department with second-degree burn caused by a direct contact to boiling water. The right thigh was the most affected part of his body where he sustained severe redness and multiple blisters. Salem complained of severe pain and his skin was very wet and angry-looking. Salem is known to have asthma since his childhood and has been diagnosed with cystic fibrosis for 2 years. He has...
An IE performed a time study on a hydraulic valve installation & test process. Each observed...
An IE performed a time study on a hydraulic valve installation & test process. Each observed installation was performed by a different production worker, so the activity factor (performance rating) varied in each observation. A factor > 1.00 meant the worker was performing better than normal in their observed time (OT) (i.e, faster), while a factor < 1.00 meant the worker was performing less normal in their observed time (OT) (ie, slower). The allowance for personal delay and fatigue as...
In the case of a start-up company (for interactive stories book for children) , what is...
In the case of a start-up company (for interactive stories book for children) , what is the difference between its objectives and its long term aim? give examples for both.
Design a case study with hypertension, pathophysiologic concepts to your chosen disease process. Your case study...
Design a case study with hypertension, pathophysiologic concepts to your chosen disease process. Your case study should include the following elements: 1. Patient age 2. Past medical history 3. Any medications they are taking 4. Vital signs 5. Presentation (also described as the “chief complaint” or “history of present illness”) 6. Diagnostic values (such as laboratory or radiologic findings) Include only relevant data--it should not be your goal to “fool” someone with an unnecessarily complex or confusing case, but rather...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT