The main concern for owners and management in operating a business is profitability. To achieve or maintain a desired level of profitability, decisions must be made which affect product mix, total revenue and total cost. A valuable tool in evaluating the potential effects of decisions on profitability is known as cost-volume-profit analysis. Linear programming is a useful mathematical technique in making decisions regarding product mix.

This project will have students use a mathematical & graphical approach, including Excel spreadsheet programming techniques, to address specific business and management problems in a prescribed hypothetical scenario. Students will assume the role of a consultant and prepare a report on their findings for presentation to the hypothetical company seeking to increase profitability.

Instructions

This analysis will require you to complete a business decision analysis on each of two different cases, and then conduct a comparative analysis between the two. You will present a final report that includes the following:

The results of your analysis (i.e. presenting analysis and conclusions for all Research Analysis Questions presented in case).

A clear rationale outlining how you conducted the analysis and why.

A comparison between the two cases that clearly outlines their similarities and differences, and strengths and weaknesses relative to each other.

The Case Scenario:

You, as a consultant, have been approached by a local business, the Widget Factory, which is seeking to develop models for sustainable profitability.

a) New Product Break-Even Analysis

The Widget Factory has developed a new product, a solar powered beach fan, that it feels has potential in the market, but wants to be assured that it will be financially viable prior to going to the next level of development. The company conducted some preliminary market research and determined that the new product, if/when launched, can be sold at $50.00 per unit.

Internal cost analysis on the manufacturing of the product, based on current assets and resources of the company, reveals the following information (NOTE: The available accounting data is in terms of units):

Fixed cost per period: $8640.00

Variable cost per unit: $30.00

Production capacity per period: 900 units

RESEARCH ANALYSIS QUESTION #1: Should the company proceed with the introduction of this product and if so, how?

b) Corporate-wide Break Even Analysis

The following information is available about the operations of the Widget Factory for the current year (Note: In this instance, the available accounting data is presented in terms of total dollars, not units).

Sales		$40000
Fixed costs	$ 12600
Variable costs	$ 16000
Total cost		$ 28600
Net income		$ 11400

Capacity is a sales volume of $60000.

RESEARCH ANALYSIS QUESTION #2: Is the company well positioned to be profitable given the parameters outlined above? Why or why not?

The Case Analysis:

In preparing your final report, your team must:

Perform a break-even analysis for each of the data sets above that includes:

an algebraic statement of the revenue function, and the cost function,

a detailed break-even chart

a computation of the break-even point of both data sets as both a percentage of capacity and in dollars.

Present the Break-Even Next for each of the data sets, using the Excel spreadsheet software to formulate the question and generate a program that includes all the cost-volume-profit analysis aspects.

The program should be functioning properly such that we should be able to change the input data [through changing cells] to vary the variables and observe the changes to Total Cost, Total Revenue, & Fixed cost graphically. For example, we could change the Fixed cost, the number of items produced, the cost per unit, selling price per unit, & the variable cost individually, or in combination in cells and see the changes resulting graphically.

Hints : in your Excel Program you can start allocating cells to the input data such as those mentioned above, and then calculate in your “calculation cells”, for the outputs such as Total cost, Total Revenue, ...etc.. Following these steps you will want to generate the graph from the output data. Notice when generating a graph you can allocate a starting quantity of the product [minimum, e.g., 0], then the maximum number of production [e.g., 500 to start the program] and then these values can be varied later.

Prepare a consulting report for the Widget Factory that includes:

Two detailed responses to the Research Analysis Questions support by your analysis.

An interpretation of the parameters of the analysis presented, including: losses, gains, risks, and the role and significance of the break-even points presented.

A discussion of the implications of market fluctuations, and the potential for them to impact the other parameters of the case. Include recommendations for the management of the Widget Factory in regards to how they can minimize the impact of market fluctuations, and what they should be aware of when reviewing market data.

NOTE: Please note that all the linear functions must be formulated and plotted in your Excel Spreadsheet that you must include in your report, not using manually inserted objects. Programing for each of linear line in Excel and adding them to the graph area through Excel graphing features is absolutely necessary. This will ensure that the Excel program will serve as a standalone program with a capacity for changing a few cells of input data, should the circumstances of the scenario change.

Instructions

Review the handout entitled Cost-Volume-Profit Analysis. This document contains much valuable information that you will find useful as you proceed through the analyses.

Complete your analysis of the Widget Factory as presented above.

Prepare your final consulting report ensuring that you address all components as outlined above. Ensure that your report includes a title page, and proper introduction and conclusion. Ensure all external sources are referenced in accordance with APA referencing standards.

There is no minimum length for your report. You will be graded in regards to both your analysis and the extent to which you are able to explain your analysis through the report to your client, the Widget Factory.