Moving Company specializes in hauling heavy goods over long distances. The companys revenues and expenses depend on revenue miles, a measure that combines both weights and mileage. Summarized budget data for the next year are based on predicted total revenue miles of 800,000. At that level of volume, and at any level of volume in between 700,000 and 900,000 revenue miles, the companys fixed costs are $120,000. The selling price and variable costs are:

Per revenue mile

Average selling price (revenue)$1.50

Average variable expense$1.30

Maintenance Expenses Per 50,000 miles = $10,000

Average distance per customer = 500 miles

Admin and selling expenses per customer = $100

Fixed Costs increase to $200,000 over 900,000 revenue miles and decrease to $100,000 below 700,000 revenue miles.

Please compile all answers in a professional business Excel model similar to what you would present to your manager in an employment situation. Your manager should be able to select or view all the required possibilities listed in number 2 below.

model is related to what your textbook calls sensitivity analysis.....which has variables that can be changed or selected to see how they affect business operations

1. Compute the budgeted net income. Your Corporate tax rate is 30%

2. Management is trying to decide how various possible conditions or decisions might affect net income. Compute the new net income for each of the following changes. Consider each case independently.

-a. A 20% increase in sales price

-b. A 10% increase in revenue miles

-c. A 10% increase in variable cost

-d. A 10% increase in fixed cost

-e. An average decrease in selling price of 3 cents per revenue mile and a 5% increase in revenue miles. Refer to original data

-f. An average increase in selling price of 5 cents and a 10% decrease in revenue miles.

-g. A 10% increase in fixed expenses in the form of more advertising and a 5% increase in revenue miles. h. Investing in new trucks for $220,000 with a five year life and a $20,000 salvage value will increase capacity to handle 20% more customers. Should you make that investment? Why or why not. Show all calculations to support your conclusion.