help

  • Thread starter Thread starter valerie
  • Start date Start date
V

valerie

Hi,
I need help with the following:
I need to plot a 2-D column graph comparing the profit margin (y-axis)
for the 3 items (x-axis) for Malaysia and Singapore, using the data
below;

Malaysia
Item Mfr's Agent's Retailer's Selling GST Profit Total
Cost Charge Markup Price Margin

Lipstick 9.85 15.76 12.81 38.42 1.54 27.03 105.40
Toner 11.90 19.04 15.47 46.41 1.86 32.65 127.33
Cleanser 15.90 25.44 20.67 62.01 2.48 43.63 170.13
Total 37.65 60.24 48.95 146.84 5.87 103.31 402.86

Singapore
Item Mfr's Agent's Retailer's Selling GST Profit Total
Cost Charge Markup Price Margin
Lipstick 6.25 10.00 8.13 24.38 0.73 17.39 66.88
Toner 7.50 12.00 9.75 29.25 1.17 20.58 80.25
Cleanser 9.90 15.84 12.87 38.61 1.54 27.17 105.93
Total 23.65 37.84 30.75 92.24 3.45 65.14 253.06

How to go about doing it?
Thanks for any help.
 
Valerie -

You need one more column to compute profit margin. Your data fits in
columns A to H. In column I you need a formula to compute margin, which
is the profit (Column G) divided by total (Column H). In my worksheet,
the Malaysia data is in rows 4 to 6. So in cell I4, I have the formula
=G4/H4, and I have filled it into I5 and I6 (and I11:I13 for Singapore).

To make the chart, I selected the labels (Lipstick, Toner, Cleanser) in
A4:A6 for Malaysia, then held Ctrl while selecting the margins in I4:I6.
I ran the chart wizard. I selected Clustered Column in Step 1, and in
Step 2, on the Series tab, I clicked in the Name box and then selected
cell A2, which has the label Malaysia. I clicked Add, and a new entry,
Series 2, appeared in the list. I clicked in the Name box and selected
cell A9 ("Singapore"), I cleared the Values box (={1}) and selected
I11:I13 (the profit margin under Singapore), and finally I cleared the
Category Labels box and selected A11:A13. This last is optional, since
these are the same as for series 1.

The result is a chart with Malaysia and Singapore side by side, with
three pairs of data: Lipstick, Toner, and Cleanser.

- Jon
 
Back
Top