1 answer

One-Variable Data Table Your maximum weekly production capability is 200 gallons. You would like to create...

Question:

One-Variable Data Table Your maximum weekly production capability is 200 gallons. You would like to create a one-variable data table to measure the impact of Production Cost, Gross Profit, and Net Profit based on selling between 10 and 200 gallons of paint within a week. a. Start in cell E3. Complete the series of substitution values ranging from 10 to 200 at increments of 10 gallons vertically down column E. b. Enter references to the Total Production Cost, Gross Profit, and Net Profit in the correct location for a one-variable data table. c. Complete the one-variable data table, and then format the results with Accounting Number Format with two decimal places. d. Apply custom number formats to make the formula references appear as descriptive column headings. Bold and center the headings and substitution values. e. Answer question 2 on the Q&A worksheet. Save the workbook. Two-Variable Data Table Your company is considering raising the manufacturing employees’ hourly wages. Employees are currently earning $15 per hour; however, you would like to review the impact of profit if the salaries were between $15 and $40 per hour. You will create a two-variable data table to complete the task. a. Copy the number of gallons produced substitution values from the one-variable data table, and then paste the values starting in cell E26. b. Type $15 in cell F25. Complete the series of substitution values from $15 to $40 at $5 increments. c. Enter the reference to net profit formula in the correct location for a two-variable data table. d. Complete the two-variable data table and format the results with Accounting Number Format with two decimal places. e. Apply a custom number format to make the formula reference appear as a descriptive column heading. Bold and center the headings and substitution values. f. Answer questions 3 and 4 on the Q&A worksheet. Save the workbook. Scenario Manager To this point you have created forecasts based on static production amounts; however, it is important to plan for both positive and negative outcomes. To help you analyze best, worst, and most likely outcomes, you will use Scenario Manager. a. Create a scenario named Best Case, using Units Sold, Unit Selling Price, and Employee Hourly Wage. Enter these values for the scenario: 200, 30, and 15. b. Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 100, 25, and 20. c. Create a third scenario named Most Likely, using the same changing cells. Enter these values for the scenario: 150, 25, and 15. d. Generate a scenario summary report using the Total Production Cost and Net Income. e. Format the summary as discussed in the chapter. f. Answer question 5 on the Q&A worksheet. Save the workbook. Use Solver You realize the best-case scenario may not be realistic. You have decided to continue your analysis by using Solver to determine the perfect production blend to maximize net income while most efficiently using raw materials and labor hours. a. Load the Solver add-in if it is not already loaded. b. Set the objective to calculate the highest Net Income possible. c. Use the units sold as changing variable cells. d. Use the Limitations section of the spreadsheet model to set a constraint for raw materials. e. Set constraints for labor hours. f. Set constraints for maximum production capability. g. Solve the problem. Generate the Answer Report. If you get an internal memory error message, remove Solver as an add-in, close the workbook, open the workbook, add Solver in again, and finish using Solver. h. Answer questions 6 through 8 on the Q&A worksheet. Apply landscape orientation to the Q&A worksheet. Save the workbook. i. Create a footer on all five worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side. j. Save and close the file. Based on your instructor’s directions, submit e06c1Manufacturing_LastFirst. Delta Paint One-Variable Data Table: Production Input Units Sold 100 Unit Selling Price $30.00 Employee Hourly Wage $15.00 Maximum Capabilty per week 200 Limitations Raw Materials in Units 15000 Required raw materials per unit 3 Required Labor Hours Per Gallon 0.25 Labor Hours available 2,000 Expenses Raw Materials Consumed 300 Labor Hours Consumed 25 Total Production Cost $375.00 Income Gross Profit $3,000 Net Profit $2,625 Two-Variable Data Table: Production and Manufacturing Time Your Answers Question 1) How many units must be sold to exhaust all raw materials and why is the not a feasible option? 2) For the one-variable data table, how many units muse be sold to Net over $5000? 3) For the two-variable data table, if the hourly wage is raised to $20.00 per hour, how many units must be sold to Net $4000? 4) For the two-variable data table, if the hourly wage is raised to $40.00 per hour, how many units must be sold to Net $4000? 5) What is the Net income based on the best case scenario? 6) What is the most profit you can generate within the constraints using Solver? 7) How many labor hours are consumed when net profit is maximized? Step by step please 8) What is the total production costs when profit is maximized?


Answers

ANSWER To insert one variable data table I have inserted two columns. (See screenshot attached). The references for Total ProSee the screenshpt below. Please let me know if you have any query Total Production cost Gross Profit 1666.66667 Req, raw mat

.

Similar Solved Questions

1 answer
60. Which atom would be expected to have a half-filled 6p subshell? 21. Using the Bohr...
60. Which atom would be expected to have a half-filled 6p subshell? 21. Using the Bohr model, determine the lowest possible energy for the electron in the He ion....
1 answer
Walk me through this a) Use the formula: k(x) to find the equation of the osculating circle for y In x at the point (1.0) 1+r732 The equation or the circle is: (x+(HS㎡+(y + (2/ b)Show that the os...
walk me through this a) Use the formula: k(x) to find the equation of the osculating circle for y In x at the point (1.0) 1+r732 The equation or the circle is: (x+(HS㎡+(y + (2/ b)Show that the osculating circle and the curve (y Inx) have the same first and decond derivative at the point (1...
1 answer
Q2. Determine the positive roots of the simultaneous nonlinear equations: yx2 y 2 cosx Use a...
Q2. Determine the positive roots of the simultaneous nonlinear equations: yx2 y 2 cosx Use a graphical approach to obtain your initial guesses. Plot both the equations in one plot area. You may have two sets of solutions. Considering one of the solutions and selecting initial guesses close to that s...
1 answer
30-What is the common name for 2-butanone? aa, methyl ethyl ketone b. isobutyl ketone c. diethyl...
30-What is the common name for 2-butanone? aa, methyl ethyl ketone b. isobutyl ketone c. diethyl ketone d. butyl ketone 31-What is the IUPAC name for ? CH3 CH2BECH2CHO a. 3-bromobutanal b.3-bromobutanone c. 2-bromobutanal d.2-bromobutanone 32- Benzaldehyde can be reduced by the Wolff- Kishner method...
1 answer
A 28.6 kg crate is placed at the bottom of an 23.0° incline whose surface is...
A 28.6 kg crate is placed at the bottom of an 23.0° incline whose surface is rough enough that the coefficient of kinetic friction is 0.371. A rope is then attached to the crate and, by pulling parallel to the incline, accelerates the stationary crate, during which the crate moves 6.30 m up the ...
1 answer
Bent wire loop
a wire loop has been bent so that it has three segments: segment be (a quarter-circle), ae (a square corner), and ab (straight). Here are three choices for amagnetic field through the loop:(1) 81 = 3i + 7] - 5tk,(2) 82 = 5,i - 4] - 15k,(3) 83 = 2i - 5t] - 12k,where B is in milliteslas and , is in se...
1 answer
8. The principal investigator asks a participant to be in her study. The participant agrees, and the investigator ha...
8. The principal investigator asks a participant to be in her study. The participant agrees, and the investigator has him sign a piece of paper, and tells him she will put it in his file. The investigator also says she will mail him information about the study specifics. This does not violates the p...
1 answer
Question Completion Status: A manufacturing company is considering two alternative locations for a new facility. The...
Question Completion Status: A manufacturing company is considering two alternative locations for a new facility. The fixed and variable costs for the two locations are found in the table below. For which volume of business would the two locations be equally attractive? If the company plans on produc...
1 answer
The pOH of a basic solution is 5.47. What is [OH-]? 0 of 1 point earned...
The pOH of a basic solution is 5.47. What is [OH-]? 0 of 1 point earned 2 attempts remaining The pH of a basic solution is 9.91. What is POH? 9 O of 1 point earned 2 attempts remaining The pOH of a basic solution is 2.79. What is [H-1? 10 L 0 of 1 point earned 2 attempts remaining The pH of a soluti...
1 answer
Part 1,2 and 3 004 (part 1 of 3) 10.0 points A 0.657 kg particle has...
Part 1,2 and 3 004 (part 1 of 3) 10.0 points A 0.657 kg particle has a speed of 2.6 m/s at point A and kinetic energy of 7 J at point B. What is its kinetic energy at A? Answer in units of J. 005 (part 2 of 3) 10.0 points What is the particle's speed at B? Answer in units of m/s. 006 (part 3 ...
1 answer
QUESTION 6 Ang company that produces pleasure boats has decided to expand one of its lines....
QUESTION 6 Ang company that produces pleasure boats has decided to expand one of its lines. The company is considering three Location alternatives. The cost structure for each location is given as shown in the table. Location Fixed Cost/yr variable cost/unit L1 13,515 2.3 L2 ...
1 answer
How do you solve the system #4x-7y=9# and #y=x-3# using substitution?
How do you solve the system #4x-7y=9# and #y=x-3# using substitution?...
1 answer
How do you write 2.5 (decimal) as a whole?
How do you write 2.5 (decimal) as a whole?...
1 answer
12. Use the fact that acceleration is - 32 feet per second per second as the...
12. Use the fact that acceleration is - 32 feet per second per second as the acceleration due to gravity. A ball is thrown vertically upward from the ground with an initial velocity of 56 feet per second. For how many seconds will the ball be thrown upward? Recall v(t) = a(t)- dt and s(t) = f(t)-dt...
1 answer
I'm looking at using some open source code in my ASP.NET web app (specifically dapper). Management...
I'm looking at using some open source code in my ASP.NET web app (specifically dapper). Management is not a fan, because open source is seen as a risk that has bitten us before. Apparently previous developers have had to rewrite things after having open-source components fail. The pros seem to b...
1 answer
What is the square root of 121 over 100?
What is the square root of 121 over 100?...
1 answer
How do you convert 0.38 into a percent and fraction?
How do you convert 0.38 into a percent and fraction?...
1 answer
Rank the EM waves from least energetic (7) to most energetic (1). Radio waves [Choose] X-rays...
Rank the EM waves from least energetic (7) to most energetic (1). Radio waves [Choose] X-rays [Choose] UV light [Choose) V Infrared [Choose] Gamma rays [Choose ] Microwaves [Choose] Visible light [Choose]...