Project on Selling Malibu

Malibu Mix

Different from the Part I, there are five sheets in total ,which are named "One-day-Optimisation", "Main sheet", "Data Set", "Data for optimisation" and "Stored Information" respectively.

In the first sheet, clients can click on the "Click to generate temperature" to get a temperature number in relative cell, then click "Click for Optimization" button to compute the one day optimized results including revenue, profit and decision made and more detailed data. If client want to play once more, just click "Clean the data" button to clear previous data, then operate the previous two buttons again. In the second sheet, which is the main sheet, clients can enter the data for Price, Malibu per jug, Ice cubes per cup, which are in blue, and click on cell that is in C4, click "Click to generate one temperature" to obtain value in cell(C4), then click "Strict(automatic) computation based on Input variable" to generate rest of results that are needed. If clients want more flexible decision making, they can firstly put data into Price, Malibu per jug , and Ice cubes per cup, then click "Total Demand" to get the result that is stored in cell(C9). After it, they can put data they want into cells of "Quantity bought", then click "Test whether input variables is satisfied" to examine if the input data is available. If it can meet the budget, click on "Balance and Sale, Profit" button to get the rest of results that are needed. For ten days optimization, clients can click "Clear all data", then click "Click to generate ten days' temperature", then click "Click for ten days optimisation" to get all the results. After client try different input data to get the result for the first day, they can click "store the information", then click "Go to "Stored Information"", then choose cell in row cells of "temperature", and click "store Information" to input data recorded in that column. Click "Clear all data" if clients tend to delete them.

This model is established to let clients make decisions automatically to show the detailed results and optimal decisions they should take on, including decisions for one day optimization, and ten days optimization if given temperature, while they can operate it manually by input appropriate data needed to make them better catching feelings of decisions they should make. As mentioned above, there are five sheets in total. In the first sheet, dependent variables and independent variables are separated, with dependent variables such as balance(Capital and ingredients), Total demand, sales, cost of cups, Malibu CocaCola and Ice cubes, and profit and accumulated profit in the same column, which Quantity of ingredients bought are beside it. Independent variables like temperature, price, Malibu per jug, and Ice cubes per cup are put in the same column. The isolation makes clients more clearly see the results with appropriate clarification and make them not easily lost in sea of data. Footnotes of variable "Total Demand" are made to notify clients that the value of it depends on those in green color. Such a clarification will help clients better understand the logic behind. Moreover, when looking at the column that represents denpendent variables, it's more like a accounting sheet, for from the upper is the amount of money clients have, then remained ingredients they are use, then revenue, cost and finally profit and accumulated profit. Such a design makes sense of people's logic in calculation. However, not all people like seeing data in this way, so there may other appropriate designs. In the second sheet, the one-day design is quite the same as the in the first sheet, except that variables for quantity of ingredient bought are integrated with independent variables, from which temperature is put to the top. The change of design is mainly because in this sheet, clients are allowed to input data of quantity of ingredients bought and adjust independent variables such as price, Malibu per jug, and Ice cubes per cup, so classifying them into the same category. Values of Malibu per jug and Ice cubes per cup are constrained by specific amount by special button because only with amount demand can be calculated based on the data given. If clients enter data out of range, demand is unknown by now, leading to no results. This kind of constraint will make data input more robust. If more information about data is known, constraints can be lessen. Price also has constraint, for base on the cost of ingredients given it will gain no profit if price is less than 30 pence, and demand will be very small if price is more than 50 pence. The table extend one day data to ten days' to show optimized ten days' results simultaneously. Temperature generation is base on probability distribution given. To convenient clients, two buttons are created to make them generate one temperature on any day or ten days' temperature at one time. To make clients better catch feelings of what decisions they should make, they can manually input decision variables. Three buttons are created to decompose the procedure of output of results. First step is to calculate the total demand or test whether these input variables meet the needs of budget, otherwise cell that represents total cost will be filled into red color to remind customer or the type of variable that is not satisfied with the budget will appear in message box until all meet the budget. The second step is to get the rest results needed to know by the third button. Such design helps clients further realize the problem they will meet when making decisions and what is vitally important in determining the decision(total demand is the most important one). Only variables like price, Malibu per jug, and ice cubes per cup are needed if clients want to use my special optimized method. Behind the method is the assumption that as soon as total demand is known, purchase decisions are made to satisfy the needs and minimize the balance amount of ingredients. Hence it may not get the most optimized result sometimes. Another optimized method is accurate in that it compare most of possible results and select the one that has the biggest value of profit. To calculate to ten days' optimized results, data in table should be cleared first by button, or it will affect the result. A more detailed function to make clients better catch the feelings of decision making is that it can store data at a time and transfer it to the other sheet named "Stored Information" to compare the difference between data from the same variables. The third sheet contains the basic data that is used to calculated the results, including the probability