need help please

Ivanka's Budgeted Income Statement You are the accountant for Ivanka Lid which operates a small mixed business. The following estimates relate to the base year (Year 1): Sales of product A $ 100 000 Sales of product B $270 000 Sales of product C $350 000 Sales of product D $290 000 Cost of goods sold (Product A) 56% of Product A sales Cost of goods sold (Product B) 60% of Product B sales Cost of goods sold (Product C) 65% of Product C sales Cost of goods sold (Product D) 62% of Product D sales Lease of premises $25 000 Electricity & gas (fixed) $1 000 Electricity & gas (variable) 0.5% of total sales General expenses $75 000 In addition to the items above, Ivanka Lid is paying commission to its three salespersons. The following are the rules for calculating commission: $0.10 for every $1 of Product A sold . . . $0.12 for every $1 of Product B sold $0.15 for every $1 of Product C sold $0.11 for every $1 of Product D sold You MUST use Vlookup to calculate the commission. A bonus (Bonus A) of additional $0.02 is added to the commission of Product B, C and D sold. For example, $0. 14 for every $1 of Product B sold, $0.17 for every $1 of Product C sold, and $0. 13 for every $1 of Product D sold. This bonus is ONLY given if the salesperson can sell $90 000, $120 000 AND $100 000 for Product B, C and D respectively. You MUST use if statement to calculate Bonus A. Take note your model must be flexible enough to accommodate changes in the bonus amount, such as changing $0.02 to $0.05. A second bonus (Bonus B) of $1 500 is also given if the salesperson can achieve sales of $35 000 for Product A OR $95 000 of Product B. You MUST use if statement to calculate Bonus B. Take note your model must be flexible enough to accommodate changes in the bonus amount, such as changing $1 500 to $2 000. The following are the salesperson and their respective sales (for Year1): Salesperson's sales performance Product Liam Noah James TOTAL 70,000 15,000 15,000 100,000 80,500 90,500 99,000 270,000 78,000 122,000 150,000 350,000 125,000 105,000 60,000 290,000Ivanka believes that these estimates will grow by the following proportions per annum: Product A 15% Compounded Product B 12% Compounded Product C 10% Compounded Product D 12% Compounded Lease of premises $1 000 Constant General expenses 12% Compounded Electricity & gas 11% Compounded on the fixed portion only Commission expenses 12% Compounded You only need to calculate the commission expense for Year 1 and for simplicity, can assume that the Year 1 commission expense you calculated will grow at 12% per annum. a) Required: You are required to prepare the projected prot and loss statement for the management meeting. 1. You are required to ll in the formulas in the badge colour highlighted cells in the Report Section (see Figure 1). Do not change the structure or format of the Report Section. You can only enter formulas in the badge colour highlighted cells as the rest of the cells are password protected. Construct the formulas in the report section to show the profit projections for the next six years. plus a total column. All the formulas must follow the two design principles; formula flexibility and formula efficiency. For example, marks will be deducted for overly too long and complicated fon'nulas. All formulas must be able to be copied across. . You are also required to fill in the Logic Section with the commission calculations for Year 1. Ensure that when constructing your model, you follow the \"formula exibility\" and \"formula efficiency\" principles discussed in your lecture. . You are required to design and write the data for the Data Section so that you can then generate the formulas necessary to complete the Logic and Report sections. There should be no formulas in the data section (refer to lecture slides as to the reasons). . You are required to write the Documentation Section (Click on the Doc tab see Figure 1). Refer to the lecture slides on what is needed for the documentation section. Note: you will be marked also to the clarity and tidiness of your design and layout in the Data and Documentation