Building on the corrections suggested (if any) from Part I, Part II of the term assignment continues with the introduction of the Master Budget. Students are required to first transfer the balance sheet and income statement to excel in order to use the data to make a quarterly budget. Following the examples from the master budgets worked on in class, students are required to make the necessary budget schedules to build a Cash Budget and Capital Budget.
Question Set 1:
1) Transfer your company’s balance sheet to an excel sheet like you did with your income statement. Also include a screenshot of the original balance sheet and income statement in the worksheet on excel.
2) Track your company’s stock price this week. Record the amount the stock price increased or decreased each day, totaling five days. I will provide an example of this during class.
Question Set 2:
1) What is the strategic plan of your company? Are there any specific long range plans? (Form -10k or Annual Report)
2) With your sales forecast, you now have to assemble the data to make your master budget. Below is a list of the data points you will have to find using your company’s most recent income statement, balance sheet, and possibly cash flow statement:
– Divide your cost of goods sold by total sales to get the cost of goods sold percentage
– Add together the fixed expsenses form your income statement and divide the total by 12 to get the monthly total.
– Add togetherbthe variable expenses (excluding COGS) from the income statement and divide the total by the sales revenue total to get your variable expense percentage.
– Divide the depreciation expense total by 12 to get the monthly depreciation.
– Divide the Income tax expense by Earnings before income taxes to find the income tax rate.
– Search for a rate of interest from the annual report of the company. Use the rate you find when you make a capital budget.
Additional Information Needed to Make Master Budget
– Sales are 40% Cash and 60% Credit. Of the credit sales, 30% are collected the next month, 40% the month after, and 25% the month after that, and 5% are determined to be uncollectible.
– Your account receivable total from your balance sheet will be collected as follows: 40% the next month, 40% in two months, 15% in three months, and 5% is determined to be uncollectible.
– 50% of your account payable total is inventory bought on account which you pay for the next month.
– Whatever your inventory total is on your balance sheet, you must keep 20% of this as ending inventory each month (Like the example we did in class).
– You must maintain 10% of your cash total from you balance sheet in your cash account (This applies to the cash budget).
Rubric
Term Assignment Part II
| Criteria | Ratings | Pts | |||||
|---|---|---|---|---|---|---|---|
|
This criterion is linked to a Learning OutcomeCorrections from Part IIf there are any corrections or additions to make to Part I, they must be made and added to this submission. |
|
2 pts |
|||||
|
This criterion is linked to a Learning OutcomeFinancial Statements Transferred to ExcelAre screenshots of the original income statement and balance sheet attached to the excel file? Is the data transferred to excel with the sub totals calculated and not typed in? |
|
2 pts |
|||||
|
This criterion is linked to a Learning OutcomeSales Forecast & Basic Budget InformationDoes the sales forecast originate from the stock tracking that was required? Is the budget information clearly presented in the excel file? |
|
2 pts |
|||||
|
This criterion is linked to a Learning OutcomeMaster Budget SchedulesThe master budget consists of a sales, cash collections, inventory purchases, cash disbursements for inventory, operating expenses budget, cash budget, and capital budget. These should be organized in excel on separate worksheets with the data linking from one sheet to the next. |
|
9 pts |
|||||
|
Total Points: 15 |
|||||||
0 comments