Excel代写 | INFOMGMT 192 Assignment #2

INFOMGMT 192 Assignment #2

Problem 1. Maintenance Checkup
RentFun is a business that uses booths to rent digital discs. The quality of the discs worsens
to the extent that RentFun considers them to be defective.
RentFun wants to maintain a stock of discs that are at least 90 percent acceptable. RentFun has
hired you to create a report that assesses each disc based on:
• Total number of times each title has been rented
• Number of times each title has reported defects
• The percent acceptable must be above 90% based on usage. If the percentage acceptable is
below 90%, RentFun wants to label the disc as “DISCARD.”
RentFun has given you with the data in a file called RentFun.xlsx. The figure below displays a
sample of the output that RentFun needs.
Problem 2. Loan Estimation
Katia wants to buy a more recent car to replace her current one. Katia’s bank offers her an annual
interest rate of 5.00% for a two-year loan. By selling her old car and using her savings, Katia has
$5,000 available as a down payment. Under her current budget, Katia figures out that the maximum
monthly loan payment she can pay is $500. She wants to find out the maximum car price she can
buy and keep the monthly payment no higher than $500. She cannot change the interest rate or
the two-year term.
Using Goal Seek, determine the highest purchase price Katia can afford.
• Use the PMT function to calculate the payments of Katia’s loan. Note that the PMT
function returns a negative value because it represents payments being made to the
lender. Thus, you can multiply the result of the PMT function by -1 to generate a
positive value.
In your solution, you must include a screenshot of your Goal Seek configuration.
Problem 3. Profit Forecast
Joe Adams, a New Zealand entrepreneur, wants to maximize his profit on the sale of New
Zealand handcraft. Joe has two items he wants to sell to tourists coming to New Zealand:
Items Retail Price Wholesale Cost
Maori Boat Woodcraft $349.95 $192.47
Maori Wooden Nesting Dolls $199.95 $109.99
Joe hires you to help him calculate his maximum profit. First, Joe would like to locate two more
products not listed in the table above with a retail price of $329.00 and $279.00, respectively. The
wholesale price of each unit is 55% of the retail price for both products. Joe has two constraints:
1. Joe’s budget to purchase the items from a local workshop is $200,000. Thus, the total
wholesale cost of the four items must be less than $200,000.
2. Joe must purchase between a minimum of 100 units and 500 units of each item from
the workshop.
Using Solver, maximize the total profit for Joe with the constraints mentioned above and limiting
the number of items to positive integers.
Problem 4. Customer Analysis
The sales manager of an international online retail store called Tilidom wants to
generate a report depicting customers’ distribution among different countries.
Using Excel and the Tilidom.csv file, create the following displays:
• A table that shows the total of sales and customers in each country
• A bar chart that displays the percentage of customers in each country