Project Description:

You are the chief financial officer for Viking Heating and Air. You were given the task of increasing gross income from $91,000 to $120,000 per year. Vikings income is earned through service calls. Customers are charged trip and hourly charges as pictured in the table below. Your goal is to create a spreadsheet to enable you to evaluate the most economically feasible option for increasing gross income while adhering to the following constraints. The maximum amount of hours billed without hiring additional technicians is 2,500. The trip charge may not exceed $50.00, and the service calls/hourly rate/trip charge must all be whole numbers.

Steps to Perform:

Step	Instructions	Points Possible
1	Open the downloaded file exploring_e06_grader_h3_Viking Heating and Air.xlsx.	0
2	Create range names based on the values in the range A2:B5.	10
3	Use the formula Service_calls * Trip_charge + Hourly_rate* Hours_billed to determine gross income.	5.5
4	Load the Solver add-in. Set the objective cell B6 to 120000 and set B2:B5 as Changing Variable Cells.	12
5	Set three separate constraints to ensure service calls, hourly rate, and trip charge are integers.	15
6	Set a constraint to ensure hours billed cannot exceed (less than or equal to) 2500.	10
7	Set a constraint to ensure the trip charge does not exceed (less than or equal) 50.	10
8	Set a constraint to ensure hourly rate does not drop below (greater than or equal to) 30.	10
9	In the Solver Options dialog box, ensure that Use Automatic Scaling is selected (on the All Methods tab), and that Require Bounds on Variables is selected on the next two tabs. Click OK. Solve and create an Answer Report to outline your findings.	17.5
10	On the Viking AC worksheet, create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.	10
11	Save and close the file. Based on your instructors directions, submit exploring_e06_grader_h3_Viking Heating and Air.xlsx.	0

Total Points

100

Su exploring_e06-grader_h3_Viking Heating and Air - Excel Tell me what you want to do... Calibri -12- A A General Data Review View DYMO Label === 2 Ep Wrap Text EE 3Merge & Center, Alignment $ 8. % , Number Custom Style 3 OJNCHd... PUV Conditional Format as Yn+6Kyoou/... Normal Bad Formatting Table Styles File Home Insert Page Layout Formulas - Cut Le Copy Paste Format Painter BIU D - A Clipboard Font B15 x for A B C D 1 Viking Heating and Ai 2 Service calls 640 3 Hourly rate $30.00 4 Trip charge $ 25.00 5 Hours billed 2500 6 Gross income E F G H I K L M Su exploring_e06-grader_h3_Viking Heating and Air - Excel Tell me what you want to do... Calibri -12- A A General Data Review View DYMO Label === 2 Ep Wrap Text EE 3Merge & Center, Alignment $ 8. % , Number Custom Style 3 OJNCHd... PUV Conditional Format as Yn+6Kyoou/... Normal Bad Formatting Table Styles File Home Insert Page Layout Formulas - Cut Le Copy Paste Format Painter BIU D - A Clipboard Font B15 x for A B C D 1 Viking Heating and Ai 2 Service calls 640 3 Hourly rate $30.00 4 Trip charge $ 25.00 5 Hours billed 2500 6 Gross income E F G H I K L M