Employee ID	First name	Last name	Job classification	Jan Sales
1001	Mary	Stevens	Senior Sales Rep	$  85,000
1002	Larry	Anderson	Senior Sales Rep	$  72,000
1003	Sunil	Gupta	Junior Sales Rep	$  35,000
1004	Sarah	Ellis	Junior Sales Rep	$  61,500
1005	Timothy	Chau	Junior Sales Rep	$  48,000

Transcribed Image Text:

Charisa Williams is the sales manager of an online financial data service company that specializes in providing brokerage firms and independent financial analysts with up-to-date financial market data and analysis. She keeps track of the commissions of her salespeople to make sure that their earnings are competitive with those of other firms. The Commissions spreadsheet has the sales of her team in January. She expects the sales amount to increase by 5% each month for the senior sales representatives and 3% for the junior sales representatives. The base monthly salaries are $3,000 and $2,000 for senior and junior sales representatives, respectively. The sales commission rate is 8% and 6% for senior and junior sales representatives, respectively. If the monthly sales of a sales representative exceeds $100,000, he or she receives a 5% bonus on the commission of that month. The personal marginal income tax rates are 15% for up to $1,999, 22% for up to $4,999, 27% for up to $9,999, and 30% for anything beyond $9,999. a. Modify the partially-completed spreadsheet model to help Charisa Williams compute the monthly incomes (both before- and after- tax) of her sales team members for the entire year. What is the team's total sales for the entire year? Note: Round your answer to 2 decimal places. Total sales b. What is the total of before-tax annual compensations of the entire sales team? What is the total for after-tax annual compensations of the entire sales team? Note: Round your answers to 2 decimal places. Total before tax compensations Total after tax compensations c. The firm is forecasting an economic recession this year; therefore, instead of a monthly increase in sales, the firm is projecting a monthly decrease in sales by 3% and 5% for the senior and junior sales representatives, respectively. What will be the total sales, total before-tax compensations, and total after-tax compensations for the entire sales team? Note: Round your answers to 2 decimal places. Total sales Total before tax compensations Total after tax compensations Charisa Williams is the sales manager of an online financial data service company that specializes in providing brokerage firms and independent financial analysts with up-to-date financial market data and analysis. She keeps track of the commissions of her salespeople to make sure that their earnings are competitive with those of other firms. The Commissions spreadsheet has the sales of her team in January. She expects the sales amount to increase by 5% each month for the senior sales representatives and 3% for the junior sales representatives. The base monthly salaries are $3,000 and $2,000 for senior and junior sales representatives, respectively. The sales commission rate is 8% and 6% for senior and junior sales representatives, respectively. If the monthly sales of a sales representative exceeds $100,000, he or she receives a 5% bonus on the commission of that month. The personal marginal income tax rates are 15% for up to $1,999, 22% for up to $4,999, 27% for up to $9,999, and 30% for anything beyond $9,999. a. Modify the partially-completed spreadsheet model to help Charisa Williams compute the monthly incomes (both before- and after- tax) of her sales team members for the entire year. What is the team's total sales for the entire year? Note: Round your answer to 2 decimal places. Total sales b. What is the total of before-tax annual compensations of the entire sales team? What is the total for after-tax annual compensations of the entire sales team? Note: Round your answers to 2 decimal places. Total before tax compensations Total after tax compensations c. The firm is forecasting an economic recession this year; therefore, instead of a monthly increase in sales, the firm is projecting a monthly decrease in sales by 3% and 5% for the senior and junior sales representatives, respectively. What will be the total sales, total before-tax compensations, and total after-tax compensations for the entire sales team? Note: Round your answers to 2 decimal places. Total sales Total before tax compensations Total after tax compensations

Answer rating: 100% (QA)

SOLUTION a To compute the monthly incomes both before and aftertax of the sales team members for the entire year we need to first compute the monthly ... View the full answer