About this lesson
Percentages and GST: from till takings to revenue
To remove GST from a GST-inclusive amount, divide by 1.1: =B2/(1+$G$1) with the 10% rate in G1. The GST component is the inclusive amount minus the ex-GST amount, which is always one-eleventh of the inclusive total.
=B2/(1+$G$1)Worked example
Strip GST out of store sales, split out the GST component, and work out each store's share of revenue.
| Cell | What it works out | Formula | Result |
|---|---|---|---|
| C2 | Bondi JunctionRevenue ($k) | =B2/(1+$G$1) | 1,200 |
| C3 | ParramattaRevenue ($k) | =B3/(1+$G$1) | 900 |
| C4 | ChatswoodRevenue ($k) | =B4/(1+$G$1) | 1,000 |
| C5 | NewcastleRevenue ($k) | =B5/(1+$G$1) | 600 |
| C6 | WollongongRevenue ($k) | =B6/(1+$G$1) | 300 |
| D2 | Bondi JunctionGST ($k) | =B2-C2 | 120 |
| D3 | ParramattaGST ($k) | =B3-C3 | 90 |
| D4 | ChatswoodGST ($k) | =B4-C4 | 100 |
| D5 | NewcastleGST ($k) | =B5-C5 | 60 |
| D6 | WollongongGST ($k) | =B6-C6 | 30 |
| C7 | TotalRevenue ($k) | =SUM(C2:C6) | 4,000 |
| E2 | Bondi JunctionShare of revenue | =C2/$C$7 | 30% |
| E3 | ParramattaShare of revenue | =C3/$C$7 | 22.5% |
| E4 | ChatswoodShare of revenue | =C4/$C$7 | 25% |
| E5 | NewcastleShare of revenue | =C5/$C$7 | 15% |
| E6 | WollongongShare of revenue | =C6/$C$7 | 7.5% |
The data
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Store | Sales inc GST ($k) | Revenue ($k) | GST ($k) | Share of revenue | GST rate | 10% |
| 2 | Bondi Junction | 1,320 | |||||
| 3 | Parramatta | 990 | |||||
| 4 | Chatswood | 1,100 | |||||
| 5 | Newcastle | 660 | |||||
| 6 | Wollongong | 330 | |||||
| 7 | Total |