# Excel代写 | INFOMGMT 192 Assignment #2

这个作业是用Excel计算贷款利息、盈利预测等问题

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.

Hints:

• 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