Excel for Decision Support: Optimize Production and Cost Analysis

AS01: Excel for Decision Support Section #: Buad 283 -01 Your Name(s): Student ID(s): Please answer all questions within their respective worksheets. You are being graded on the final answers , worksheet model , formatting , and Solver entries. Remember to include an Answer Report for each question. Save the workbook using your name (e.g., AidanCole.xlsx or, if working with a partner, AidanColeLionelMessi.xlsx).

Question 01: Production Mix Standing Desks Profit per Unit Max Demand Rizer Desk $1,150 600 Briton Desk $850 500 Siegel Desk $625 350 Required per Unit Glue (ml) Casters (#) Tubes (m) Motor (#) Rizer Desk 50.0 8.0 12.0 2.0 Briton Desk 72.0 4.0 8.0 1.0 Siegel Desk 45.0 4.0 10.0 0.0 Raw Materials Glue (ml) Casters (#) Tubes (m) Motor (#) Physical Inventory 42,500 6,500 8,000 1,200 A company makes three different types of standing desks: Rizer, Briton, and Siegel. The marketing has asked for your help in determining how many of each it should produce this year in order to maximize their total profit, limited by their current levels of raw material inputs. With carrying cos being extremely high, the company would rather produce less than what is demanded (to ensure s churn) than have to carry inventory to next period.

3.0 Points Wood (m) 52.0 32.0 20.0 Wood (m) 35,000 group sts sales

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Question 02: Call Centre Logistics Call Demand Call Centre Locations Customer Regions (Minutes) Vancouver Edmonton Winnipeg Toronto West Coast 60,000 $1.78 $2.00 $2.05 $2.08 Mountain 45,000 $1.85 $1.86 $2.03 $2.05 Central 38,000 $1.97 $1.84 $1.75 $1.87 East Coast 50,000 $2.16 $2.45 $2.50 $1.71 Total Minutes Available per Centre 48,000 45,000 36,000 43,000 A company wants to minimize the total cost of long distance charges for their call centres. The table below spec average cost per minute from each call centre location to a particular customer region. How should the compan call volume amongst the various centres in order to meet demand? For the BEST result, please choose "Simplex LP" as the solving method for this problem, rather than GRG Non

2.8 Points Halifax $2.10 $1.95 $1.65 $1.63 42,000 cifies the ny distribute its nlinear.

Question 03: Siberian Forest Cats To ensure optimal health, a breeder needs to feed their Siberian cats a daily diet containing a minimu grams (g) of fat, 55 g of carbohydrates, and 9 g of protein. However, each cat should be fed no more tha ounces of food per day (or they get too fat!) Rather than order one commercial cat food, it is healthier to order two types: FRESHLY and NUTRI-DIE then blend them together for an optimal mix. FRESHLY contains 4 g of fat, 7 g of carbohydrates, and 1.1 protein per ounce, and costs $0.48 per ounce. NUTRI-DIET contains 6 g of fat, 8 g of carbohydrates, and protein per ounce, at a cost of $0.63 per ounce. Assuming the breeder wishes to minimize their daily cost per cat, yet ensure that they are properly fe the optimal blend of these two cat foods and the average daily cost per Siberian cat?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

2.8 Points um of 38 an 8 ET, and 1 g of d 1.4 g of ed, what is

Question 04: Excel Decision Support Tools 1.4 Points BANK of OC: Loan Department Loan Amount $30,000 for 4-years of tuition and living expenses Term in Years 4 Annual Interest Rate 5.00% 1. A loan application form provides the input values shown above. ANSWER: Calculate the monthly loan payment using the appropriate function: Present you 2. The customer wants to know what the monthly payments would be ANSWER: cell B6 would he/she have to negotiate in order to make this happen? for $25000 at 4.6% , $28000 at 4.8% , and $32000 at 5.2% . Using the appropriate What-If tool, create and summarize these three scenarios as Low , Med , and High using a Summary tab. 3. The customer wants the payment to be exactly $675.00 per month on the original loan amount of $30000 . What annual interest rate in

ur answer as a positive value

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

attachment_2

Related Documents