Homework 3 (Individual)
1. Business Plan Model 15 points
Build a spreadsheet that provides a financial model for the Business Plan Spreadsheet Modeling Exercise. As always with financial models, years are across the columns, and financial information are down the rows.
Your model should have good spreadsheet engineering.
Compute an appropriate NPV using WACC as the discount rate.
Don’t do any other analysis at this time.
2. Bloomex Case 25 points
Create an “evaluative model” for the Bloomex case. Use your evaluative model to examine two proposed shipping plans and report on how well each plan meets Bloomex’s needs.
You might want to look at Powell & Baker sections 10.2 and especially 10.4.
A. Build a spreadsheet evaluative model that you can use to determine if a given shipping plan satisfies Bloomex’s business requirements. Outputs from you model should include:
· Total boxes shipped from each Supply Point
· Total boxes shipped to each Transshipment Center
· Total boxes shipped from each Transshipment Center
· Total boxes shipped to each Demand Point
· Total cost
Set all Decision Variables (shipment quantities) to 1. (Setting all DVs to 1 is a smart way to check model calculations; it is called “The Ones Test”.)
B. Feasibility: Two proposed shipping plans are provided. Does the plan satisfy Bloomex’s business needs? Is anything strange about the plan? (Don’t worry about whether the plan is “smart”, just does it “work”?) Show your work.