Question: note please read and follow all the instructions and include...
NOTE: PLEASE READ AND FOLLOW ALL THE INSTRUCTIONS AND INCLUDE THE DIAGRAMS IN THE ANSWERS THAT ARE ASKED FOR. AND MOST IMPORTANLY USE EXCEL IF THE QUESTIONS ASKS TO USE EXCEL AND PLEASE ATTACH THE PHOTO OF THE EXCEL WORK AS WELL AS THE LINK FOR ACCESS.
1. Motion Music is doing a financial feasibility analysis for a new album. Recordings and production are estimated at $45,000. The printing of CDs is fixed at $3,000 for set up plus $2.40 per CD. The artist’s royalty is 4% per CD of the publisher’s price to music stores. Advertising and promotion costs are budgeted at $9,000.
(a) If the price to music stores is set at $21, how many CDs must be sold to break even? Find the break-even point algebraically and by using an EXCEL graph. Attach the printout or copy your EXCEL graph into your assignment submission.
EXCEL Instructions: Create a column called Number of CDs and in that column enter values from 0 to 5,000 in increments of 500. Then create two more columns, one for Total Cost and another for Total Revenue. Enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of CDs column. Highlight the resulting three sets of numbers and go to the Insert tab (or Chart menu) to obtain an appropriate diagram.
Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend). Refer to Topic 3 in the EXCEL Supplement for further instructions on entering formulae and graphing in EXCEL.
- (b) The marketing department is forecasting sales of 4,000 CDs at the $21 price. Based on your graph from part (a), will there be a net profit or net loss from the project at this volume of sales? How do you know? Calculate this net profit or loss amount. (Assume no tax costs.)
(c) If the artist requires an increase in royalty to 7%, how does this impact Motion Music?
Assuming the fixed costs and the selling price remains as in (a), explain in a short paragraph (3- 4 sentences) whether the number of CDs required to break-even will increase or decrease. Do not re-calculate the break-even quantity, x, for this question however you may quote the break- even formula to aid your explanation.
(d) The marketing department is also forecasting that if the price is reduced by 5% then unit sales will be 10% higher. Should Motion Music continue with the original price in (a) or this reduced price? Show calculations that support your recommendation. Assume initial fixed and variable cost estimates.