View the step-by-step solution to:

MGT150B - Spring 2013 - Homework #6 MGT150B - Spring 2013 Homework #6 A few important notes A. There are practice problems with solutions on...

Please attach your excel file with .xlsm (Save as Macro-Enable workbook).
MGT150B - Spring 2013 - Homework #6 MGT150B – Spring 2013 Homework #6 A few important notes A. There are practice problems with solutions on Blackboard. Please work through those problems first, before tackling the homework assignment. B. Homework is graded for both correctness and neatness. Your spreadsheet should be easy to understand, well laid out, and presented in a professional manner. If there is anything you think might be ambiguous in your presentation or solution – explain it! C. Submit the spreadsheet with answers both on Blackboard, AND bring a hardcopy to class. However, the softcopy may not be evaluated. D. As in all assignments, read through until the end before starting your analysis. Candy Express Candy Express operates 20 stores in 5 states. The chain specializes in fine chocolates and other sweet treats. Yes, a lot of their business is around Valentine’s Day. The company’s stores vary in size from 2,000 square feet to 13,000 square feet. The economics of both revenue and cost vary by store size. The list of stores is on Blackboard. Monthly Cost : Store cost depends both on location and size. Each store has a fixed cost of operation, and a variable cost, which is linear in the store size. Both of these depend on the state the store is licensed in, as detailed in the following table: State Fixed Cost of Operation Variable Cost of Operation (per 1 sq. ft. of space) Arkansas $4,000 $2 Illinois $10,000 $5 Missouri $8,000 $4 Oklahoma $5,000 $2 Texas $6,000 $3 You’ll need to develop a Custom Function in Excel to relate store size to monthly cost. Store revenue : Store revenue depends on size, too. Small stores, those below a specified threshold have revenue of $10 per month per square foot of store space. Large stores exhibit economies of scale. Their monthly revenue per square foot is $20. The threshold for determining if a store is small or large is 10,000 sq. ft. Assignment [1]
Background image of page 1
MGT150B - Spring 2013 - Homework #6 Develop a model in Excel to calculate total monthly profit for the chain. Specifically include the following: Calculate monthly revenue using an IF statement. The threshold for small or large store should be a parameter of your model. Develop a Custom Function to calculate monthly cost as a function of State and size. Calculate monthly profit per store At the bottom of the list calculate: o Total profit for all small stores o Total profit for all large stores o Total monthly profit for all stores. Using a One-way Data Table find the impact of a change in the revenue of small stores from the current value of $10 per sq. ft. to each value in the range $8 - $18. For each revenue alternative identify (1) total profit for all small stores; (2) total profit for all large stores; (3) total monthly profit for the chain. Using a One-way Data Table to find the impact of a change in the revenue of large stores from the current value of $20 per sq. ft. to each value in the range $16 - $26. For each revenue alternative identify (1) total profit for all small stores; (2) total profit for all large stores; (3) total monthly profit for the chain. Using a Two-way Data Table find the impact of a change in both the revenue of small stores from the current value of $10 per sq. ft. to each value in the range $7 - $12 and in the revenue of large stores from the current value of $20 per sq. ft. to each value in the range $16 - $26. For each alternative identify total monthly profit for the chain. Hint : it’s a bit easier if you have the values for the large stores along the left-most column, and small stores across the top row. [2]
Background image of page 2
Sign up to view the entire interaction

Top Answer

Here is the explanation for... View the full answer

solution (1).xlsm

State State
Missouri
Oklahoma
Texas
Missouri
Missouri
Illinois
Texas
Arkansas
Illinois
Oklahoma
Illinois
Illinois
Missouri
Oklahoma
Arkansas
Arkansas
Oklahoma
Texas
Arkansas
Texas Store SizeType...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online