Description
First step: Financial Impact of Operations
Using an Excel spreadsheet, calculate the bottom line improvement using the base line below under these scenarios:
- Increase sales by 50%
- Reduce finance costs by 50%, keeping sales at $100,000
- Reduce production costs by 20% (cost of goods sold), keeping sales and finance costs at the initial levels
What option offers the best overall result?
Upload the Excel file with your calculations.
Communicate with your instructor if you have difficulties.
Base Line Financials
Item
Base Line
Sales
$100,000
Cost of goods sold
$80,000
Gross margin
$20,000
Finance costs
$6,000
Subtotal
$14,000
Taxes at 25%
$3,500
Contribution
$ 10,500
Second Step: Multifactor Productivity
Guido’s Pizzeria produces 750 pies per day. To accomplish this, it needs three employees, one driver, and a small dining room. It also uses 250 pounds of wheat, 120 pounds of cheese, 145 pounds of tomato sauce, and 30 pounds of pepperoni. The daily energy and water consumption is 65 kWH and 210 gallons, respectively.
Average fully allocated labor cost is $14.2 per hour. Assume 8 paid hours per work day. About half the pizzas produced are delivered by third-party drivers at a fee of 45 cents per pizza.
The cost per pound of wheat is $2.20. Cheese costs $1.30 per pound, tomato sauce is $0.95 and pepperoni is $3.60.
Electricity is charged at $1.99 per kWH and water costs 7 cents per gallon.
Equipment amortization is estimated at $450 per month and property rental is $2,500 per month. Guido’s operates 360 days per year.
As a consultant to Mr. Guido, your job is build the framework of a model in Excel to calculate multifactor productivity.
