Description

The company you work for has three major events that may or may not happen coming up. The first of the three events is a big client contract that will affect several expenses. If your company can land this new client, the company’s payroll expenses will increase by 4% to accommodate new hires to help with the 3.5% production increase. All of this will account for a 3% increase in delivery costs and a 3% increase in your units sold. All of these extra expenses could account for less of a profit in the short-term, but you are hoping that it will give you more clients in the long-term with a higher profit.

The second of the three events that could happen is that your company qualifies for a tax rebate on the rent that it pays for the building. If it does not qualify for the rebate, then the rent will remain what it was. If your company does qualify for the rebate, then it will receive a 2.5% discount on the rent per month for the full year.

The final event that could happen is a gas discount that your company could qualify for. If it does qualify, then you will receive a 1.1% discount on your delivery costs.

Instructions: 

1. Correctly fill in cells C3, D3, E3, F3, G4, and H5 with conditional statements that include the percentages listed above if the corresponding input cell is “Y”. For example, if B3 is “Y”, then C3, D3, E3, and F3 should all show the correct percentage values if you receive the new client contract. 

2. Calculate cells C9, C10, C11, and C17 based on the conditional percentage values. 

3. Calculate the yearly totals in cells D9:D17 with a grand total in D18. 

4. Based on the conditions, calculate C22. 

5. Calculate the yearly total in D22. 

6. Calculate the total income for the year in D24. 

7. Insert a formula in H8 that will calculate profit. 

8. Create 8 scenarios that will show the profit for any situation that could occur. For example, you could have a client contract, a client contract and the tax discount, etc. 

9. Create a scenario summary that will output the profit based on all of the different scenarios. Be sure that your scenario summary is clearly labelled with changing cells and result cells. 

10. Create a column graph that is clearly labelled to show the profit for all scenarios. Your graph should have axis labels, graph and axis titles, and be easily read to identify which scenario has the highest profit.