Once you reach a solution that satisfies the preceding constraints, save your workbook. Name the worksheet Rose Tree Guess

Once you reach a solution that satisfies the preceding constraints, save your workbook. Name the worksheet Rose Tree Guess

February 13, 2022
15 Views
0
0

ASSIGNMENT 1: CREATING SPREADSHEET MODELS FOR DECISION SUPPORT

In this assignment, you will create spreadsheets that model the business decision Rose Tree Regional Airline is seeking. In Assignment 1A, you will create a spreadsheet and attempt to assign the planes manually to minimize the total operating cost. In Assignment 1B, you will copy the spreadsheet to a new worksheet, and then set up and run Solver to minimize the total operating cost. In Assignment 1C, you will copy the Solver solution to a new worksheet and modify it to add calculations for taking on additional cargo. You will then rerun Solver to determine how the extra cargo affects the company’s total operating cost. In Assignment 1D, you will copy the modified Solver spreadsheet and rerun Solver to maximize daily gross profit.

This section helps you set up each of the following spreadsheet components before entering the cell formulas:

Constants
Calculations and Results
Income Statement

The Calculations and Results section is the heart of the decision model. You will set up columns for travel distance, daily demand, plane assignment by type, plane use, and operating costs. The spreadsheet rows will represent destination cities. The Plane Assignment section will be the range of changing cells for Solver to manipulate. The total operating cost will serve as your optimization cell for Assignments 1B and 1C, and the daily gross profit will be your optimization cell for Assignment 1D. You will add formulas to the additional cargo cells in the Calculations and Results section for Assignments 1C and 1D.

A discussion of each spreadsheet section follows. This information helps you set up each section of the model and learn the logic of the formulas in the spreadsheet. If you choose to enter the data directly, follow the cell structure shown in the figures. You can also download the spreadsheet skeleton if you prefer. To access the base spreadsheet skeleton, select Case 8 from your data files and then select RoseTreeRegionalAirline.xlsx.

Constants Section

First, build the skeleton of your spreadsheet. Set up the spreadsheet title and Constants section as shown in Figure 8-1. An explanation of the column items follows the figure.

Spreadsheet title—Enter the spreadsheet title in cell B1 and then merge and center the title across cells B1 through Fl.
Constants section, Plane Data table—Enter the column headings shown in cells B5 through G5.
Plane Type—Enter each of the four planes listed in cells B6 through B9.
Passenger Capacity—Enter each of the four passenger capacities listed in cells C6 through C9.
Cargo Capacity (ft3)—Enter each of the four cargo capacities listed in cells D6 through D9.
Operating Cost per Passenger-Mile—Enter each of the four operating costs per mile listed in cells E6 through E9.
Operating Cost per Cargo-Mile—Enter each of the four operating costs per mile listed in cells F6 through F9.
Available Fleet—This value is the number of planes of each type that Rose Tree Regional Airline keeps in service. Enter these numbers in cells G6 through G9.
Constants section, Fee Schedule table—Enter the column headings shown in cells B11 through D11.
Destination—Enter the seven destination cities in cells B12 through B18.
Average Ticket Price—Enter the passenger ticket prices for the seven destinations in cells C12 through C18.
Cargo Price/ft3—Enter the cargo price per cubic foot for the seven destinations in cells D12 through D18.
Fill Legend—This section is actually adjacent to the Constants section. Enter “Fill Legend” in cell J5, fill cell J6 in yellow, fill cell J7 in blue, enter “Changing Cells” in cell K6, and enter “Optimization Cell” in cell K7.

Calculations and Results Section

The Calculations and Results section (see Figure 8-2) will contain distances, daily passenger bookings, and daily cargo shipment data obtained from the operations department. Although these values are constants, keeping them in the Calculations and Results section facilitates writing and copying formulas in the Plane Utilization, Costs, and Extra columns. This section also includes the Plane Assignment table, which contains the changing cells and calculations for plane usage, costs, and additional cargo. An explanation of the sections and columns follows the figure.

If your formulas are correct, the initial Income Statement section will appear as shown in Figure 8-5.

The initial income statement correctly reflects the revenues expected from the passenger and cargo bookings, but the operating costs are not correct because the planes required to transport the passengers and cargo have not been completely assigned yet.

Attempting a Manual Solution

Attempt to assign your plane fleet manually in the spreadsheet. You have several good reasons for doing this. First, you can make sure your model is working correctly before you set up Solver to run. Second, assigning the plane fleet manually will demonstrate which constraints you must meet in solving the problem. For instance, if a passenger or cargo utilization rate is over 100%, you have not assigned enough planes to carry all the passengers and cargo to a particular destination. Therefore, one constraint is that the total passenger capacity for the planes assigned to a destination must be greater than or equal to the passenger bookings. Another constraint is that the total cargo capacity for the planes assigned must be greater than the cargo shipments booked. Given the fleet size, you can probably assign the fleet manually and meet all of your constraints. However, will your total operating cost be the least expensive solution? Running the problem manually will provide an initial operating cost to which you can compare your Solver solution later. The Solver optimization tool should give you a better solution than assigning the fleet manually.

When attempting to assign the planes manually in the Plane Assignment section (the changing cells), you must satisfy both the passenger and cargo demands for each destination—in other words, the Total Passenger Capacity values in cells J22 through J28 and the Total Cargo Capacity values in cells L22 through L28 must be equal to or greater than the Daily Demand values in cells D22 through D28 and E22 through E28. If you have satisfied the passenger and air freight demands correctly, none of the utilization rates in cells K22 through K28 and M22 through M28 will exceed 100%. In addition, the total planes assigned for each type (cells F29 through I29) cannot exceed the available number of each plane type (cells G6 to G9).

Once you reach a solution that satisfies the preceding constraints, save your workbook. Name the worksheet Rose Tree Guess and then right-click the worksheet name tab, click Move or Copy, copy the worksheet, and rename the new copy Rose Tree Solver 1. You will use the new worksheet to complete the next part of the assignment.

Answer and ExplanationSolution by a verified expert

Explanation
These are pointed primarilyat motorcyclists, albeit a couple of things are sold as style pieces of clothing.

Falcon offers 65% of its yield to huge corporate store such asMotorcycle City and Carnells, trades 25% to the USA and Japan, andsells the excess 10% to people who contact the organization directly.The last gathering of clients determine their necessities for a made tomeasure suit (they are regularly proficient racers whose suits must beapproved by the specialists, for example, the Auto Cycle Union). The largeretailers demand low edges and are delayed to settle their obligations.

There are around twelve organizations in the UK who make similarproducts to Hawk, in addition to a lot of different organizations who contend with muchlower costs and second rate quality. Bird of prey's regular selling cost for aoneâ­piece suit is £1,000, though the bad quality opponents' suits retailat around £400. As Hawk say in their writing "on the off chance that you hit thetarmac,there's not a viable alternative briefly skin from Hawk". Syntheticmaterials are waterproof, not normal for calfskin, yet don't presently offersufficient assurance in a mishap.

Deals of cowhides in the UK are developing quickly, primarily due to aresurgence of trekking from more develop riders of enormous, powerfulmachines. Such riders are frequently rich and have family and financialcommitments. Presently Hawk, and its adversaries for quality cowhides arefinding it difficult to stay aware of interest. Notwithstanding, Government strategy andEU discharges controls are probably going to restrict bike execution, andsome specialists anticipate that these guidelines will cause deals of largemotorcycles to level off.

While supplies of cowhide from Asia, Scandinavia and the UK areplentiful, a key issue is selecting and preparing mechanics to stitchand line the articles of clothing. Falcon has had the option to put resources into current machineryto help creation however the interaction is still work concentrated. Bird of prey hasfound that the mastery, notoriety and talented work required tosucceed in the business requires a long time to develop.

Albeit the business is genuinely customary, there are some newdevelopments, for example, a site for singular clients to peruse andspecify prerequisites, and new tones, for example, metallics for leathers,and a little yet developing interest from non bikers who are intrigued in'recreational' and 'club wear' things.

You find that between the past and current time frames there was a4% general expansion rate and it is figure that costs will furtherincrease by 6% in the following period.

The firm didn't build the selling cost in the current periodalthough contenders raised their costs by 4% to take into account theincreased costs.

A study by financial advisors was charged and has foundthat the interest for the item is flexible with an expected priceelasticity of interest of 1.5. This implies that volume will fall by one anda half times the pace of genuine cost increment.

The action grid beneath shows the financial plan for the business orderdepartment of Cognet Inc. Pertinent data as to theoperation of the business request division is as per the following:

iA group of staff manages existingcustomers in regard of issues with orders or with prospectivecustomers enquiring about possible orders.

ii The handling of requests requires correspondence with the creation and despatch elements of the organization.

iiiThe nature of the business is suchthat there is some despatching of part-requests to clients which helpsreduce stock holding expenses and helps clients in their work flowmanagement.

ivSales writing is conveyed to existing and imminent clients by methods for a month to month mail shot.

Cognet Inc has chosen to secure extra PC programming withinternet joins to improve the viability of the business orderdepartment. The expense to the organization of this activity is assessed at$230,000 dad.

It is assessed that there will be the accompanying expense and volume changes to exercises in the business request division:

(1)Reduction in by and large pay rates by 10% per annum, applied to the current compensation divisions.

(2)Reduction of 60% in the stores/supplies cost in the business writing action as it were.

(3)$20,000 of the program cost willbe apportioned to the business writing movement. The equilibrium will beshared by different exercises with respect to their current offer ofIT costs.

(4)Sundry expenses for client negotiation,processing of orders and executing despatches will change in proportionto the quantity of units of every movement. Various expenses for salesliterature and general organization will be unaltered.

Answer
Table A - Summary of monetary and other working data

Number of:

Note 1: incorporates materials, compensation/pay rates, vehicle and machine costs, and so forth

Note 2: following assessment by assessors after work executed

Note 3: starting overview and site investigation

Note 4: examination and activity on grievances.

Table B

Proposed system for Alpha division for year to 30 November 20X9

It is assessed that the planned benefit prerequisite of $20m will be accomplished as an outcome of the accompanying:

The quantity of orders got and prepared will be 11,000 (with normal value levels staying according to 20X8 genuine value levels) from an underlying complete of 15,500 client enquiries

The promoting cost remittance would be decreased to $7.2m

The preparation cost remittance would be decreased to $3m

Cost of deals ($) will ascend by 10% from the 20X8 real all out to take into account the consolidated impact of volume and value changes

Healing work on requests will add up to $1m for material, work and overhead expenses

Starting study and examination costs on client enquiries will stay at the 20X8 normal expense per enquiry

Client grumbling related expenses are relied upon to ascend to $0.25m.

Table C

Staff reward figuring for the year finished 30 November 20X8 utilizing KeyPerformance Indicators (KPI's) founded on relative agreement factors

(B)* â€" each KPI score esteem is positive (+) where the 20X8 valueshows an improvement over the earlier year OR negative (â€") where the20X8 esteem shows less fortunate execution than in the earlier year.

Each KPI score esteem is the % expansion (+) or decline (â€") in 20X8 as suitable

Question 2 - Environmental impacts

Purchase this answer to view it.
Click the button to login/signup and buy full solution at 2 USD only.

The Best Research Paper Writing Service

Would you want to pay someone to write your paper professionally from scratch? 100% Original and 0% AI Content!.

🎓 Write my Essay
📚 Write my Persuasive Essay
📋 Humanize AI Content for Turnitin
💻 Write my Reflective Essay
📑 Write my Research Paper
📜 Write my Thesis Paper
📘 Write my Dissertation
📋 Write my Case Study
📝 Write my Online Exam
✒️ Write my Term Paper
Write my Paper