I have attached instructions. Deadline is Monday 6/28/21 at 3 pm North Eastern Time (New York). Only the excel documents are necessary. The two Pdfs are merely instructionss
Formatting MonthTotal for 6 monthsEmployee's contribution Calculating Employee IDJanuaryFebruaryMarchAprilMayJuneper employeein the first six monthsBonus 312232731283031170 413212128263430160 514261923243327152 615202226212929147 716242527253232165 Average Total Monthly Target sales232228243129 Questions: 1. Improve the presentation of the data and texts (use formatting, etc.) 2. Using appropriate formulas, fill the yellow colored cells in the table 3. Calculate employee's bonus of 12% of his/her total sales if his/her sales are above the monthly target for the month of June (blue area) 4. Using conditional formatting paint cells in red if an employee was not performing during the month of May (his/her sales are less than target) 5. Create a chart to compare the monthly average vs. target sales (separate sheet) Hint: Employee's contribution is the ratio between total for the employee divided by the grand total for all employees presented in % Forecasting Store AreaSales 678443 318734 400133 580738 800247 613139 732743 651240 859753 768646 751544 550437 805348 520436 683045 1. Create a linear graph of the data presented 2. Create a Scatter plot (XY chart, Sales=f(store area)) chart and add a linear trendline 3. What is the slope of the trendline? 4. Assuming the company built a new store with 6600 sq ft, what level of sales can they expect (sales are presented in the table are in the 1000s) 5. Assuming the company built a new store with 2000 sq ft, what level of sales can they expect (sales are preseneted in the table are in the 1000s) Solver A firm has approached to you with an optimization problem. They produce three products: Product A, Product B, and Product C. The profit margin for the products are $18, $22, and $20, respectively. However, they have a few constraints: - The firm has a capacity of producing 360 units of the products above, all combined, per day; - Due to their customer base, they should produce at least 100 units of each products; Assuming that all their products are going to sell in the market at the expected price, tell the firm at what level they should produce each of their products for maximum profits given the constraints above. Q1.ConvertUserInput Follow the instructions on pages 71 and 72 of your textbook to draw a button and assign a macro to it. Then, instead of recording the macro, follow the instructions from the supplementary instructions file (the PDF on the Blackboard) to write a macro that asks for a user input (the finalGrade), and uses either of the two functions that I have introdeuced (in the PDF file) for converting the finalGrade to a letterGrade. Finally, show the letterGrade to the user. The functions are already written for you (look at the VBA codes and the instructions in the PDF file for more details.) Q2.LetterGrades First NameLast NameFinal Grade (from Quizzes)Final Grade RescaledLetter Grade ErinNephew1575 BillAnderson1785 AndrewJohnson14.572.5 MayaRodriquez15.577.5 NatashaCook17.587.5 AriPardo16.2581.25 RamonDawes17.2586.25 MateusSantos15.7578.75 SerjioRanieri16.7583.75 PedroAlvaz15.7578.75 Calculate the letter grades using a macro written in VBA. You can find instructions in the supplementary information file. I have written two functions (either of them works fine). All you need to do is to use the function appropriately (as I have explained in the other file), and to use a loop to go through the final grades and assigns the corresponding letter grade in the correct spot, that stops when there are no more grades to map on its letter grade equivalence. Come up with an appropriate loop that does the job, and ends the loop correctly when it should end the loop. The letter-grade mapping is: A: 92 and above A-: Over 90 and below 92 B+: Over 88 and below 90 B: Over 82 and below 88 B-: Over 80 and below 82 C+: Over 78 and below 80 C: Over 72 and below 78 C-: Over 70 and below 72 D: Below 70 Q3.Solver Chicken SandwichEgg SandwichFriesGarden Salad Calories400250180160 Salt5462 Sugar2244 Fat3015255 Cost$7.99$3.99$2.99$3.99 Consider the problem of diet optimization based on cost and different nutritious facts. There are four different types of food. Your task is to find a minimum-cost diet that contains: - At least 500 calories; - No more than 20 grams of salt; - No more than 20 grams of sugar; - At least 20 grams of fat. ITM 510 Spring 2020 Project 4 Page 1 Supplementary Information for Project 4 During the previous session of the class, due to the time it consumed for us to go through the VBA coding example that I had prepared for you, I was not able to introduce some very useful VBA instructions. Thus, I am using the opportunity of this project for introducing a few of those. This will give you a chance to work with these instructions and use them for making your excel sheets more powerful in the future. The first two instructions are helpful when we need to make a decision based on the value of an expression. This sounds similar to IF and nested IF, but here, we don’t have to implement nested IF anymore! We can use the “If” structure in VBA and by simply using the “Else If” instruction, simplify the code – nothing like the nested IF in the worksheet formula! Take a look at the following code, which is assigns letter grades based on our definition from the question on the first tab of our previous exam: If finalGrade >= 92 Then letterGrade = "A" ElseIf finalGrade >= 90 Then letterGrade = "A-" ElseIf finalGrade >= 88 Then letterGrade = "B+" ElseIf finalGrade >= 82 Then letterGrade = "B" ElseIf finalGrade >= 80 Then letterGrade = "B-" ElseIf finalGrade >= 78 Then letterGrade = "C+" ElseIf finalGrade >= 72 Then letterGrade = "C" ElseIf finalGrade >= 70 Then letterGrade = "C-" Else letterGrade = "D" End If Please note that in an “If” command structure, all “ElseIf” and the “Else” sections are optional. The same logic as above (assigning the letter grades) can be implemented using another command which is design specifically for similar cases in which there are different decisions ITM 510 Spring 2020 Project 4 Page 2 that can be made based on different values of a variable or an expression: The “Select Case” structure. The following piece of code does the same for us as the code above: Select Case finalGrade Case Is >= 92 letterGrade = "A" Case Is >= 90 letterGrade = "A-" Case Is >= 88 letterGrade = "B+" Case Is >= 82 letterGrade = "B" Case Is >= 80 letterGrade = "B-" Case Is >= 78 letterGrade = "C+" Case Is >= 72 letterGrade = "C" Case Is >= 70 letterGrade = "C-" Case Else letterGrade = "D" End Select To help us with this conversion every time, all we need to do is to save one of these codes in a VBA “Function”, so we can simply call the function, and the function does the conversion for us. This function receives one argument, which is the finalGrade – the grade to be converted to a letter grade – and returns one value – the corresponding letter grade – using one of the code blocks above. Assume that we name the function as “LetterGrade_IF” (or LetterGrade_CASE() hereafter). The input argument comes in parenthesis just after the function name. To tell Excel that we are writing a function, not a simple macro, we should use the keyword “Function” instead of “Sub” in the first and last line of the function. For this case, we will have: Function LetterGrade_IF(finalGrade) End Function To use the function, we can simply pass to it as the input argument the grade that we want to be converted to a letter grade. We can store this grade in a cell and then reference the cell to ITM 510 Spring 2020 Project 4 Page 3 this function. Assume that the numeric finalGrade is stored in cell A1. One way to reference this cell and use its contents, is by the “Cells()” command. The same “Cells()” command can also write contents into a cell. In the following example, we are reading the finalGrade from the cell A1 (referenced as 1,1) and will write the corresponding “letterGrade” variable – using the “LetterGrade_IF()” function – into the cell A2 (references as 2,1): Cells(2, 1) = LetterGrade_IF(Cells(1, 1)) Please note that the “Cells” command on the left side of the “=” sign will receive the output from the function “LetterGrade_IF()” (the one that we introduced by IF, or otherwise the other one using SELECT CASE) and puts it in the cell A2 (row 2, column 1), and the “Cells” on the right side of the “=” sign is the input argument to the “LetterGrade_IF()” function, which contains the finalGrade variable which we want converted. I have written both functions in the XLSM file attached in this project. To use both IF and SELECT CASE, I have written two functions, and the function