Construct spreadsheet model in excel to compute total profit

Assignment Help Operation Management

Reference no: EM131230296

You have been called in to consult Tammy Duncan, the operation manager and head beer meister of Black Warrior Brewing Co. which produces the very popular BW Ale. Tammy would like to develop a spreadsheet model to help her determine the best sales price for BW Ale which she currently sells for $45.00 per gallon. BWB can produce 5,400 gallons of ale per week at a production cost of $16.00 per gallon. The primary ingredients of BW Ale are barley and hops. Each gallon of BW Ale requires 1.30 pounds of barley which costs $4.25 per pound. Each gallon also requires 0.50 pounds of hops which costs $8.50 per pound. The fixed cost of operating the brewery is $4,900 per week. Tammy has done extensive market research to estimate the price-demand curve for BW Ale. She has estimated that at a sales price of $38.00 per gallon, BWB would sell 7,700 gallons of BW Ale per week and that a $0.01 increase/decrease in price would decrease/increase sales by 6 gallons per week.

Construct a spreadsheet model in excel to compute the total profit [Hint: If demand corresponding to some price exceeds capacity, only the capacity can be sold. Also if capacity exceeds demand, only the demand can be sold. The spreadsheet model needs to be flexible enough to handle either case.]

Reference no: EM131230296

Questions Cloud

Calculate total number of vulnerabilities for each platform : Calculate the total number of vulnerabilities for each platform. What is the potential impact of the security problems for each computing platform on the organization?

Write about how the great honeyguide communicate with human : Title: THE Great honeyguide. Description: I have to write about how the great honeyguide communicate with human and help humans too

Assembling machinery and evaluating quality : Suppose that an employee has two tasks to perform—assembling machinery and evaluating quality. Suppose that the supervisor can only observe how many machines have been assembled by the worker. Is a hierarchical firm structure necessary under this sit..

Calculation of taxable income and current tax liability : Accounting for Corporate Entities Assignment - Prepare a schedule that shows the calculation of taxable income and current tax liability for AMC (Sales) Ltd for the year ending 30 June 2016 and provide the relevant journal entry

Construct spreadsheet model in excel to compute total profit : You have been called in to consult Tammy Duncan, the operation manager and head beer meister of Black Warrior Brewing Co. which produces the very popular BW Ale. Construct a spreadsheet model in excel to compute the total profit.

Explain what is brendas break-even price for a dozen of eggs : What is Brenda's break-even price for a dozen of eggs? Explain how you found that answer. If the market price of a dozen eggs at the local farmers' market is $1.45 per dozen, should Brenda continue producing eggs in the short run?

Understanding of ballet and kabuki as classical art forms : What contributions to society do these two art forms make? -- Are these contributions viable in the 21st Century?

Explain how a cyber attack can be carried out : Describe some of the main points of the Digital Agenda for Europe.

Define a supply chain and identify each of its components : Distinguish between a push-based and a pull-based model of supply chain management and explain how contemporary supply chain management systems facilitate a pull-based model.

User Account

All Pages