In: Accounting
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 | ||||||||
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!!!!!