PLEASE solve Production Schedule !

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 # 1Using 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.You have enough data in the Given Worksheet to complete these schedules. Make sure that you reference cells in the Given Worksheet or other worksheets to build each of the required schedules. Use formulas in your schedules wherever required. You should use Chapter 6 to assist you with format for the schedules and formulas required. The data in each worksheet should show unit data and dollar amount data for the following:

1st qtr.

2nd qtr.

3rd qtr.

4th qtr.

Total 2019

To ensure that your data is secure, please save often (at least every thirty minutes). Also, save your data to a source other than your computers hard drive. I suggest a flash drive or to the cloud.

On the due date for the first assignment, please print all of the schedules required in # 1. Make sure that when you print your worksheets they look professional. Determine whether you are going to print portrait or landscape and make sure all schedules are printed the same way. Make sure that you use , for thousands of units and dollars. Round all numbers to whole numbers. All schedules should have a heading and schedule # assigned to it. Breakout data by product lines so that management can see details of units and dollar amounts by product line. All of your schedules should use a consistent font style and font size for all worksheets. Make sure that your name is on every page printed. Staple all of your work in upper left corner. Do not put your solutions in notebooks or folders.

The second report required to be printed is you formula printing sheet. To do this function in Excel, go to the worksheet you want formulas printed. The top tool bar should have one labeled formulas. Go to this tab and look for function call show formulas. Once you click this icon, your worksheet will show all formulas on the worksheet. This report will be stapled behind your schedules.

The first assignment is worth 50 points. You can earn 20 points on the proper use of Excel, 20 points on the correctness of your solutions, and 10 points on the format and printing of the schedules.

1 Salem Valve and Pump Company 2Budget Data for 2019 4 6 Budgeted Sales in Units 2019 7 Valves 8 Pumps 9 Flow Controllers 10 Total Units 2nd qtr 3rd qtr 4th qtr Total 2019 91,800 153,000 61,200 306,000 1st qtr 21,600 36,000 14,400 72,000 24,000 40,000 16,000 80,000 22,800 38,000 15,200 76,000 23,400 39,000 15,600 78,000 1st qtr 2020 25,740 42,900 17,160 85,800 Flow 12 Sales Mix Ratio(Solve) 13 Sales Ratio in Units 14 Valves Pumps Controllers Flow Valves Pumps Controllers 15 16 Sales Price per unit 17 18 58.00 $ 100.00 110.00 Flow Controllers 19 Valves Pumps 20 12/31/18 Ending Inventory Units 4,320 7,200 2,880 Flow Inventory Value Finished Goods 12/31/2018 $ Value of Ending Inventory 22 Valves Pumps Controllers Total FG Inv 23 $140,400 $ 327,816$ 211,392 $ 679,608 1 Salem Valve and Pump Company 2Budget Data for 2019 4 6 Budgeted Sales in Units 2019 7 Valves 8 Pumps 9 Flow Controllers 10 Total Units 2nd qtr 3rd qtr 4th qtr Total 2019 91,800 153,000 61,200 306,000 1st qtr 21,600 36,000 14,400 72,000 24,000 40,000 16,000 80,000 22,800 38,000 15,200 76,000 23,400 39,000 15,600 78,000 1st qtr 2020 25,740 42,900 17,160 85,800 Flow 12 Sales Mix Ratio(Solve) 13 Sales Ratio in Units 14 Valves Pumps Controllers Flow Valves Pumps Controllers 15 16 Sales Price per unit 17 18 58.00 $ 100.00 110.00 Flow Controllers 19 Valves Pumps 20 12/31/18 Ending Inventory Units 4,320 7,200 2,880 Flow Inventory Value Finished Goods 12/31/2018 $ Value of Ending Inventory 22 Valves Pumps Controllers Total FG Inv 23 $140,400 $ 327,816$ 211,392 $ 679,608