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?