excel

Q1. (30 marks) Create a spreadsheet named Lancaster Company. The Lancaster Brewery company is famous for their production of two types of beer: Blonde and Red to luxurious buyers. The normal ingredients supplied to each product as well as the profits earned for each product are described as follows. Each barrel of Blonde requires 20 lbs. of cor (unit as lbs), 2 ozs, of hops (unit as ozs), and 10 lbs. of malt (unit as lbs); it yields $20 of profit. Meanwhile, each barrel of Red requires 1 oz. of hops and 30 lbs. of malt; it yields $30 of profit. The company has a limited supply of ingredients that are used in both products. It has 500 lbs. of com, 60 ozs, of hops, and 900 lbs. of malt on hand. Managers of this company wishes to maximize the profit. a. Use Solver to determine how many barrels of the Blonde and Red to produce to maximize the profit. [25 marks) b. If the total profit is adjusted to $1500, how many barrels of Blonde are required to produce? Show the result on your Excel's worksheet. [3 marks]