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'.)

Marking guidance

Marks will be awarded for your ability to make model or models which address the client's request. (50 marks)

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.