Week 1 Decision Modeling & Analysis
- In cell G6 write a formula giving Ann’s total sales using the SUM function.
- Drag the formula in G6 to cells G7; G10, did this give the right values for Bobs, Charles, davids andellens total sales?
13 without using the sum function, enter a formula in cell b11 giving the total sales of boxes of Christmas cars.
14 drag the formula in cell b11 to cells c11:f11, did this give the right values for total sales of boxes of birthday, all occasion, thank you, and mixed cards?
15 in cell b12 enter a formula for the total profit made by these five persons selling Christmas cards. Do not enter any numbers, only cell references.
- drag the formula in cell b12 to cells c12:f12 did this give you the right values for the total profit on boxes of birthday, all occasion, than you and mixed cards?
17 using the sumproduct function, enter a formula in cell H6 giving Ann’s total profit from selling all types of cards.
18 drag the formula in H6 to cells H7:H10. Do not enter any numbers, only cell references. Did this give the right values for bobs Charles, davids and ellens total profit from selling all types of cards? If not, it is because you did not use absolute addresses for the profits per box. If that is the case, go back to cell h6. In the formula bar, highlight each unit profit and use the F4 key to make the reference absolute. After making all the references to unit profits absolute, now drag the formula in H6 to H7:H10. No did this give the right values for bob, Charles, david and elllens total profit from selling all types of cards?
19 in cell b13 use the MAX functionin order to determine the maximum Christmas card sales.
20 drag the formual in cell b13 to cells c13:f13. Did the give the maximum values of birthday, all occasion, thank you and mixed card sales?
21 in cell I5 enter “most Christmas sales.” In cell I6 enter an IF statement that will compare anns Christmas card sales to the maximum value in cell b13. If it is equal to this value, anns name (the entry in cell a6) should be printed in I6; otherwise nothing should be printed – this is accomplished by entering “”.
22 make sure the reference to cell b13 is absolute in the formula in the IF statement formula in cell I6. Drag cell I6 formula down to I7:I10. Did it print the person with the most Christmas card sales?
23 instead of the profit per box values in row 4, assume that ann’s profit is calculate as follows: 0.12 (chrstmas card sales)² + 0.02 (Birthday Card sales)³+ 3.50 + 0.05 (thank you card sales)*(mixed card sales) in cell c15 determine anns total profit.
24 in cell c16 use an IF statement that compares anns profit using the new method proposed in problem 33 (given in cell c15 with that of the old method proposed in problem 27 (given in cell H6) if the new profit is hight, “NEW” should be printed in cell c16: otherwise, “OLD” should be printed in the cell.
- suppose we wish to calculate the sum of all mixed card sales of those persons who sold less than 20 boxes of Christmas cards. In cell c19 use the SUMIF command to determine this quantity.