Question
Can someone help me with these questions? I can't figure out the formulas to do calculations. Help me with calculations, i need to see also
Can someone help me with these questions? I can't figure out the formulas to do calculations. Help me with calculations, i need to see also formula so i know how to do it. Thanks
1.Each sales rep makes a 20 percent commission on all weekly sales above $1000. If the rep does not have more than $1000 in weekly sales, his/her Commission Wages equals zero. Write an IF function in cell E11 that determines Als (the first sales rep) Commission Wages.
After writing the IF Function for cell E11, use the most appropriate method to quickly compute the commissions for the remaining sales reps.
HINT: When the IF functions are entered correctly, Al (the first sales rep makes no commission; Bill (the second sales rep) makes $40.00.
2.Calculate the Gross Wages for each sales rep. Gross wages include the base wages plus any commission wages earned. Place your answers in the appropriate cells in Column F.
3.Locate the Assumptions area in the worksheet (starting in cell A29) to view the Federal Tax rate for all the sales reps. Place a formula in cell G11 that uses an absolute cell reference referring to the 28% Federal Tax Rate and calculates the amount of Als Federal Tax. Then use the best method to quickly compute theFederal Tax for the remaining sales reps.
4.Calculate the Net Pay for each sales rep. For this problem, net pay is the amount of gross wages remaining after the Federal tax has been subtracted. Place your answers in the appropriate cells in Column H.
5. In cell B35, use the appropriate function to total the weekly sales for the entire group of Stereo sales reps. Repeat this procedure to sum the weekly sales for the sales reps in the remaining three departments and place your answers in the corresponding cells.
In cell C35, use the appropriate function to find the average amount of sales among the sales reps in the Stereo department. Repeat this procedure to determine the average sales figures for the sales reps in the remaining three departments and place your answers in the corresponding cells.
6.Format all the numbers in the spreadsheet in the comma format (with two decimals). Then format only the numbers in row 11 in the Accounting format.
Locate the shaded Goal Seek Step 14 area in the worksheet (starting in cell A 33). Read through the data in rows 34 and 35 to see that Holly has a salary of $1,234.00, the current Fed Tax % is blank, and there is a formula ( =B35 * C35) in cell D35 to calculate the amount of Fed Tax Holly needs to pay when you set the Tax Rate % in cell C35.
Use the goal seeking process to find what the Fed Tax % would be if Holly had to pay $502.00 in Federal Tax.
Correct any spelling or grammar errors (like capitalization) in the worksheet. (All words in the two title lines are spelled correctly.)
Change the name of the worksheet tab to Supplemental Problem (2 points).
Working with the cells in TOTAL SALES BY DEPARTMENT, place the appropriate type of chart, on its own chart sheet that shows the labels and percentages of each departments total sales compared to the 100% total of all for departments sales. Format your table so that information is logical and appropriately proportioned. Name your Chart tab.
LAZOR LECTRONICS STORE SALES | |||||||
Payroll Information - Week 7 | |||||||
Department | Sales Rep | Weekly sales | Base Wages | Commission Wages | Gross Wages | Ferderal Tax | Net Pay |
Stereo | Al | 900 | 300 | ||||
Bill | 1200 | 300 | |||||
Sam | 1100 | 300 | |||||
Joe | 1400 | 300 | |||||
T.V.s | Holly | 900 | 300 | ||||
Sue | 2000 | 300 | |||||
George | 400 | 300 | |||||
Mary | 1000 | 300 | |||||
CamCorders | Josie | 3000 | 300 | ||||
Erika | 200 | 300 | |||||
Amy | 700 | 300 | |||||
Amanda | 900 | 300 | |||||
Compruters | Tim | 1500 | 300 | ||||
JT | 2500 | 300 | |||||
Bob | 975 | 300 | |||||
Dave | 990 | 300 | |||||
Assumptions | |||||||
Commission Rate = | 20% | of all weekly sales greater than $1000 | |||||
Fed Tax Rate= | 28% | ||||||
TOTAL AND AVERAGE SALES BY DEPARTMENT | |||||||
Department | Total | Average | |||||
Stereo | |||||||
T.V.s | |||||||
Camcorder | |||||||
Computers | |||||||
Goal Seek Step 14 | |||||||
Sales Rep | Salary | Fed Tax % | Fed Tax Amount | ||||
Holly | $1,234.00 | $ - |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started