Need Help with case Studies attached
Hardworker
template_for_case_studies.xlsCase 5-2
| Case 5-2 Template | ||||
| INSTRUCTIONS: Read the case in the textbook. As a team, answer the questions in this spreadsheet, then save and submit the assignment as one Microsoft® Excel® attachment. Also, submit a 1-paragraph Microsoft® Word document explaining any issues or successes you had in answering these questions. | ||||
| Refer to and use the following abbreviations for the problems below: • R = Royalty Payment • CT = Artist Credit Total • SV = Share Value • CV = Credit Value • RP = Retail Price • PD= Packaging Deduction • R%=Royalty Percentage of Suggested Retail Price minus packaging | ||||
| 1. Ziam wants to know how much his royalty will be for a song he has written. How will it be calculated? Write the steps or the formulas that will be used to calculate his royalty payment. | ||||
| (Write the answer in this cell.) | ||||
| 2. Ziam has written a popular song titled “Going There,” which has been recorded by a well-known performer. He recently received a royalty check for $7,000. If Ziam gets a 0.5 share of the royalties and the credit value is $3.50, what was the credit total that his song earned? Write out the problem in the form of an equation and solve it. | ||||
| (Use the equation below and show your work in this cell to solve for the credit total.) Manual Equation: R = (CT)(SV)(CV) | ||||
| Use the marked cells below to fill in the appropriate numbers for the variables (CV, SV, and R) to check your answer for the credit total. | ||||
| Variables Used | CV= | $1.00 | ||
| SV= | 1.00 | |||
| R= | $1.00 | |||
| Excel Formula | CT= | $1.00 | ||
| Use the cursor to highlight the yellow cell and look at the area above the column headings to see the background formula. You should see the formula, =E15/(E13*E14). By changing the CV, SV, or R values in the Excel reference formula you can experiment with various what-if scenarios that will change the CT value. Give it a try. | ||||
| 3. Ziam quickly published another song, “Take Me There,” that is played even more often than “Going There.” If his first song earns 4,000 credits and his second song earns 6,000 credits, what will the royalty payment be from the two songs if the credit value remains at $3.50? | ||||
| Use the equation below and show your work in this cell to solve for the royalty payment. Manual Equation: R = (CT)(SV)(CV) | ||||
| Use the marked cells below to fill in the appropriate numbers for the variables (CT, SV, CV) to check your answer for the royalty payment. | ||||
| Variables Used | CT= | $1.00 | ||
| SV= | 1.00 | |||
| CV= | $1.00 | |||
| Excel Formula | R= | $1.00 | ||
| Use the cursor to highlight the yellow cell and look at the area above the column headings to see the background formula. You should see the formula, =E24*E25*E26. By changing the CT, SV, or CV values in the Microsoft® Excel® reference formula, you can experiment with various what-if scenarios that will change the R total. Try it. | ||||
| 4. Ziam is considering an offer to perform his own songs on a CD to be titled “Waiting There.” In the past, he has written but not performed his music. If Ziam’s royalty is 0.12 of the suggested retail price of $15.00, but 0.25 of the retail price is deducted for packaging before Ziam’s royalty is calculated, how much will he receive for sale of the CD? Write your answer in the form of an equation and solve it. | ||||
| Use the equation below and show your work in this cell to solve for the royalty payment. Manual Equation: R = (PD - 0.25 * R%)(0.12) | ||||
| Use the marked cells below to fill in the appropriate numbers for the variables (CT, SV, CV) to check your answer for the royalty payment. | ||||
| Variables Used | RP= | $1.00 | ||
| PD= | 1.00 | |||
| R%= | 1.00 | |||
| Excel Formula | R= | $0.00 | ||
| Use the cursor to highlight the yellow cell and look at the area above the column headings to see the background formula. You should see the formula, =(E35-E36*(E35))*(E37). By changing the RP, PD, or R% values in the Microsoft® Excel® reference formula, you can experiment with various what-if scenarios that will change the R total. Try it. |
Case 6-2
| Case 6-2 Template | ||||||
| INSTRUCTIONS: Read the case in the textbook. As a team, answer the questions in this spreadsheet, then save and submit the assignment as one Microsoft® Excel® attachment. Also, submit a 1-paragraph Microsoft®Word document explaining any issues or successes you had in answering these questions. | ||||||
| Refer to and use the following abbreviations for the problems below: • PS = Public Subscribers • AD = Advertisers • TC = Telephone Customers • ISP = Internet Customers • TOT = Total Customers | ||||||
| 1. What percentage of the total does each of the four customer groups represent? Round to the nearest hundredth of a percent. | ||||||
| (Show your work in this cell to solve for the percentage of the total that each of the four customer groups represent.) | ||||||
| Use the cells below to fill in the appropriate numbers for the variables (PS, AD, TC, ISP) to check your answers. | ||||||
| Variables Used | PS= | 1 | ||||
| AD= | 1 | |||||
| TC= | 1 | |||||
| ISP= | 1 | |||||
| TOT= | 1 | |||||
| Excel Formulas | PS % of total | 100.00% | ||||
| AD % of total | 100.00% | |||||
| TC % of total | 100.00% | |||||
| ISP % of total | 100.00% | |||||
| Use the cursor to highlight the yellow cells and look at the area above the column headings to see the background formulas. What is great about a Microsoft®Excel® reference formula is you can change any of the non-total variables and the Total and Percentage answers will change; thus, allowing many what-if scenarios. Try it. | ||||||
| 2. Minh’s data shows that on average only 4.6% of customers were purchasing complementary services available within Media Systems. By using his company’s services, Minh was projecting that these percentages would triple across all user groups within 1 year. a) How many customers would that equate to in total for each group? b) What would be the difference compared to current levels? | ||||||
| (Show your work in this cell to solve for the total number of customers in each group and the difference compared to current levels.) | ||||||
| Use the cells below to fill in the appropriate numbers for the variables (PS, AD, TC, ISP, current purchasing %, projected multiple factor, and % difference) to check your answers. | ||||||
| Variables Used | PS= | 1 | ||||
| AD= | 1 | |||||
| TC= | 1 | |||||
| ISP= | 1 | |||||
| Current Purchasing % | 1.00% | |||||
| Projected multiple factor | 1.00 | |||||
| % Difference | 1.00% | |||||
| Excel Formulas | Part A | Part B | ||||
| PS= | $0.01 | $0.01 | ||||
| AD= | $0.01 | $0.01 | ||||
| TC= | $0.01 | $0.01 | ||||
| ISP= | $0.01 | $0.01 | ||||
| Use the cursor to highlight the yellow cells and look at the area above the column headings to see the background formulas. What is great about a Microsoft®Excel® reference formula is you can change any of the variables and the cells with formulas will change; thus, allowing many what-if scenarios. Try it. | ||||||
| 3. Customer complaint data showed that within the last year, complaints by category were as follows: publication subscribers, 1,174; advertisers, 423; telephone service customers, 4,411; and ISP customers 823. a) What percentage of customers (round to two decimal places)complained within the last year in each category? b) If the CRM software were able to reduce complaints by 50% each year over the next 2 years, how many complaints would there be by category at the end of that time period? And c) What would the number of complaints at the end of 2 years represent on a percentage basis? | ||||||
| (Use this cell to answer parts a, b, and c. Be sure to show your work.) | ||||||
| Now use the cells below to fill in the appropriate numbers for the variables to check your answers. | ||||||
| Variables Used | Complaints | Customers | ||||
| PS= | 1 | 1 | ||||
| AD= | 1 | 1 | ||||
| TC= | 1 | 1 | ||||
| ISP= | 1 | 1 | ||||
| TOT= | 4 | 4 | ||||
| Complaint reduction Factor | 1% | |||||
| Excel Formulas | Part A | Part B | Part C | |||
| PS= | 100.00% | $0.00 | 0.01% | |||
| AD= | 100.00% | $0.00 | 0.01% | |||
| TC= | 100.00% | $0.00 | 0.01% | |||
| ISP= | 100.00% | $0.00 | 0.01% | |||
| Use the cursor to highlight the yellow cells and look at the area above the column headings to see the background formulas. What is great about a Microsoft®Excel® reference formula is you can change any of the variables and the cells with formulas will change; thus, allowing many what-if scenarios. Try it. |
