Please use the Excel Solver to solve all exercise question(s) and upload all Excel files for each question.
The Excel file must be attached.
1) Jim and Shirley Irvin, a newly married couple, will be filing a joint tax return for the first year. Because both work as independent contractors (both are soccer coaches), their income is subject to some variability. However, because their earnings are not taxed at the source, they know that they must pay estimated income taxes on a quarterly basis, based on their estimated taxable income for the year. To help calculate this tax, the Irvins would like to set up a spreadsheet-based decision model. Assume that they have the following information available:
Their only source of income is from their jobs as soccer coaches. The would like to put away 3% of their total income in a retirement account, up to a maximum of $4,000. Any amount the put in that account can be deducted from their total income for tax purposes. They are entitled to a personal exemption of $3,300 each. There is a standard deduction for married couples of $11,500, meaning this amount is free from any taxes and can be deducted from total joint income. Jim makes an estimated $41,000 and Shirley makes an estimated $36,000. The tax brackets are 9% for up to $17,000, 14% for $17,001 to $70,000, and 21% for $70,001 to $140,000. What are the estimated taxes per quarter that Jim and Shirley must pay?
Please use the Excel Solver to solve the above exercise question(s) and upload Excel file section.
2) A company manufactures four products A, B, C, and D that must go through assembly, polishing, and packing before being shipped to a wholesaler. For each product, the time required for these operations is shown below (in minutes) as is the profit per unit sold.
Product Assembly Polish Pack Profit ($)
A 2 3 2 1.50
B 4 2 3 2.50
C 3 3 2 3.00
D 7 4 5 4.50
The company estimates that each year they have 1667 hours of assembly time, 833 hours of polishing time and 1000 hours of packing time available. How many of each product should the company make per year to maximize its yearly profit?
Please use the Excel Solver to solve the above exercise question(s) and upload your Spreadsheet Answer into the folder section.
3) A company wants to determine how to allocate its $200,000 advertising budget to market a new cereal. The company is considering newspaper ads, television ads, and radio ads. The following table illustrates the cost of advertising in these different media and the exposure to new customers reached by increasing the number of ads in each medium.
Media and Number of Ads No. of New Customers reached Cost per ad
Newspaper: 1-5 700 $500
Newspaper: 6-10 500 $400
Television: 1-10 9000 $5000
Television: 11-20 7500 $4000
Radio: 1-10 4000 $2000
Radio: 11-20 300 $1500
Use Excel to formulate and solve this problem to maximize audience exposure.
Please use the Excel Solver to solve the above exercise question(s) and upload your Spreadsheet Answer into the folder section.
4). A company can ship its product from any of its three factories, F1, F2, and F3, to any of its retail outlets, R1, R2, and R3. The capacity, demand, and shipping cost information is provided as follows:
Demand (units) Capacity (units)
R1: 300 F1: 250
R2: 500 F2: 350
R3: 200 F3: 400
Shipping Cost/unit ($)
R1 R2 R3
F1 1 3 2
F2 3 4 2
F3 2 2 3
The company wants to come up with an optimal shipping strategy that will allow it to minimize its total shipping cost.
Please use the Excel Solver to solve the above exercise question(s) and upload your Spreadsheet Answer into the folder section.
5). Bob Jenkins needs to drive from City 1 to City 7 and would like to find the shortest route between the two. The road system with the distance in miles between cities is shown in the network below. What cities should he travel through to minimize his distance?
Please use the Excel Solver to solve the above exercise question(s) and upload your Spreadsheet Answer into the folder section.
CHAPTER 6:
6) A company is considering producing 8 different products for the upcoming holiday season. The profit contribution per product is illustrated below:
Product Profit ($)
1 10
2 12
3 7
4 15
5 8
6 9
7 13
8 11
The marketing manager has imposed the following restrictions on the production mix:
• Since products 1, 2, and 3 are electronic gadgets, at least two of these products must be in the production mix.
• Since products 4 and 5 are kids-oriented, the production mix must include no more than one of these products.
• If product 7 is included in the mix, then product 8 must be included, and vice versa.
Which products should be included in the mix to maximize profit?
Please use the Excel Solver to solve the above exercise question(s) and upload your Spreadsheet Answer into the folder section.
9) Cars arrive at Sparkling Clean automatic car wash at the rate of 15 per hour and follow the Poisson distribution. It takes exactly 3 minutes for each car to go through the automated process of wetting, soaping, scrubbing, and drying.
a. What is the average time that each car spends in the queue?
b. What is the average time that each car spends in the queue and gets washed?
c. What is the probability that the next car that arrives will not have to wait?