Create an excel spreadsheet that gets quotes for each quantity and each markup on cost at each quantity.

Question

1. Campus Manager needs a quote for a product.
2. Here are our inputs:
3. Shirt Cost: $12 per shirt
4. Print Cost: $14 per shirt
5. Set Up Cost For Order: $20 flat cost
6. Shipping Cost: $1 per shirt
7. Sales Manager Commission: 7% of the entire order Quantities
8. 20
9. 40
10. 220  

Answer 1

Rohit, here is a solution.

In row 1, we will put the headings

a1) Shirt Cost

b1) Print Cost

c1) Shipping Cost

d1) Quantity

e1) order setup

f1) commission

g1) total costs

h1) quote

i1) markup on cost

in row 2, we will put the costs

a2) 12

b2) 14

c2) 1

d2 ( nothing for a moment)

e2) 20

f2) .07

g2) =d2*(a2+b2+c2)

h2) =(1+f2)*(g2+e2)

i2) =(h2-g2)/g2

Note, I assume that the phrase “markup on cost” means the fraction of your cost that got added to the quote. These included: order setup and commission.

Now using one of the basic magic features of Excel, you can use the same formula for all three quantities.

First, select row 2, copy it down to rows 3 and 4. If you’ll look in columns g, h, and I, you’ll see that the formulas in a column are referring to the same column, but different rows. This is a good thing.

next, enter the quantities (20, 40, 220) into cells d2, d3, and d4 respectively.