Modeling Excel

1 MSDM 22025 Evaluation 2 This Evaluation comprises two distinct questions The required submission consists of two separate Microsoft Excel files Each file must correspond exclusively to one question Submissions that consolidate both questions into a single Excel file will not be considered and will receive no grade Excel models delivered for this evaluation must follow all requirements of the internationalstandard modeling methodology first devised by Winston Albright with enhancements as presented during the MSDM course sessions or available in the course material Do not use integer constraints and in accordance with business practice ensure that all variables are shown with two decimal places Cell labels must include the correct dimension of the data to which they refer eg units such as micrograms or gallons as well as derived or compound units such as meterssecond and so on Questions begin on the next page 2 Question 1 Atomic Energies Org is a publicprivate partnership that produces highgrade plutonium based products for vetted governments around the world Two of its main products are Atomic Transformed 1 and Atomic Regular 2 The uranium needed to manufacture each of these two products incurs a processing cost of 25738789 per gram Processing one gram of uranium requires 37 machineweeks Each gram of processed uranium yields 795 milligrams of Atomic Transformed 1 and 871 milligrams of Atomic Regular 2 Atomic Transformed 1 is sold for 568336 per milligram and Atomic Regular 2 is sold for 521475 per milligram Atomic Energies Org also produces premium versions of both products for a small selected group of governments Premium Atomic Transformed 1 is sold for 875369 per microgram and Premium Atomic Regular 2 is sold for 799366 per microgram In order to produce the Premium version of Atomic Transformed 1 a milligram of the main product Atomic Transformed 1 must be processed for 27 machineweeks This costs 124333 and yields 182 micrograms of Premium Atomic Transformed 1 To produce the Premium version of Atomic Regular 2 requires 22 machine weeks processing time of its main milligram equivalent costing 124366 and this yields 135 micrograms For the next operating cycle Atomic Energies Org has 8457 machineweeks available and 6589 grams of readytouse uranium 1 Maximize the profit for the next operating cycle Answer the following questions in a textbox for Atomic Energies Org 2 How much Premium Atomic Transformed 1 is produced 3 What is the revenue from Premium Atomic Regular 2 4 How many of the available machineweeks are used 5 Overall operating costs for the next operating cycle are estimated at 20 billion Explain whether and why Atomic Energies Org will at least break even End of Question 1 Question 2 begins on the next page 3 Question 2 Pilkington Glass Ltd uses three different processes to produce various types of glass Each process involves blending types of quartz sand QS and at times glass float sheets in the companys blender It costs 2230 to run Process 1 for an hour This requires two sacks of QS Type 1 and three sacks of QSType 2 The output from running Process 1 for an hour is two float sheets of GlassType 1 and one float sheet of GlassType 2 It costs 2960 to run Process 2 for an hour and this requires one sack of QSType 1 and three sacks of QSType 2 The output from running Process 2 for an hour is three float sheets of GlassType 2 To run Process 3 for an hour it costs 2170 and requires two sacks of QSType 2 and three float sheets of GlassType 2 The output from running Process 3 for an hour is two float sheets of GlassType 1 one float sheet of GlassType 2 and two float sheets of GlassType 3 3945 sacks of QSType 1 are available for purchase at 5287 per sack For QSType 2 6458 sacks are available for purchase at 6490 per sack The blender has been cleared for 3002 hours of potential use All glass produced can be sold at the following prices GlassType 1 8996float sheet GlassType 2 9292float sheet and GlassType 3 9192float sheet To ensure model flexibility and thus adapt to forthcoming changes in process running requirements and possible outputs the Board at Pilkington want a model that shows all possible combinations between Processes on the one hand and QSTypes and Glass Types on the other even where current values at these intersections are zero Furthermore since any one GlassType is a potential input to a Process Revenue calculations for each type must be based on net float sheets sold where net float sheets sold float sheets produced float sheets used in production 1 Maximize Pilkingtons profit In a text box report the following to the Board 2 The number of sacks purchased and used of QSType 1 3 The total running cost of Process 2 4 The net float sheets sold of GlassType 2 5 The revenue from GlassType 3 6 The number of hours run by Process 3 End of Question 2