MSC 615-01 Spring 2021 Assignment 1

Work on the spreadsheet template given (change the file name by replacing my name with yours).

For spreadsheet models, always color cells according to the convention used in this book; define range

names and show the whole list of range names.

1. XYZ company is considering to introduce a new product. The cost structure is estimated as follows.

Production Volume

10,000 Units 20,000 Units

Capital Costs $70,000 $70,000

Material at $1.50 Per Unit 15,000 30,000

Labor at $0.75 Per Unit 7,500 15,000

Overheard

Fixed 10,000 10,000

Variable at $0.50 Per Unit 5,000 10,000

Selling and Administrative

Expense

Fixed 5,000 5,000

Variable at $0.25 Per Unit 2,500 5,000

Total Cost $115,000 $145,000

a) Suppose the unit price of the product is $8.00. Summarize the data above and construct a

breakeven analysis model on the spreadsheet for the problem. What is the total fixed cost?

What is the unit variable cost? Test your model by entering different production quantities to

make sure it computes correctly. Now use the trial and error method and try to find the

production quantity resulting in zero profit, i.e. the breakeven point.

b) Plot the total revenue and total cost as functions of quantity produced/sold, using a chart

function in Excel (as in my example). Hint: You need to generate sufficient data points to

produce a graph with two lines. Do not include quantity zero in the chart to make each function

a straight line.

c) Express the total revenue, total cost, and total profit each algebraically as a function of the

production quantity, assuming all units produced can be sold. Define all the variables used in the

functions. Compute the breakeven point.

d) Verify the breakeven point computed in Question (c) with the result in Question (b). Include the

computation of the breakeven point as part of the spreadsheet model in Question (a). The

breakeven point would be an outcome of the model.

e) From the results above, how would you characterize the profit function for this problem? What

would you recommend to the management team?

f) How many units must be sold (at $8.00 per unit) in order to have a total profit of $20,000?

formulate a model to answer the question, using parameters in Question (a). How many units must

be sold to produce a total profit of $20,000 if the unit price drops to $7.00? Note: when a new

model is created, different range names should be used.

g) New information about the sales is just available. It shows that the sales will not be more than

25,000 units. Continuing from Question (a), formulate a new model that reflects this new

constraint, while keeping all other elements in the original model. Test your model with

different quantities. What would the total profit be if the production quantity is 30,000 units?

Note: when a new model is created, different range names should be used.