Order custom papers, masters thesis and dissertation in 3 guided steps; human written!
Posted: November 29th, 2022
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.
Looking for reliable dissertation help or thesis writing? Our expert team of MPhil and PhD-qualified professionals provides top-notch online assistance for dissertations, research papers, and essays—free from errors, plagiarism, and hidden fees. We select each specialist based on their academic expertise, ensuring high-quality thesis writing and research paper support. Beyond writing, we offer personalized guidance, one-on-one doubt-clearing sessions, and free study resources. Whether you need instant dissertation help or long-term research paper support, our experts are ready to help you succeed.
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.
Our custom dissertation help and thesis writing services meet your unique requirements. Unlike companies using pre-written content, we craft every dissertation, research paper, or essay from scratch based on your specific instructions and grading rubric. A subject-specialized writer will deliver a well-researched dissertation or thesis tailored just for you. Beyond essays, our academics and scholars offer PowerPoint presentations, cover letters, editing, tutoring, and business reports—comprehensive dissertation help and research paper support for all your academic needs.
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:
Completing dissertations or research papers can be overwhelming, but expert dissertation help and thesis writing are smart ways to improve your grades. Our platform connects you with top-rated tutors in education, law, and nursing, offering high-quality research paper support and sample resources. Working with experienced professionals enhances your dissertation or thesis writing, boosting confidence in your coursework—at affordable fees. Sign up today for instant dissertation help, research paper guidance, and stress-free learning!
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.
You Want Quality and That’s What We Deliver
We’ve put together our writing team with care, choosing talented writers who shine in their fields. Each one goes through a tough selection process, where we look for folks with deep expertise in specific subjects and a solid history of academic writing. They bring their own mix of know-how and flair to the table, making sure our content hits the mark—packed with info, easy to read, and perfect for college students like you.
We don’t do AI-written essays or copycat work—everything’s original. Competitive pricing is a big deal for us; we keep costs fair while delivering top-notch quality. Our writers are some of the best out there, and we charge rates that stack up well against other services. This means you get stellar content without draining your wallet. Our pricing is straightforward and honest, built to give you real value for your money. That’s why students turn to us for high-quality writing services that won’t break the bank.
Academic integrity is at the heart of what we do. Every paper starts from scratch, with original research and writing tailored just for you. We write 100% authentic—no plagiarism research essays. Our strict quality control process includes scanning every draft with top tools like SafeAssign and Turnitin, so you get a similarity score and proof of originality. We’re obsessive about proper citation and referencing too, crediting every source to keep things legit. It’s all about giving you peace of mind with content that meets the highest standards.
When you decide to place an order with Dissertation Writer, here is what happens: