Decision-making case using spreadsheet: purchasing analysis

 

Decision-Making Case Using Spreadsheet: Purchasing Analysis

Kayo Computer assembles and sells personal computers. Each computer needs one custom-designed printed circuit board (PCB). Kayo has contracted to buy the PCBs from an outside PCB manufacturer, Apex Manufacturing. The long-term contract stipulates that Kayo pays $200 per board to Apex for up to 2,000 PCBs. If the annual order quantity exceeds 2,000 PCBs, then Apex is obligated to give a discount of $40 per board for the portion beyond 2,000, thus selling them at $160.

Kayo can also buy the same PCBs from another manufacturer, TCI Electronics, that offers a lower price of $120 per PCB but asks a onetime payment of $100,000 as a nonrefundable design and engineering fee. Kayo’s engineers have determined that Kayo may use PCBs from either of the two manufacturers, or from both in any mixture without any manufacturing cost or compatibility problems.

The PCB along with other components are assembled by Kayo into its personal computer. The variable assembly cost of the Kayo personal computer is $450 each with an annual fixed cost of $1,500,000. Kayo sells the assembled computer for $1,000 each. At the moment no one is sure how many Kayo computers the company can sell for the next year. You, as VP of Finance at Kayo Computer, know that this model of Kayo computer will be discontinued after next year and so any one-time fee that might be paid to TCI must be justified based on next year’s sales alone. You will evaluate certain economic and legal issue as part of your financial plan for the next year.

Use Excel to build spreadsheet analysis models that captures the profitability of the Kayo personal computer for next year. It is not allowed to use Solver to solve this problem. You should build your own analysis models in spreadsheet. You will analyze the following scenarios:

  1. Build a spreadsheet model that captures the profitability of the Kayo personal computer for next year. As a start, assume that 5,000 computers can be sold next year and only 1,000 of the PCBs are purchased from Apex (the balance being supplied by TCI). Name this worksheet as Scenario 1.
  2. If total sales were 5,000 units, how many PCBs would you buy from Apex and how many from TCI to maximize next year’s profits? Use another worksheet to create a data table that shows several different combinations of the number of PCBs from Apex and the number of PCBs from TCI and then identify the combination that will bring maximum profit. Name this worksheet as Scenario 2.
  3. In reviewing the Apex contract, you note that it is requires Kayo to purchase at least 20% of the PCBs used in the Kayo computers sold (and not less than 1,000 PCBs) from Apex. The contract also contains a liquidated damages clause in the event of Kayo’s default in the amount of $1,000,000. What would be the economic effect if unforeseen changes caused Kayo to default on the 20%/1000 minimum contracted purchase provision by substituting more TCI boards in the event that 5,000 Kayo computers can be sold next year? Use a new worksheet to exhibits the effect. Name this worksheet as Scenario 3.
  4. (Optional, 10 extra points if you complete this part correctly) A market analysis reveals that unit sales will depend on the price of the computer. At the price of $1,000, about 5,000 units will be sold, but for every increase (or decrease) of $100, sales will decrease (or increase, respectively) by 1,000 units. On a new worksheet, create a data table to maximize Kayo’s profit next year, by finding (a) the optimal price, and (b) the optimal number of boards to buy from Apex while still honoring the original contract. Name this worksheet as Scenario 4.







Calculate Your Essay Price
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 10% with the coupon code: best10