A507 Project 1

Due September 11, 2022

Dickson Inc. sells electronic component parts to manufacturers through a bid process. Once Dickson receives a quote request, he checks the unit cost on each component and then marks it up by some portion between 0 and 1. (Start with a markup of 0.4.) If a customer wants more than 100 units of a given component, he gives a quantity discount of 5% off the unit price on that component. If a customer wants more than 500 units of a given component, he gives a quantity discount of 7% off the unit price on that component.

Assume that it costs Dickson $500 to prepare and manage a bid, whether or not he gets the order. This cost is not directly passed along to the customer.(i.e., it does not get added to his quote amount.)

Obviously, the higher the markup, the less likely that it is that Dickson will get the order. He speculates that the relationship is something like

Probability of Getting Order = 1- Markup*.975.

The file Dickson.xlsx contains item numbers and the associated unit costs. It also contains five sets of customer requests for quotes. Prepare each quote on a separate tab of the workbook.You can include the cost and profit analysis in a "do not print" area on the same sheet.

Note that your model should be designed such that parts a through c can be answered by conducting additional analysis on that model. Do not build multiple models.

1. There will be two parts of the model that you develop. One part is for internal use only (e.g., cost, profit) and the other part is a quote that the customer is given. The model should be tested for all five customer quote requests that are provided.

The model should look up the item costs using VLOOKUP statements. It should calculate the actual cost of the items per customer quote, the price to quote each customer, the profit per customer (if Dickson gets the job), and the expected profit per customer (there are two possible outcomes: Dickson gets the job or does not get the job). Dickson is also interested in knowing the savings passed along to customers as a result of his quantity discount pricing. The final output of interest is expected profit as a percent of total cost (items and bid prep). Design the model to allow for up to 20 item numbers per quote, whether 20 item numbers are included or not.

For each quote, determine what markup maximizes the expected profit as a percent of total cost. Use a data table considering markups from 0 to 1 by increments of .05. Use conditional formatting to identify the maximum value. Display it highlighted in green with a blue font. What recommendation would you make based on this evidence concerning the markup Dickson should use?

1. Determine what markup level would allow Dickson to achieve an expected total profit as a percent of total cost of 20% (compute one ratio over all five quotes).

brief report for Dickson and turn in your work under Project 1 in Canvas. (If you work in a team of two people, just submit one project with both names on it.)Remember that on projects, you are evaluated on spreadsheet design as well as functionality.

(Note: use caution when naming cells in a sheet and then making copies of that sheet. You want to be very sure that your logic stays intact.)

File Home Insert Page Layout Formulas Data Review View Help Comme X A A E ap Wrap Text AutoSum Paste E Merge & Center $ ~ % 9 Conditional Format as Cell Fill Insert Delete Format Sort & Find & Ser Formatting Table Styles v Clear v Filter ~ Select v Undo Clipboard Font Alignment Number Styles Cells Editing Se 41 vix fx Dickson Inc. A B C D F G H K M N O P O R S T U Dickson Irc. Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Item # Unit Cost Item # Qty Item # Qty Item # Qty Item # Qty Item # Qty DS425 $ 7.22 DS375 101 AQ105 55 AQ105 30 AQ140 20 DS400 120 GR100 S 7.29 FC154 22 AQ110 12 AQ145 160 DS275 125 DS475 80 GR040 S 7.90 AQ100 56 AQ135 86 AQ150 45 DS350 89 FC162 150 FC146 8.46 DS325 40 DS275 65 DS200 300 FC122 36 FC170 65 DS300 8.61 GRO20 511 DS400 50 DS300 55 FC134 22 GRO40 500 FC142 8.75 GR090 65 DS450 26 DS325 78 FC154 78 GR080 100 GR060 8.78 KL409 260 DS450 30 DS500 315 GRO30 145 GR090 300 FC130 8.82 MG444 32 DS500 140 FC130 60 GR040 150 KL809 42 FC158 9.03 MG999 2 FC126 167 FC134 12 GR120 200 MG999 25 GR030 9.20 KL309 FC134 101 FC142 275 KL609 1 AQ125 S 9.51 FC150 88 FC150 100 MG888 5 DS225 S 9.58 FC158 250 GRO20 25 AQ100 S 9.59 FC166 25 GR050 50 MG222 9.60 GR080 40 AQ120 9.83 GR090 501 DS325 S 10.12 GR100 125 FC150 S 10.64 GR110 98 GR140 $ 11.14 KL709 255 DS200 $ 11.27 KL809 20 AQ150 $ 11.30 KL909 67 MG777 $ 11.56 GR050 S 11.58 AQ130 $ 11.89 Sheet1 + ady Circular References Accessibility: Good to go