Write My Paper!

We provide assignment writing assistance through a team of specialists with Masters and Ph.D. degrees.

Order from us today and get top-quality plagiarism-free papers!!

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.

Special offer! Get 15 - 30% discount on all orders - Check Coupons Section

Essay Help for Your Convenience

Any Deadline - Any Subject
We cover any subject you have.
Set the deadline and keep calm.
Receive your papers on time.
Detailed Writer Profiles
That’s our Place of Truth.
Look over the writers’ ratings, success rating, and the feedback left by other students.
Email and SMS Notifications
Stay informed 24⁄7 about every update of the whole ordering process.
Plagiarism Free Papers
We double-check all the assignments for plagiarism and send you only original essays.
Chat With Your Writer
Communicate directly with your writer anytime regarding assignment details, edit requests, etc.
Affordable Prices
We offer the lowest prices per page in the industry, with an average of $7 per page.
App Essay Writers Features
Get All The Features For Free
per page
FREE  Plagiarism report
FREE  The best writer
FREE  Formatting
FREE  Title page
FREE  Outline
Place An Order

We Offer the Custom Writing Service with 3 Key Benefits

Quality Essay Help

Our team of professional writers guarantees top-quality custom essay writing results.

The Best Support Service

Get 24⁄7 help with proofreading and editing your draft – fixing the grammar, spelling, or formatting of your custom writing. 

Affordable Writing Service

We guarantee a perfect price-quality balance to all students. The more pages you order, the less you pay.

Benefit From App Essay Writers Extras

Along with our writing, editing, and proofreading skills, we want to make sure you get real bang for your buck, which is why we provide all these extra features.

Quick Turnaround
Looming deadline? Get your paper done in 6 hours or less. Message via chat and we'll get onto it.
Free Turnitin Report
Originality is our key priority. We provide a FREE Turnitin report with every essay, so you'll know it's definitively plagiarism-free!
QA Department
An extra set of eyes never hurts! Your essay is examined by our QA experts before delivery.
Flexible Discount System
The further the deadline or the more pages you order, the lower the price! Affordability is in our DNA.
Unlimited Revisions
All revisions are free of charge. Ask your writer for adjustments and you'll have them in no time!
24/7 Support
We're here for you day and night. Connect with customer support around-the-clock for any orders or urgent questions.

Custom Essay Writing Service

Get your custom writings in the best quality: