Management Information Systems Worksheet with Formulas

Agreed purchase price of the house (£)

Annual cost of living increase (£)

Annual buildings insurance premium (£)

Annual house maintenance expense (£)

Annual house appreciation rate (%)

Bank deposit interest rate (%)

Bank loan interest rate (%)

Cost of petrol per litre (£)

Council Tax Band A, lowest value (£)

Council Tax Band B, lowest value (£)

Council Tax Band C, lowest value (£)

Days worked per year (days)

Deposit percentage (%)

Distance of house from work (km)

Expected value of renovated house (£)

Initial renovation expenses (£)

Mortgage interest rate (%)

Mortgage percentage (%)

Mortgage repayment period (years)

Personal loan repayment period (months)

Petrol consumption (km/litre)

Savings (£)

Solicitor's fees (£)

Stamp duty limit (£)

Stamp duty rate (%)

VAT rate (%)

INITIAL COSTS

Deposit

PurchasePrice*10%

Solicitor's Fees (including VAT)

SolicitorFee+(SolicitorFee*17.5%)

Stamp Duty Applicable

IF (PurchasePrice>StampDutyLimit, PurchasePrice*StampDutyRate,0)

Total initial costs

Deposit+SolicitorFeeIncVAT+StampDutyApplicable

Amount of personal loan required

Amount placed in bank

CONTINUING COSTS

Year 1

Council tax

VLOOKUP (PurchasePrice,$B$11:$C$13,2)

Insurance premiums

AnnualInsurancePremium

House maintenance

AnnualHouseMaintenance

Travel to and from work

DistanceOfHouseFromWork*2)/PetrolConsumption)*CostOfPetrolPerLiter)*

DaysWorkedPerYear

Initial renovation

InitialRenovationExpense

Mortgage repayments

1)*PMT (MortIntRate, MortRepayPeriod, MortPerc*PurchasePrice)

Personal loan repayments

Less bank account interest

AmtPlacedInBank*BankDepIntRate)*(-1)

Total continuing costs

CouncilTax+InsurancePremium+HouseMaintenance+Travel_to_and_from_work+

InitialRenovation+MortgageRepayments+PersonalLoanRepayments+

LessBankAccountInterest

CAPITAL COSTS

Interest on savings lost

Savings*BankDepIntRate)+LessBankAccountInterest

END OF YEAR SUMMARY

Total costs during year

TotalContinuingCost+InterestOnSavingsLost

Total monthly cost during year

TotalCostsDuringYear/12

Total costs to date at end of year

TotalCostsDuringYear

Value of house at end of year

PurchasePrice+(PurchasePrice*AnnualHouseAppreciationRate)

HOUSE A VARIABLE SECTION

Variable

Agreed purchase price of the house (£)

Annual cost of living increase (£)

Annual buildings insurance premium (£)

Annual house maintenance expense (£)

Annual house appreciation rate (%)

Bank deposit interest rate (%)

Bank loan interest rate (%)

Cost of petrol per litre (£)

Council Tax Band A, lowest value (£)

Council Tax Band B, lowest value (£)

Council Tax Band C, lowest value (£)

Days worked per year (days)

Deposit percentage (%)

Distance of house from work (km)

Expected value of renovated house (£)

Initial renovation expenses (£)

Mortgage interest rate (%)

Mortgage percentage (%)

Mortgage repayment period (years)

Personal loan repayment period (months)

Petrol consumption (km/litre)

Savings (£)

Solicitor's fees (£)

Stamp duty limit (£)

Stamp duty rate (%)

VAT rate (%)

INITIAL COSTS

Deposit

Solicitor's Fees (including VAT)

Stamp Duty Applicable

Total initial costs

Amount of personal loan required

Amount placed in bank

CONTINUING COSTS

Year 1

Year 2

Year 3

Year 4

Year 5

Council tax

Insurance premiums

House maintenance

Continuation of House A Travel to and from work

Initial renovation

Mortgage repayments

Personal loan repayments

Less bank account interest

Total continuing costs

CAPITAL COSTS

Interest on savings lost

END OF YEAR SUMMARY

Total costs during year

Total monthly cost during year

Total costs to date at end of year

Value of house at end of year

HOUSE B

VARIABLE SECTION

Variable

Agreed purchase price of the house (£)

Annual cost of living increase (£)

Annual buildings insurance premium (£)

Annual house maintenance expense (£)

Annual house appreciation rate (%)

Bank deposit interest rate (%)

Bank loan interest rate (%)

Cost of petrol per litre (£)

Council Tax Band A, lowest value (£)

Council Tax Band B, lowest value (£)

Council Tax Band C, lowest value (£)

Days worked per year (days)

Deposit percentage (%)

Distance of house from work (km)

Expected value of renovated house (£)

Initial renovation expenses (£)

Mortgage interest rate (%)

Mortgage percentage (%)

Mortgage repayment period (years)

Personal loan repayment period (months)

Continuation of House B

Petrol consumption (km/litre)

Savings (£)

Solicitor's fees (£)

Stamp duty limit (£)

Stamp duty rate (%)

VAT rate (%)

INITIAL COSTS

Deposit

Solicitor's Fees (including VAT)

Stamp Duty Applicable

Total initial costs

Amount of personal loan required

Amount placed in bank

CONTINUING COSTS

Year 1

Year 2

Year 3

Year 4

Year 5

Council tax

Insurance premiums

House maintenance

Travel to and from work

Initial renovation

Mortgage repayments

Personal loan repayments

Less bank account interest

Total continuing costs

CAPITAL COSTS

Interest on savings lost

END OF YEAR SUMMARY

Total costs during year

Total monthly cost during year

Total costs to date at end of year

Value of house at end of year

HOUSE C

VARIABLE SECTION

Variable

Agreed purchase price of the house (£)

Continuation of House C

Annual cost of living increase (£)

Annual buildings insurance premium (£)

Annual house maintenance expense (£)

Annual house appreciation rate (%)

Bank deposit interest rate (%)

Bank loan interest rate (%)

Cost of petrol per litre (£)

Council Tax...

Through the tabular form of expected expenses and expected value of the houses, a buyer can easily see the differences between each house, as well as whether the values match the buyer's financial capabilities.
The Excel features also serve as useful tools that facilitate computations. Such features become handy especially when certain values need to be changed. Through the Excel's capability of storing formulas, automatic computation is made possible. This feature eliminates the need for redundant computations should a value in the variable area of our examples' Scenario Manager is changed.

Aside from the ability to facilitate computations, the Scenario Manager and the Excel features eliminates the time-consuming process of waiting for the essential computational results before a decision can be made. As in our examples, the Scenario Manager and the Excel features can aid a buyer in his decision-making process through a presented organized view of the necessary factors, particularly the expected amount of expenses and liabilities when buying a house, before coming up with a decision.

Every process of decision-making entails the need for a detailed and clear presentation of information to allow a decision-maker see the advantages and disadvantages between available options. This necessity is the fundamental feature that our examples' Scenario Manager provides. Through the organized structure of a house's details and computations, a buyer can easily pre-decide in just a brief study of the information provided by the Scenario Manager.

Implications and Relative Advantages and Disadvantages of Buying Each House

Each house presented has relative advantages and disadvantages to a buyer. Following is a discussion of these factors based on each house and based on comparison of each with the other houses.

Buying house A, being the least expensive of the four houses in terms of purchase price, offers the most financial convenience to a buyer in terms of initial costs. Compared to the other three houses, house A requires the lowest deposit. Unlike houses B, C, and D, house A has no stamp duty applicable due to its below stamp duty limit purchase price. Another advantage of house A is in its distance from work. It is the second nearest house to work among the four houses. However, despite of the low initial costs, the financial load comes to the buyer in terms of renovation costs. House A has the most expensive renovation costs among the four houses. Hence, during the first two years, it has the highest annual costs/expenses.

House B, on the other hand, is the second least expensive among the four houses in terms of purchase price. The two main advantages in buying this house is that it has the least annual house maintenance cost and the nearest house from work (only 2 km). These aspects are beneficial to the annual cost computation of House B, thus, making it the least expensive in terms of annual cost. The disadvantage of this house, however, is that its expected value after renovation is less than the total of its purchase price and the renovation expenses.

House C. is the most expensive house, equal to house D, among the four houses in terms of purchase price. The only advantage in buying this house is that it has a low initial renovation expense. Compared to house D, which is of the same purchase price, a buyer can save more in house C. Its annual house maintenance expense, initial renovation expense, and solicitor's fee, are all lower than those of house D. Aside from this, house C. is nearer to work than house D. Thus, making the continuing expense of travel from home to work lesser than that of house D. Adding all these advantages, in comparison to house D, house C. has lesser annual costs.

Buying house D. is perhaps suitable for those with good financial capabilities. Aside…