Dear freelancer, please answer the below case questions. So what is needed is to answer 1-3 prompts inside the attached XLS answer sheet. And using this data to answer prompts 4-6 inside a 1000 words essay. Like for final analysis, conclusion and recommendations. Please check the “Assignment #2 Brief – Obama Publishing Case [Section 2]” it has all the required information. Other information that can be used is inside the Case file directly, “Obama Publishing Case (A)”. For the xls part i have also attached a mp4 file with a few explanations. Will add an additional 10 hours once the order is picked up.

You have been asked by Molly Stern from Crown Publishing Group (CPG) to analyze the potential economics of publishing the book Becoming by Michelle Obama and recommend a bidding number that would allow CPG at least to break even on the basis of hard copy sales.

CPG is in a frenzied bidding war for the book Becoming ! likely blockbuster book currently under development by Michelle Obama. Bidding has already exceeded $15 million. In comparison, Bill Clintonàadvance for his 2004 book My Life was $15 million, or about $21 million when adjusted for inflation. His wife Hillary Clinton received an advance of $11.5 million for Hard Choices in 2014.

To prepare for the auction, CPG management has hired your firm to provide an independent, objective perspective on the bookàeconomic value (to CPG). CPG previously published President Obamaàmultimillion-copy bestsellers Dreams from My Father and The Audacity of Hope, as well as Mrs. Obamaà2012 illustrated book about the White House kitchen garden, American Grown, which was a #1 New York Times bestseller.

While all these books have been very profitable for CPG and their relationship with the Obama family is very important for them, they do not want to overpay. Crown Publishing Group management is financially conservative and is a proponent of Discovery Driven Planning (DDP). The company uses Reverse Income Statements (RIS) to frame their thinking and use Assumption Checklists to keep track of pivotal and uncertain variables.

In this case, their specific requirement is that, at a minimum, Michelle Obamaàhardcover edition should break even.They¥ said that any upside financial returns from derivative products (e.g., paperback, e-books or audio books) and follow-ons (e.g. movie rights, merchandise) should not be considered.

References:

CSA Toolkit: Discovery Driven Planning

instructions

• Draw and annotate an ROI Tree for an average CPG front list title.
• Translate your ROI Tree into an Excel financial model of the P&L for Becoming
• Use your Excel financial model to estimate results across a range of acquisition bid values
• Evaluate the assumptions underlying your financial model:
  • Which assumptions are most pivotal to your answers?
  • What is your confidence level for each assumption?
  • Which assumptions should get priority for deeper analysis?
• Develop a list of additional data that you like to have and specific questions that you like to ask Molly?
• With the information given, what is your recommendation for Molly? How much should CPG bid for Michelle Obamaàbook? (Or should they walk away from this auction?)

Note: It won literally be an ROI Tree since it will decompose P&L, not ROI. GCP accountants treat AuthoràAdvances as expense items on CPGàP&L, not investments (assets) on the companyàbalance sheet.

As a starting point, reference case Appendix 3 — a P&L representing an average front list title.

The key to the exercise is to specify relationships among the variables (e.g. what variable links gross and net copies?), and to identify which costs are fixed, variable (i.e. linearly related to volume) and non-linear (e.g.hybrid costs, step functions).

References:

(1) CSA Toolkit: ROI Trees

(2) CSA Toolkit reakeven Analysis

Again, use the P&L format and data presented as Appendix 3 as a starting point for your model.

Start by replicating the results that Molly mentioned in the orientation interview (Appendix 1): (e ÷eet spot&or Becoming is a $21.99 cover price … With a $300,000 advance, we could have broken even selling about 100,000 (net) copies./a>

Plug in the $21.99 Cover Price and $300,000 AuthoràAdvance and use Excelàïlver&unctionality to validate the number of Gross Copies required to breakeven (at the Net Profit line of the P&L).

Note: The royalties paid to authors are a bit tricky (i.e. non-linear) since authors get paid a standard percentage royalty after they have arned4heir advance. See case Appendix 2 for details.

Reference:

CSA Toolkit ost Modeling (Fixed & Hybrid Costs)

Apply the Reverse Income Statement (RIS) analytical technique from Discovery Driven Planning.Specifically, assume that !t a minimum PG must break even on the hardcover edition of Becoming.

Using ExcelàSolver function, determine the ï-get.umber of copies that must be printed (gross copies) and sold (net copies) across a range of specific acquisition costs: $15 million, $20 million, $25 million and $30million. Identify the implications of go-get-number and assess the likelihood of achieving each go-get number.

References:CSA Toolkit ssumptions Analysis (Checklist);

SBA Toolkit nalytical Priorities Matrix

What vital few clarifying questions would you most like to ask Molly?What vital few pieces of information (data) would you most like to have?What additional analyses would you intend to do?

Be sensitive to the projectàtight deadline. Assume that that you, only have one chance to ask Molly questions (and won have an opportunity to interview anyone else).

Put yourself in Mollyàshoes when making this decision. Crown Publishing Group is about to go through an organizational restructuring, and she will be reporting to someone else in less than a year. Hopefully, her new boss will be supportive of the decisions she has made but she cannot help thinking that she might be on her own when defending this decision if something goes wrong. Additionally, the book publishing industry has become very competitive and as a result net revenue of this industry has stagnated; thus, she does not want to be over optimistic.

Deliverables

Use the Answer Sheet entitled ichelle Obama Book Answer Sheet/u> posted on Canvas.

1. Michelle Obama Book Answer Sheet:

• Annotated Spreadsheet

• A neatly formatted, reader-friendly, appropriately annotated spreadsheet showing your complete breakeven P&L for the scenarios of bidding: $15 million, $20 million, $25 million and $30million.
• On your spreadsheet, include a column that lists for each P&L line the source of the data, e.g. 2ovided by CPG management assumed based on (what?), calculated (how?).

1. An ROI Tree supporting your analysis for a $20 million bid.

Note: You may have to adjust the ROI Tree that you developed in step #1 to reflect the $20 million bid.

Hint: This is an important analytical step. Don just mechanically plug in your numbers.

Critically ask yourself if the numbers and relationships make sense.

• Assumption Analysis Checklist

Your Assumptions Analysis Checklist from Step 4, listing the pivotal assumptions underlying your analysis and your level of confidence that your hypotheses are correct. Use the Assumptions Analysis tab in the Answer Sheet.

Note: These should be the ©tal few!ssumptions (max. 5 assumptions) on which the ultimate answer is most likely to substantially vary. The checklist should not be a laundry list of all model parameters.

Analysis Pack – Delivery Instructions

Submit the excel Answer Sheet in Canvas containing all the deliverables:

• Obama Book Answer Sheet
• Annotated Spreadsheet for the $15 million, $20 million, $25 million and $30million scenarios.
• ROI Tree (for $20 million scenario)
• Assumptions Analysis (Checklist)