Parameters Fixed cost $ $10,000\mathrm{.}00$ Material costs per unit $ $0\mathrm{.}15$ labor cost per unit $ $0\mathrm{.}10$ Revenue per unit $ $0\mathrm{.}65$ Case Study $1$: Financial Analysis for Cox Electric's New Product Design Cox Electric is evaluating the financial feasibility of a new electronic component design with the following cost and revenue estimates: $$ Fixed costs: $$10,000($incurred regardless of production volume$)$ Material costs per unit: $$0\mathrm{.}15$ Labor cost per unit: $$0\mathrm{.}10$ Revenue per unit: $$0\mathrm{.}65$ These data are given in the file $$Module $7$ Homework.xlsx$$ worksheet $$CoxElectric$.$ Assuming that Cox Electric sells all units produces, profit is calculated as total revenue minus ttoal fixed cost and variable cost $($material and labor$).$ Questions: a$.$ Develop a spreadsheet model to determine the profit$/$loss for a given demand. If Cox Electric makes $12,000$ units of the new product, what is the resulting profit? $(10$ points$)$ Profit Calculation for $12,000$ Units Formula for Profit: Profit$=$Total$$Revenue$($Total$$Fixed$$Cost$+$Total$$Variable$$Cost$)$ Where: Total Revenue $=$ Units Produced $$ Revenue per Unit Total Variable Cost $=$ Units Produced $($Material Cost per Unit $+$ Labor Cost per Unit$)$ For $12,000$ units: Total Revenue $=12,0000\mathrm{.}65=$ $$7,800$ Total Variable Cost $=12,000(0\mathrm{.}15+0\mathrm{.}10)=$ $$3,000$ Total Cost $=$ Fixed Cost $+$ Total Variable Cost $=10,000+3,000=$ $$13,000$ Profit $=7,800-13,000=-$$$5,200($Loss$)$ b$.$ Construct a one$-$way data table with production volume as the column input and profit as the output. Breakeven occurs when profit goes from a negative to a positive value; that is$,$ breakeven is when total revenue $=$ total cost, yielding a profit of zero. Vary production volume from $0$ to $100,000$ in increments of $10,000.$ In which interval of production volume does breakeven occur? $(10$ points$)$ c$.$ Use Goal Seek function to find the exact production volume at which Cox Electric breaks even. Assign Set cell: equal to the location of profit, To value: $=0,$ and By changing cell: equal to the location of the production volume in your model. $(10$ points$)$ Case Study $2$: Grade Calculation for Production Operations Management Course Professor Treleaven needs to calculate grades for $58$ students in his course based on their Midterm Scores and Final Scores. The Course Average is determined by weighting the Midterm Score and Final Score equally $(50\%$ each$).$ He has already developed a spreadsheet, which can be found in the file $$Module $7$ Homework.xlsx$$ worksheet $$Grades$\text{'}.$ Questions: a$.$ Use the VLOOKUP function to generate the Course Grade for each student based on their Midterm Score and Final Score. $(10$ points$)$ b$.$ Use the COUNTIF function to determine how many students receive each letter grade based on their Course Grades. $(10$ points$)$ Instructions: $$ Save the completed Excel file with all calculations, formulas, and results. $$ Ensure all answers are clearly highlighted with corresponding question numbers for easy evaluation. $$ Submit the Excel file along with any supporting documents $($if applicable$).$