NEED ASAP ITS DUE TOMORROW MORNING PLEASE HELP

Christian Bulwig is the business manager at Bug Kits Ltd, a toy company in austin?

robotic toys that mimic the appearance and actions of insects. Christian wants to create a

statement for the Basic Bug kit. He wants to find the optimal price for selling the kit- From

research, Christian projects that the company can sell 12,000 kits at $21 per kit- He also wants to know

if the company's profits would increase if the sales price increased. Obviously the higher the sales price

the higher the profit per kit, but the company might also sell fewer kits. The Optima' Price point occurs

when the company can sell the most units possible at the highest price without hurting sales. Christian is

also considering a loan for possible expansion and wants to calculate possible payments. There iS a

proposed salary increase at Bug Kits Ltd. Christian wants to know what the cost of the increase would

be as well as a count of employees by location.

Complete the following:

1. Open the Optimal workbook from the Portal and save it as Optimal Price Point.

2. In the Documentation worksheet enter your name and the today?s date in Long Date format.

3. in the Income Statement worksheet, in cell C10, enter 21 as the price per kit. Based on the market

research, Christian has entered the formula in cell C11 to calculate the effect of higher prices on

sales. (Based on this information, the company can sell 12,000 kits if the price per kit is $21).

4. in cell C12, calculate the projected revenue by multiplying the price per kit by the number of kits

produced.

5. The company plans to manufacture about 3 percent more kits than it sells. ln cell C15, calculate the

kits produced by multiplying the value in C11 by 1.03.

6. in cell C16, enter a material cost per kit of $7.20, and in cell C17, calculate the total material cost by

' multiplying the material cost per kit by the number of kits produced.

7. in cell C18, enter a manufacturing cost per kit of $4.80, and in cell C19, calculate the total

manufacturing costs for all of the kits produced.

8. ln cell C20, calculate the total variable expenses by adding the total material cost and the total

manufacturing cost for all of the kits produced by the company.

9. in the range C23:C25, enter the fixed expenses of $15,000 for advertising, $35,000 for

administration, and $15,000 for miscellaneous expenses. In cell C26, calculate the total fixed

expenses.

10. in cell C29, reference the total revenue from cell C12. In cell C30, calculate the sum of the variable

and fixed expenses. In cell C31, calculate the net income (revenue minus total expenses).

11. In the range, E5:l36 set up a one-variable data table that tracks the effect of changing the price of

kits sold, total revenue, total expenses, and net income. In cell E5, reference the price per kit from

cell C10. in cell F5, reference the kits sold from cell C11. In the range G5:I5, reference the total

revenue, total expenses, and net income from the range C29:C31. In the range E6:E36, enter price

values from $15 up to $30 increments of $0.50

12. Complete the one-Variable data table by calculating the remaining values in the range F6:I36.

13. Create a scatter chart with smooth lines, plotting net income versus price per unit using the values

in the ranges l6:l36 and E6:E36, respectively. Move and resize the chart to cover the range K4:P17,

and format it appropriately to make the chart easy to read and interpret. Be sure to change the chart title.

14. Use Solver to ?nd the sales price in cell C10 that maximizes the company?s net income. The

company has a contract with a building shop and must manufacture at least 8,000 kits; however,

Christian does not feel the company should manufacture more than 12,000 kits at this time. Add

these two constraints to your model.

15. Create an Answer report for the Solver solution, and then move the report worksheet to the end of

the workbook.

16. Save the Solver model to the range K20:K25 on the Income Statement worksheet. In cell K19, enter

Solver Model and format the label with the Accent 3 cell style.

17. Go to the Compensation worksheet. In cell G2, calculate the Years of Service by using the

appropriate function from the Date & Time Category of the function dialog box. Hint: You may need

to divide by the number of days in the year to convert to years of service. Complete the rest of

column G and format as a number with 1 decimal place

18. In cell H2, calculate the salary increase for employees based on their location. If the employee is in

NY or SD, they are to receive a 2% increase. If not, they will receive 1.5%. Complete the rest of

column H and format in Accounting format with no decimals.

19. In cell H35, calculate the total of all salary increases.

20. In cells B39:B44, count the number of employees for each location.

21. Check for duplicate Employee IDs in column A. Fill any duplicate cells with a yellow fill with dark

yellow text to indicate any duplicates. Change the first duplicate to 1170.

22. Go to the Loan worksheet. Enter 5% in cell C2. In cell G5 and G6 calculate the total number of

payments. In cell H5 and H6, set the interest rate to equal cell C2.

23. In cell I5 and I6, calculate the payment mount for each option.

24. Change cell C2 to 7% Enter the updated payment amount from cell J5 in Cell G10 and from cell J6 to G11. Change cell C2 back to 5%

25. Save File.

CyberBugs Author Date Purpose xxxxx 6/22/2015 To calculate the optimal price point for the Basic CyberBugs kit given the elasticity of price and demand