Quadcopter Drones (Simulation) Oftentimes in business world, we have to deal with uncertainty. That is, we need to be able to find a solution even if we dont know the exact values of some (or all) of our inputs. We might know the cost structure of our production process, and our marketing department might give us an estimation of the average demand and its standard deviation, but we dont know the exact the demand for our product. The demand is a random variable. We might know the order quantity that we place with our suppliers, but we dont know how many of the parts that we receive might be defective. This is a random variable. We have learned that simulation allows us to efficiently deal with such situations. First, we need to build a model, where all inputs are assumed to be deterministic (deterministic means, we pretend that we know exactly what the values of the random inputs are, even though we dont). Then, we have to replace those inputs that are random with Excel functions that draw from appropriate distributions (like RANDBETWEEN(1,10), where it changes every time you hit F9). Then, we use a two-way data table to make 100s or 1000s of random draws for each of the inputs and record the output values. You were just hired to work for the analytics team of the supply chain department of a company Nittany Production of Home and Recreational Drones (NPHARD) that manufactures quadcopter drones. Your job is to determine the order quantity from your main supplier such that yearly profit is maximized. The drones are popular and are sold for $200 each. Because of very high shipping costs and uncertain shipping time, you dont sell drones directly to consumers, and instead are working through main online stores and retailers, who place orders every month. You know that the demand for drones is normally distributed with average demand (100 drones) and standard deviation (15 drones) per month, but you dont know the actual demand until the beginning of each month. The parts needed to make drones can be ordered from a supplier or 3D printed in house or both. Supplier: The supplier will ship the parts to your factory in the beginning of each month, but due to capacity planning requirements of your supplier, the quantity that will be shipped to you each month must be the same and must be fixed in the beginning of the year in increments of 5 (5, 10, 15, 20, ....). Because of the extreme complexity of parts (or maybe because NPHARD worked very hard to find the cheapest supplier), some of the parts in each monthly shipment are defective and cannot be used for production. You tried to find some data by talking to your Quality Assurance (QA) team, but the best you could estimate based on historical data is that the defect rate (fraction of defective parts) in each monthly shipment can be any whole percentage number between 10% and 20% with equal chance (10%, 11%, ..., 19%, 20%). Of course, if the parts are defective, you are not paying for them to your supplier, but if the quantity that arrived from the supplier and passed the QA is less than the actual demand for that month, NPHARD needs to 3D print any remaining parts. Printing 3D parts Printing 3D parts is not an ideal situation since 3D part are more expensive. The parts that you order from your supplier cost $75, but 3D printed parts cost $195. The good thing is that the 3D printer is located in house, so you can print missing parts quickly in order to fill the demand, so you can maintain your reputation as a reliable manufacturer. Of course, if the number of parts arrived from your supplier and passed QA is higher than the demand in that month, you can store extra parts in your warehouse and use them next month. In this case, there is nothing to 3D print that month.

Here is the sequence of events each month. 1. In the beginning of each month, the retailers tell you exactly how much they want for that month (your demand). 2. Then, whatever amount you agreed upon with your supplier arrives. 3. You check the supplier parts for defects. 4. You 3D print whatever you need, which is the demand for that month minus whatever good parts arrived from your supplier, minus whatever you had in inventory carried over from last month. 5. You ship everything at the end of the month. The planning horizon is 12 months. NPHARD needs to decide how much to order from the supplier to maximize total yearly profit. That is, at the beginning of the planning horizon (lets say January) it needs to commit to a constant amount of quadcopter parts that will arrive each month from its supplier. You start with zero quadcopter parts.

Use a simulation in Excel to find the optimal order quantity and plot the graph of the expected profit vs. the order quantity. Submit an Excel tab with the model and simulation and the answers to the questions below. Tips: - You should have 3 main parts in your model: general inputs, calculations and output for each of the 12 months, and a 2-way data table with 1000 (or more) rows for simulations and appropriate order quantities o For each month youll need to calculate: The random demand (recall how we generated a normally distributed random variable) Random percent defects (between 10% and 20% uniformly) The amount of good parts arriving from your supplier Beginning inventory for the month (which is zero in month 1) The amount to 3D print (youll never 3D print more than you need) Ending inventory (if any) o From this youll need to calculate the supplier costs (you are charged only for those items that are GOOD, not all the items you ordered) plus 3D printing costs. From this, you can calculate the total revenue (based on realized demand [the demand that actually occurred], not the mean demand), and from this profit. - One of the inputs of the 2-way data-table should point to an empty cell in your spreadsheet - The demand and defect rate are different every month (drawn randomly every month), so you have to make a table that tracks what you do every month - Make sure you allow for extra parts from your supplier to be stored in your warehouse and that you only 3D print if you need to (think Plumpynut model) - Use appropriate cell styles for everything, including random inputs (refer to Sams bookstore solution file, or the Excel template SCM421 Excel Template.xlsx in the General Information module to get a spreadsheet with this cell style) - For random normal, use NORM.INV(), for random uniform use RANDBETWEEN(). If you need to make a random variable that can take any values between 1% and 5%, a useful approximation would be RANDBETWEEN(1,5)/100. This is not ideal, since your numbers would only be 0.01, 0.02, etc., and you wont get 0.015, but this is good enough for us. You can do better using RAND(), which would allow for any fractional value, but you dont have to do it. - Do not try to do everything at the same time. First, build a deterministic model that tracks your performance if everything was known to you (no random variables). Then, replace appropriate inputs with random function. Then, simulate and average results. Then, plot. - Do not combine this model with any other Excel models that you do on different tabs. Because this is a computationally intensive spreadsheet, its better to have it on its own workbook.

A. What is the optimal amount for you to order in order to maximize the average profit?