Section A1: All staff members receive an annual bonus of $200 plus an additional percentage of...

Section A1: All staff members receive an annual bonus of $200 plus an additional percentage of their Annual Income. Each staff member has been allocated their own percentage bonus rate (column C). In D6:D16 calculate each staff members Bonus

Section A2: Using the % Superannuation given to all staff. Calculate the annual Super amount paid to each staff member using the value in 9.5%. Copy the formula down to the last cell (to get full marks, a named range or an absolute cell reference must be used).

Section A3: In 'total package' calculate the total package for each staff member (Annual Salary, Bonus and Super). Adjust the spreadsheet so that the “#####” problem is addressed.

Section A4: Find total labour cost

Section A5: Inserest a row under the total labour cost and find the average total package

Section A6: use a formula to calculate the highest Total Package paid to an individual staff member (i.e. the biggest total package)

PLEASE PROVIDE AN EXPLAINATIONS during each step

Table is provided below.

Employee Number	Annual	Bonus Rate	Bonus Amount	Annual Super	Total Package
10026	$ 49,283.00	1%
10027	$ 33,968.00	2%
10030	$ 32,158.00	2%
10032	$ 45,435.00	0%
10033	$ 51,722.00	0%
10034	$ 42,040.00	2%
10035	$ 44,161.00	1%
10036	$ 41,368.00	3%
10037	$ 57,029.00	2%
10038	$ 33,193.00	1%
10039	$ 37,410.00	0%

				Total
		Super%	9.50%	Highest

Solutions

Expert Solution

Column A	Column B	Column C	Column D	Column E	Column F
Employee Number	Annual	Bonus Rate	Bonus Amount ($200 + (Annual*Bonus Rate))	Annual Super (Annual + Bonus Amount)*9.50%	Total Package (Annual + Bonus Amount + Annual Super)
10026	$49,283	1%	$692.83	$4,747.70	$54,723.53
10027	$33,968	2%	$879.36	$3,310.50	$38,157.86
10030	$32,158	2%	$843.16	$3,135.11	$36,136.27
10032	$45,435	0%	$200.00	$4,335.33	$49,970.33
10033	$51,722	0%	$200.00	$4,932.59	$56,854.59
10034	$42,040	2%	$1,040.80	$4,092.68	$47,173.48
10035	$44,161	1%	$641.61	$4,256.25	$49,058.86
10036	$41,368	3%	$1,441.04	$4,066.86	$46,875.90
10037	$57,029	2%	$1,340.58	$5,545.11	$63,914.69
10038	$33,193	1%	$531.93	$3,203.87	$36,928.80
10039	$37,410	0%	$200.00	$3,572.95	$41,182.95
				Total	$520,977.25

				Average	AVERAGE(F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13)	$47,361.57
				Highest Package	MAX(F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13)	$63,914.69

Annual Super calculation is calculated on ordinary time earnings which includes normal package plus additional performance bonus paid to employees

ekkarill92 answered 8 months ago

Next > < Previous

Related Solutions

You recently got hired to work for Hank. You are set to receive an annual bonus...

You recently got hired to work for Hank. You are set to receive an annual bonus of $12,900 per year for the next 16 years. Assume the interest rate is 7.8 percent. How much more are the payments worth if they are received at the beginning of the year rather than the end of the year?

A company pays its five salespeople on a commission basis. The salespeople receive $200 plus 10%...

A company pays its five salespeople on a commission basis. The salespeople receive $200 plus 10% of their sale. For example, for the employee sale of $1000, the commission is $300. Write a C program that: inputs each employee sales. The program then calculates the employee commission, prints out each employee commission. The program calculates the total sum of all commissions paid, and print. Your program will use a sentinel value to exit. You must use a one-dimensional array for...

Mary and her staff are all members serving on an ethics committee at an academic teaching...

Mary and her staff are all members serving on an ethics committee at an academic teaching hospital with no religious affiliations or sponsors. Mary's hospital permits the performing of abortions by physicians in the first trimester or in later trimesters if the health of the mother is at stake. The costs associated with abortions are paid either by the patient out-of-pocket or by the patient's private insurance. (It is federal law that there are no federally funded abortions, for example,...

True or False: The annual percentage rate is the simple percentage cost of all finance charges...

True or False: The annual percentage rate is the simple percentage cost of all finance charges over the life of the loan on an annual basis. With a discount method single-payment loan, the entire interest charge is subtracted from the principal before you receive the money, and at maturity you repay the principal. The APR is lower when money is lent under the discount method than when it is lent under the simple interest method. Amortization refers to the process...

Use the procedure outlined in Section 11.6.2 on p.262 of textbook and the annual percentage default...

Use the procedure outlined in Section 11.6.2 on p.262 of textbook and the annual percentage default rate for all rated companies in Table 11.6 on p.259, a. Estimate the probability of default (PD) and default correlation (ρ) for the period 1970-1993, and for the period 1994-2016 separately. b. Plot the probability distribution of default rate (similar to Figure 11.6 on p.263) for the time period 1970-1993 and 1994-2016 together on the same graph. 970 2.631 1971 0.286 1972 0.453 1973...

You just won the TVM Lottery. You will receive $80 million today plus another 20 annual...

You just won the TVM Lottery. You will receive $80 million today plus another 20 annual payments that increase by $4,000,000 per year. starting from this year. If the appropriate interest rate is 6.5 percent, what is the value of your winnings today?

You just won the TVM Lottery. You will receive $1 million today plus another 10 annual...

You just won the TVM Lottery. You will receive $1 million today plus another 10 annual payments that increase by $375,000 per year. Thus, in one year, you receive $1.375 million. In two years, you get $1.75 million, and so on. If the appropriate interest rate is 6.5 percent, what is the value of your winnings today?

November 1st, 2019: All 200 staff at Tony Inc. buy Apple Care for which they pay...

November 1st, 2019: All 200 staff at Tony Inc. buy Apple Care for which they pay $100 each. Apple Inc. expects that 30% of staff will break their iPhones before December 31st, 2019 and have to go to Apple to get a replacement phone. The replacement phones cost Apple $200. Apple Care ends on December 31st, 2019 and it turns out that 20% of staff break their phones on December 31st , 2019 and Apple immediately replaces them. A）Record all...

Assume that Sweeten Company used cost-plus pricing (and a markup percentage of 80% of total manufacturing cost) to establish selling prices for all of its jobs.

Sweeten Company had no jobs in progress at the beginning of March and no beginning inventories. The company has two manufacturing departments—Molding and Fabrication. It started, completed, and sold only two jobs during March—Job P and Job Q. The following additional information is available for the company as a whole and for Jobs P and Q (all data and questions relate to the month of March): Molding Fabrication Total Estimated total machine-hours used 2,500 1,500...

Suppose that every additional four percentage points in the investment rate (I ÷ GDP) boost economic growth by one percentage point. Assume also that all investment must be financed with consumer saving. The economy is now assumed to be fully employed at

Suppose that every additional four percentage points in the investment rate (I ÷ GDP) boost economic growth by one percentage point. Assume also that all investment must be financed with consumer saving. The economy is now assumed to be fully employed atIf the goal is to raise the economic growth rate by 1 percent, Instructions: Enter your responses as a whole number. a. By how much must investment increase? $ billion b. By how much must consumption decline for this to occur? $ billion

Subjects