spreadsheet model to maximize profits

[jddhivyan1]

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: 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

 

[Andrew Poulsom]

We are not here to do your homework for you. Please put in time and effort on the problem yourself. When you have done that you can ask for help on a specific problem.