Assignment 2 (DS-660) (20 points)
1. Return on investment (ROI) is computed in the following manner: ROI is equal to turnover multiplied by earnings as a percent of sales. Turnover is sales divided by total investment. Total investment is current assets (inventories, accounts receivable, and cash) plus fixed assets. Earnings equal sales minus the cost of sales. The cost of sales consists of variable production costs, selling expenses, freight and delivery, and administrative costs. (2 Points)
a. Construct an influence diagram that relates these variables.
b. Define symbols and develop a mathematical model.
2. A bank developed a model for predicting the average checking and savings account balance as balance = -17,732 + 367 * age + 1,300 * years education + 0.116 * household wealth. (2 Points)
a. Explain how to interpret the numbers in this model.
b. Suppose that a customer is 32 years old, is a college graduate (so that years education = 16), and has a household wealth of $150,000. What is the predicted bank balance?
3. A firm installs 1500 air conditioners which need to be serviced every six months. The firm can hire a team from its logistics department at a fixed cost of S6,000. Each unit will be serviced by the team at $15.00. The firm can also outsource this at a cost of SI 7.00 inclusive of all charges. (2 Points)
a. For the given number of units, compute the total cost of servicing for both options.
Which is a better decision?
b. Find the break-even volume and characterize the range of volumes for which it is more economical to outsource.
4. A manufacturer of mp3 players is preparing to set the price on a new model. Demand is thought to depend on the price and is represented by the model D = 2,500 - 3P
The accounting department estimates that the total costs can be represented by C = 5,000+ 5D
Develop a model for the total profit in terms of the price, P. (1 Point)
5. The Excel file Firm Data shows the prices charged and different product sizes. Prepare a worksheet using VLOOKUP function that will compute the invoice to be sent to a customer when any product type, size, and order quantity are entered. (2 Points)
6. The Excel file Store and Regional Sales Database provides sales data for computers and peripherals showing the store identification number, sales region, item number, item description, unit price, units sold, and month when the sales were made during the fourth quarter of last year. Modify the spreadsheet to calculate the total sales revenue for each of the eight stores as well as each of the three sales regions. (1 Point)
7. The Excel file President's Inn Guest Database provides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed-and-breakfast inn during one month.4 Room rates are the same for one or two guests; however, additional guests must pay an additional $20 per person per day for meals. Guests staying for seven days or more receive a 10% discount. Modify the spreadsheet to calculate the number of days that each party stayed at the inn and the total revenue for the length of stay. (2 Points)
8. The worksheet Base Data in the Excel file Credit Risk Data provides information about 425 bank customers who had applied for loans. The data include the purpose of the loan, checking and savings account balances, number of months as a customer of the bank, months employed, gender, marital status, age, housing status and number of years at current residence, job type, and credit-risk classification by the bank.
a. Use the COUNTIF function to determine
i. how many customers applied for new-car, used-car, business, education, small appliance, and furniture loans
ii. the number of customers with checking account balances less than $500.
b. Modify the spreadsheet using IF functions to include new columns, classifying the checking and savings account balances as low if the balance is less than $250, medium if between $250 but less than $2000, and high otherwise. (8 Points)