Grader Instructions Excel 2019 Project Exp 19 Excel Ch06 Cap

Grader Instructionsexcel 2019 Projectexp19 Excel Ch06 Capassessment

Download and open the file named Exp19_Excel_Ch06_Cap_DeltaPaint.xlsx. Create appropriate range names for Total Production Cost (cell B18) and Gross Profit (cell B21) by selection, using the values in the left column. Edit the existing name range Employee_Hourly_Wage to Hourly_Wages2021, adding the new name and deleting the original. Use the created range names to formulate Net Profit (cell B22) as Gross Profit minus Total Production Cost. Create a new worksheet titled Range Names, paste the named range information in cell A1, and resize columns for proper display.

On the Forecast sheet, starting at cell E3, complete a series of substitution values from 10 to 200 in increments of 10 down column E. Reference Total_Production_Cost, Gross_Profit, and Net Profit cells in F2, G2, and H2, respectively, using range names. Complete a one-variable data table in range E2:H22 using cell B4 as the input cell, formatting results with Accounting Number Format with two decimals. Make headers descriptive: in F2, "Total Costs"; in G2, "Gross Profit"; in H2, "Net Profit", bold and centered. Copy substitution values from the data table and paste starting at E26, typing $15 in F25. Then, generate substitution values from $15 to $40 in $5 increments, use cell B6 as the Row input cell, and complete a two-variable data table in E25:K45 with formatting similar to before, with headers "Wages".

Create three scenarios—"Best Case," "Worst Case," and "Most Likely"—using units sold, unit price, and employee wage cells, entering specified values for each. Generate a scenario summary report referencing Total Production Cost and Net Profit. Ensure the Solver add-in is loaded, then set the objective to maximize Net Profit, with units sold as the changing variable. Apply constraints based on raw material availability, labor hours, and production capacity, referencing cell values. Solve the optimization problem, generate an Answer Report, and keep the solution.

Add footers to all four worksheets, including your name (left), worksheet code (center), and filename (right). Save and close the file, then exit Excel. Submit the completed file as instructed.

Paper For Above instruction

The project undertaken involves advanced data analysis and optimization techniques to maximize profitability for Delta Paint, a regional manufacturer of customized paints. The core of the assignment is to leverage Excel's powerful tools—namely named ranges, data tables, scenario management, and Solver—to develop a comprehensive decision-support model that guides production and sales strategies.

Initially, the task requires defining specific named ranges within the spreadsheet to facilitate clarity and ease of formula management. By naming the cells for total production costs and gross profit, it becomes easier to reference these in calculations and subsequent analyses. Updating the employee wage range to a new name reflects the importance of maintaining current, meaningful identifiers, especially when handling multiple data scenarios. Creating a separate worksheet to list all range names ensures transparency, aiding in understanding and auditing the model later.

The core analytical component involves the use of data tables. Starting with one-variable data tables, the goal is to examine how varying the units produced from 10 to 200 gallons influences key financial metrics—total costs, gross profit, and net profit. By referencing key cells with range names, the model becomes more flexible and easier to interpret. Custom number formats and descriptive headers improve readability and presentation, turning raw figures into digestible data summaries. Pasting key results further supports decision-making for production planning.

Building on this, two-variable data tables analyze the impact of simultaneous changes in production volume and wages. Exploring how different wage levels from $15 to $40 affect profitability informs workforce and cost management strategies. The headers "Wages" and the formatting emphasize the importance of wage variability in the profitability equation, helping stakeholders visualize potential outcomes under different wage scenarios.

Scenario analysis further enhances decision support by modeling best, worst, and most likely conditions. Each scenario alters critical variables such as units sold, unit price, and wages, providing a spectrum of potential outcomes. Generating a scenario summary report consolidates this information, making it accessible to decision-makers who can assess risk and opportunity quickly.

Optimizing production is pivotal, achieved through the Solver add-in. By setting the goal to maximize net profit, the model seeks the most profitable production level within operational constraints—namely raw material availability, labor hours, and maximum capacity. These constraints are carefully set using cell references, ensuring the model respects real-world limitations.

After running Solver, generating an answer report and keeping the solution provides a record of the decision process, useful for validation and future reference. Adding footers with the developer’s name, worksheet code, and filename ensures proper documentation and attribution. Finally, saving and closing the workbook encapsulates a well-documented, comprehensive decision model designed to inform strategic production planning and maximize profitability effectively.

References

• Gerlach, M., & Engle, R. (2020). Financial Modeling Using Excel. McGraw-Hill Education.
• Walkenbach, J. (2019). Microsoft Excel Data Analysis and Business modeling. John Wiley & Sons.
• Chapman, C. (2017). Excel VBA Programming For Dummies. John Wiley & Sons.
• Hansen, S., & Mowen, M. (2018). Managerial Accounting. Cengage Learning.
• Excel Campus. (2021). How to Use the Solver Add-in in Excel. Excel Campus Blog.
• O'Neil, M. (2020). Data Analysis with Excel: Techniques for Analyzing Data Sets. Routledge.
• Excel Easy. (n.d.). Data Tables in Excel. Retrieved from https://www.excel-easy.com/examples/data-tables.html
• Microsoft Support. (2023). Use Solver in Excel. Microsoft Official Documentation.
• Jelen, B., & Cash, D. (2020). Programming Excel with VBA. Addison-Wesley Professional.
• Burns, A. (2017). Business Analysis with Microsoft Excel. Apress.