IFSM 201
You are the CFO (chief financial officer) of Midwest Universal Gas
preparing the annual financial statement. Below are the "business
formulas" that are to be used in the spreadsheet. This is a typical
way a spreadsheet is created: you have, or know, or are given, the
business formulas and some data and then you turn them into a worksheet.
Copy and paste the layout of the spreadsheet below into an Excel worksheet.
Apply some formatting to make it look professionally appealing.
Because the monetary values are in thousands of dollars, don't show decimal points.
Add a column chart that shows Total Sales, Total Expense and Net
Income for the four quarters.
Fit the worksheet onto one printed page. The chart can be on its own page.
BCF stands for Billion Cubic Feet.
Retail gas sold is 22% of Total Gas Sold.
Wholesale gas sold is 78% of Total Gas Sold.
Retail sales is the Retail Price times the Retail gas sold.
Note: use absolute cell referencing to reference the Retail Price
cell so that when copying the formula the reference doesn't change.
i.e. put $ in front of both the row and column addresses.
e.g. $B$6
Wholesale sales is the Wholesale Price times the Wholesale gas sold.
Total Sales is Retail sales plus Wholesale sales.
Cost of sales is 61% of Total Sales.
Marketing and selling is 14.4% of Total Sales.
Depreciation per quarter is one-fourth of the annual rate of 8.6% times Investments.
Total Expense is the sum of the four expense categories.
Operating Income is Total Sales minus Total Expense.
Interest Expense per quarter is one-fourth of the annual rate of 12%
times Short-term debt plus one-fourth of the annual rate of 10.5% times Long-term debt.
Income Before Taxes is Operating Income minus Interest Expense.
Income Taxes is 46% of Income Before Taxes.
Net Income is Income Before Taxes minus Income Taxes.
Return on Investment is Net Income divided by Investments. It's a percentage.
MIDWEST UNIVERSAL GAS
Pro Forma Income Statement
(Dollars in thousands)
Retail Price $350.00
Wholesale Price $286.00
1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Total
--------------- --------------- --------------- --------------- ---------------
Production (in BCF):
Retail gas sold
Wholesale gas sold
--------------- --------------- --------------- --------------- ---------------
Total Gas Sold 228 201 82 221
Revenues:
Retail sales
Wholesale sales
--------------- --------------- --------------- --------------- ---------------
Total Sales
Expenses:
Cost of sales
Marketing and selling
General admin 4465 3740 3980 4065
Depreciation
--------------- --------------- --------------- --------------- ---------------
Total Expense
Operating Income
Interest Expense
--------------- --------------- --------------- --------------- ---------------
Income Before Taxes
Income Taxes
--------------- --------------- --------------- --------------- ---------------
Net Income
--------------- --------------- --------------- --------------- ---------------
Return on Investment
Supporting Items:
Investments 102400
Short-term debt 13933
Long-term debt 39200