You work at a professional services consulting firm. One of the major business lines of that business

is to provide advice to lobby groups. One of the firm's clients is concerned about something it calls

'middle class welfare', where the government allows a variety of laws to operate which advantage

the middle class and the rich. The client is particularly interested in the impact of the dividend

imputation scheme on government revenues.

The client suggests that dividend imputation cease, and all dividends are treated as ordinary income

in the hands of the recipient.

The client asked your firm to estimate the impact on the Commonwealth Government revenues if

his suggestion is implemented. He thinks there will be huge savings, and these can be used to reduce

tax rates on individuals.

The client also wants to get a sense of who is benefitting from this scheme. For example, if this was

removed, what impact if any, would there be on females v males? What are the characteristics of

the major classes of beneficiaries of this scheme? What are the major characteristics of people who

do not benefit from this scheme? For some strange reason, the client really dislikes Canberra and is

convinced that this scheme unfairly benefits residents of that lovely place. You better address this

issue, as your manager does not want the client to be embarrassed if he makes an erroneous

statement in public. Your manager wants to know which areas are most advantaged by the current

scheme.

Your manager is delighted, as this job will generate considerable income for the firm. She wants you

to build a model based on relevant data from some or all of the following tables (Tables 3, 4, 5, 6, 7,

8) found at the Taxation Statistics website

https://www.ato.gov.au/About-ATO/Research-and-statistics/In-detail/Taxation-statistics/Taxation-

statistics-2016-17/?anchor=Individualsdetailedtables#Individualsdetailedtables.

You trust your manager, but you are welcome to look for and incorporate data from other tables, if

you think this will assist your response.

Your manager advised that that you may find something called 'Share of franking credit from

franked dividends' in some of the tables. She advised you to treat this as a normal franking credit.

Briefly, the share of franking credit arises from certain payments to and from trusts. She suggested

you don't worry about the detailed tax law, just treat these the same way as other franking credits.

(At this point, you did not know whether to thank her profusely or take your mum's advice and apply

for that job as an undertaker, as they seem such happy people.)

Your manager has hinted strongly that she wants you to identify any major assumptions and

limitations in your analysis. She also asked you to identify potential 'political' risks if the proposal is

implemented and briefly discuss ways these could be managed. (She does not want the client to look

like a fool if another lobby group raises concern about a section of society being disadvantaged

'unfairly'.)

You will need to follow the basic rules of spreadsheet design, such as separating the data from the model etc. Ensure there is no 'hard-coding' in the model.