Scribd Logo
Upload

Welcome to Scribd!

  • 77 views

    Problem Set 2 - Analyzing Using Spreadsheets

    Uploaded by

    Ayush Sharma
    1. This document provides 7 problems involving the analysis of business scenarios using spreadsheets. The problems involve concepts like data tables, scenario manager, goal seek, and varying inputs to understand their impact on key outputs like sales, costs, and profits. 2. For each problem, the document provides background context, and then lists a series of sub-questions that involve setting up spreadsheet models and data tables to evaluate how changes to inputs might affect outputs. 3. The overall goal is to help various business managers understand the sensitivity of profits and other metrics to potential changes in factors like prices, costs, production levels, demand, and more.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Problem Set 2 - Analyzing Using Spreadsheets

    Uploaded by

    Ayush Sharma
    77 views4 pages
    1. This document provides 7 problems involving the analysis of business scenarios using spreadsheets. The problems involve concepts like data tables, scenario manager, goal seek, and varying inputs to understand their impact on key outputs like sales, costs, and profits. 2. For each problem, the document provides background context, and then lists a series of sub-questions that involve setting up spreadsheet models and data tables to evaluate how changes to inputs might affect outputs. 3. The overall goal is to help various business managers understand the sensitivity of profits and other metrics to potential changes in factors like prices, costs, production levels, demand, and more.

    Original Title

    Problem Set 2_Analyzing Using Spreadsheets

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    1. This document provides 7 problems involving the analysis of business scenarios using spreadsheets. The problems involve concepts like data tables, scenario manager, goal seek, and varying inputs to understand their impact on key outputs like sales, costs, and profits. 2. For each problem, the document provides background context, and then lists a series of sub-questions that involve setting up spreadsheet models and data tables to evaluate how changes to inputs might affect outputs. 3. The overall goal is to help various business managers understand the sensitivity of profits and other metrics to potential changes in factors like prices, costs, production levels, demand, and more.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    77 views4 pages

    Problem Set 2 - Analyzing Using Spreadsheets

    Uploaded by

    Ayush Sharma
    1. This document provides 7 problems involving the analysis of business scenarios using spreadsheets. The problems involve concepts like data tables, scenario manager, goal seek, and varying inputs to understand their impact on key outputs like sales, costs, and profits. 2. For each problem, the document provides background context, and then lists a series of sub-questions that involve setting up spreadsheet models and data tables to evaluate how changes to inputs might affect outputs. 3. The overall goal is to help various business managers understand the sensitivity of profits and other metrics to potential changes in factors like prices, costs, production levels, demand, and more.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 4

    Problem Set 2: Analysis using Spreadsheets

    (Data Tables, Scenario Manager and Goal Seek)


    1. Advertising Budget Decision
    Refer to Advertising Budget Decision problem in Problem Set 1.
    a. What happens to profit if unit costs rise to $26 from $25? What is the percentage
    change in profit in this case?
    b. What happens if we spend an additional $1000 in Q1 for advertisement? What is
    the percentage change in profit?
    c. What happens to profit in the above two cases if sales is calculated using the
    following relationship:
    𝑆𝑎𝑙𝑒𝑠 = 3000 + 0.1 × 𝐴𝑑𝑣𝑒𝑟𝑡𝑖𝑠𝑖𝑛𝑔 × 𝑠𝑒𝑎𝑠𝑜𝑛𝑎𝑙 𝑓𝑎𝑐𝑡𝑜𝑟
    d. What happens to profit if we double the price, i.e., $80?
    e. What happens to the value of profit if we vary the unit cost from $20 to $30 in
    steps of $1? Create a graph to depict the relationship between unit cost and profit.
    f. How is profit affected by both Q1 and Q2 advertising? Show the results by varying
    Q1 and Q2 advertising from $5000 to $15000 in steps of $1000.
    g. What happens to profit in the following scenarios:
    Scenario Unit Price Unit Cost Overhead
    Optimistic $50 $20 12%
    Pessimistic $35 $30 18%
    Base $40 $25 15%

    h. Determine the breakeven unit cost.


    2. SwimSkin Inc.
    Refer to SwimSkin Inc. problem in Problem Set 1.
    a. Florian Fortescue, VP of Marketing at SwimSkin Inc., is apprehensive about the
    sales group’s estimate of the number of units sold. What if the figure is incorrect?
    Florian wants to understand what the revenue, contribution margin and profit before
    taxes would be if the number of units sold at $129.95 varied from 500 to 5000, in
    increments of 500 units. Build an appropriate data table to help him. Contribution
    margin is calculated by subtracting variable costs from revenue and represents the
    amount of revenue that contributes to covering the fixed expenses of the company.
    b. What happens to the data table in (a), when manufacturing cost per unit changes
    from $75.75 to $95 and marketing cost is reduced from $75,000 to $60,000?
    c. Florian also wants to see how profit is affected by different selling prices and units
    sold. The company has a policy that all prices must end in 9.95. So, Florian wants to
    use prices that range from $119.95 to $159.95 with an increment of $10. As before,
    units sold can be varied from 500 to 5000, in increments of 500. Build an appropriate
    data table for him. What happens to the profits in this data table if the variable
    manufacturing cost is reduced to $70 per unit?
    d. Create a data table showing the effect of change of marketing costs from $25,000
    to $125,000 in increments of $25,000 on profit before taxes.
    e. How does the profit vary as marketing costs vary from $25,000 to $125,000 in
    increments of $25,000 and prices vary from $99.95 to $149.95 in increments of $5?
    f. Show the effect on profit for the following three scenarios:
    Scenario Unit Price Units Sold
    Worst case $99.95 2500
    Likely case $129.95 3000
    Best case $149.95 4000

    3. Honeydukes Co.
    Refer to Honeydukes Co. problem in Problem Set 1.
    a. What is the breakeven value of the open-market grape price?
    b. Using a data table, show how profit varies as a function of the price set for raisins.
    Cover a range from $1.80 to $2.80 in steps of $0.10.
    c. Using a data table, show how profit varies as a function of the price set for raisins
    and the amount of grapes brought under contract. Cover a range from $1.80 to $2.80
    (in steps of $0.10) for the price and a range from 0.5 million pounds to 1.5 million
    pounds of grapes bought under contract (in steps of 0.1 million).
    d. What happens to profit in the following scenarios?
    Scenario Open-market Grape Price/lb
    Low $0.25
    Medium $0.30
    High $0.35

    4. F1 Night City Race


    The Formula 1 car race is coming to town. This is the first time the race is held in a
    city street at night. This will help to boost tourism for a short while and increase the
    city's global visibility. To help defray the massive costs in hosting the race, during
    the race, the city's tourism authority is going to levy a charge on hotels and this
    charge is 30% of the revenue. The overall estimation is that hotels can charge up to
    three times their usual rate for each room and still see full occupancy. Hotel general
    manager Tom Hawkes wants to understand the situation better before he makes the
    decision on pricing. He is skeptical that room occupancy will be full at such high
    room rates. The fixed cost and the unit variable costs are respectively $100 and $10.
    Assume that there are 100 rooms available with Hawkes.
    a. Build the base case spreadsheet models for two cases: i) Occupancy is 80% and
    ii) 𝑂𝑐𝑐𝑢𝑝𝑎𝑛𝑐𝑦 = 1.7 – 0.00175 × 𝑅𝑜𝑜𝑚 𝑟𝑎𝑡𝑒.
    b. At what room rate, will Tom Hawkes breakeven when occupancy is 80%?
    c. How does the profit vary when the government levy varies from 20% to 50%?
    Assume the occupancy rate to be 80%.

    5. Mini Cauldron Cakes


    You are trying to determine the price and the advertising for Mini Cauldron Cakes.
    The annual demand for the cakes equals 500 − 3𝑝 + 10𝑎0.5, where 𝑝 is the price of
    the cakes (per pack) in dollars and 𝑎 is hundreds of dollars spent on advertising the
    cakes. The annual fixed cost of selling the cakes is $8,000, and the unit variable cost
    of producing the cakes (per pack) is $15.
    a. Create the base case spreadsheet model.
    b. How does the profit vary for a variation in price between $10 and $100 per pack
    (in steps of $10) and amount of advertising between $100 and $2000 (in steps of
    $100)?

    6. Acme Company
    Acme Company is organizing a workshop for aspiring online content creators. The
    fixed cost for organizing such workshops in $15,000. It must pay the 10 speakers
    $700 each and the hotel $300 for food/lodging costs for all participants. Acme now
    needs to decide how much to charge each workshop participant (who is not a
    speaker). Last year, Acme charged $1200 for each such participant and the number
    of such participants who registered for the workshop was 42.
    a) Create the base case spreadsheet model to help Acme decide how much to charge
    the participants. You can use the last year’s charges and no. of registered participants
    to create the base case model.
    b) How does Acme’s profit vary if the charge for the participants varies from $600
    to $1500? Use the appropriate data table.
    c) How many paying participants must register for Acme to breakeven?
    7. Bob’s Woodworking Shop
    Bob owns a woodworking shop where he builds picnic tables and Muskoka chairs.
    He charges $250 for each picnic table and $80 for each Muskoka chair. It costs him
    $105 in materials and $50 in labour to build a picnic table. It costs him $30 in
    materials and $30 in labour to build a Muskoka chair. Demand for picnic tables is
    relatively constant at 30 tables per summer season. Demand for Muskoka chair
    increases with his investment in advertising. Based on his past experience, he
    believes that the relationship between advertising and demand for Muskoka chairs
    is as follows:
    125
    𝐷𝑒𝑚𝑎𝑛𝑑 =
    1 + 5𝑒 −0.005×𝐴𝑑𝑣𝑒𝑟𝑡𝑖𝑠𝑖𝑛𝑔 𝑆𝑝𝑒𝑛𝑑
    Bob wants to decide how much he should invest in advertising.
    a. Build the base case spreadsheet model. You can use the base case advertising
    spend to be $100.
    b. Build a one-way data table for Bob to evaluate the profit as well as the number of
    Muskoka chairs sold for various investments in advertising ($0 to $2,500, in steps
    of $250).
    c. What are the effects of the following three scenarios on the profit for Muskoka
    chairs? All costs are unit costs.

    Scenario Labour Cost Material Cost


    High labour cost/high material cost $80 $140
    Medium labour cost/medium material cost $60 $110
    Low labour cost/low material cost $40 $90

    You might also like