Posted: November 29th, 2022

Homework Week 12 – Spreadsheet Modeling

Homework Week 12 – Spreadsheet Modeling

Case Problem #1: Cox Electric (12 points)
Cox Electric makes electronic components and has estimated the following for a new design of one of its products:
• Fixed cost = $10,000
• Material cost per unit = $0.15
• Labor cost per unit = $0.10
• Revenue per unit = $0.65
These data are given in the first tab (Cox Electric). Note that fixed cost is incurred regardless of the amount produced. Per-unit material and labor costs together make up the variable cost per unit. Assuming Cox Electric sells all that it produces, profit is calculated by subtracting the fixed cost and total variable costs from total revenue. Perform the following.
1. Prepare a mathematical model for calculating profit, similar to the example covered earlier this week.
2. Implement from your model in Excel using the principles of good spreadsheet design, similar to the example covered earlier this week.
3. If Cox Electric makes 12,000 units of its new product, what is the resulting profit?
4. Create a separate tab with the same information in step 2 and 3 from your model and construct a one-way table with production volume as the column input and profit as the output. Breakeven occurs when profit goes from a negative to a positive value; that is breakeven is when total revenue = total cost yielding a profit of zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur?
5. Create a separate tab with the same information in step 2 and 3 from your model and use Goal Seek to find the exact breakeven point. Assign Set Cell: equal to the location of the profit, To value: = 0, and By changing cell: qual to the location of the production volume in your model.

Case Problem #2: University Center for Business Analytics (14 points)
The University Center for Business Analytics is an outreach center that collaborates with industry partners on applied research and continuing education in business analytics. One of the programs is a quarterly BI symposium that features three guest speakers on real-world use of analytics. Each corporate member of the center (currently 10 members) receives five free seats to each symposium. Non-members wishing to attend must pay $75 per person. Each attendee receives breakfast, lunch, and free parking. The following are costs for putting on this event:
• Rental costs for the auditorium = $150
• Registration processing = $8.50 per person
• Speaker costs = 3 at $800 = $2,400
• Continental breakfast = $4.00 per person
• Lunch = $7.00 per person
• Parking = $5.00 per person
These data are given in the second tab (University). Perform the following:
1. Build a spreadsheet model that calculates a profit or loss based on the number of non-member registrants.
2. Create a separate tab with the same information as step 1 from your model and use Goal Seek to find the number of non-member registrants that will make the event breakeven.
3. Create a separate tab with the same information as step 1 from your model and extend it to account for the following and answer the question – what is the profit if each corporate member registers their full allotment of tickets (10 * 5 = 50) and 127 non-members register:
a. 25% of members (no refund if do not attend but remember there is no registration fee for members) do not show and 10% of registered non-members do not attend (50% refund of price if do not attend)
b. The center pays the caterer for breakfast and lunch based on the number of registrants (not the number of attendees).
c. The center pays for parking only for those who attend.
4. Use a two-way table to show how profit changes as a function of number of registered non-members and the no-show percentage of non-members. Vary the number of non-member registrants from 80 to 160 in increments of 5 and the percentage of non-member non-shows from 10% to 30% in increments of 2%.
5. Create a separate tab with the same information in step 3 and 4 from your model. Create a table that shows the following three scenarios listed below and use Scenario Manager to generate a summary report that gives the profit for each of these three scenarios and note which one is the highest profit and which one is the lowest profit.
a. % of members who do not show (Base Case 25%, Worst Case 50%, Best Case 15%)
b. % of non-members who do not show (Base Case 10%, Worst Case 30%, Best Case 5%)
c. Number of non-member registrants (Base Case 130, Worst Case 100, Best Case 150)

Case Problem #3: Revenue Model with Quantity Discounts (8 points)

Richardson Ski Racing (RSR) sells equipment needed for downhill ski racing. One of RSR’s products is fencing used on downhill courses. The fence product comes in 150-foot rolls and sells for $215 per roll. However, RSR offers quantity discounts. The following table shows the price per roll depending on order size:

Quantity Ordered
From To Price per Roll
1 50 $215
51 100 $195
101 200 $175
201 and up $155
Perform the following using the third tab (RSR), which contains 172 orders that have arrived for the coming six weeks:
1. Use VLOOKUP function with the table above to determine the total revenue from these orders.
2. Calculate Revenue for each order based on Price per roll * Order Quantity and sum Revenue to get total Revenue.
3. Use the COUNTIF function to determine the number of orders in each price bin.

Case Problem #4: Auditing a Transportation Model (6 points)

Consider the transportation model in the file (Williamson), which is very similar to the Foster Generators model discussed earlier this week. Williamson produces a single product and has plants in Atlanta, Lexington, Chicago, and Salt Lake City and warehouses in Portland, St. Paul, Las Vegas, Tucson, and Cleveland. Each plant has a capacity, and each warehouse has a demand. Williamson would like to find a low-cost shipping plan. Mr. Williamson has reviewed the results and notices right away that the total cost in way out of line. Perform the following:

1. Use the Formula Auditing tool under Formulas tab in Excel to find any errors in this model. (Hint: The model contains two errors. Be sure to check every formula).
2. Correct the two errors with the correct formulas.

Check Price Discount

Study Notes & Homework Samples: MUSI 1114 Introduction to World Music »Previous Post