spreasheet model to maximize profits using Solver

[jddhivyan]

A bakery shop is selling 3 kinds of cakes (C1, C2 and C3) and the selling price is $25, $35 and $40 respectively.
The raw materials to make the cakes are (A, B, C, D) and the unit of each required to make the cakes are given in the table.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Cake[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Price($)[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Available Qty[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]300[/TD]
[TD]250[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help with the followin questions using Solver. Please illustrate solution in excel file.

The objective is to maximize the revenue.
(a) Design a spreadsheet model to show how the production manager can optimize his profit.
(b) What is the objective functions and formula used.
(c) Elaborate the purpose of each constraint used.
(d) What is the optimal number of cakes to produce for each category and what is the revenue?
(e) If C1 is a much popular cake such that the quantity of C1 must be at least twice as much as C3, will there be a change in production plan and revenue? Explain!

Thanks

 

[VoG]

Duplicate of http://www.mrexcel.com/forum/excel-questions/666472-spreadsheet-model-maximize-profits.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).