7-41 **Spreadsheet and Sensitivity analysis of Operating Expenses**

Study appendix 7. A speedy-Mart Store in Northcenter Mall has the following budgeted sales, which are uniform throughout the month:

May $450,000

June 375,000

July 330,000

August 420,000

Cost of goods sold averages 70% of sales, and merchandise is purchased and paid for essentially as needed. Employees earn fixed salaries of $22,000 monthly and commission of 10% of the current month’s sales, paid as earned. Other expenses are rent, $6000, paid on the first of each month for that month’s occupancy: miscellaneous expenses, 6% of sales, paid as incurred: insurance, $450 per month, from a 1-year policy that was paid for on January 2; and depreciation, $2850 per month.

1. Using Spreadsheet software, prepare a table of budget data for the Speedy-Mart Store.

2. Continue the Spreadsheet in number 1 to prepare budget schedules for (a) disbursement for operating expenses and (b) operating income for June, July, and August.

3. Adjust the budget data appropriately for each of the following scenarios independently and recomputed operating income using the Spreadsheet:

a. A sales promotion that will cost $30,000 in May could increase sales in each of the following 3 months by 5%.

b. Eliminating the sales commission and increasing employees’ salaries to $52,500 per month could decrease sales thereafter by a net of 2%

**7-46 Budgeting assumptions at Nike**

Examine **Nike**’s 2008 10-K presented in Appendix C (see** page 3**). Find the section of the 10-K titled “Results of Operations” showing a condensed income statement for fiscal years 2006, 2007, and 2008. Use condensed income statement to calculate budgeted net income for fiscal 2009 under the following alternative sets of assumptions:

1. Note that Nike’s revenues have increased by about 10% per year for each of the last 2 years. Assume cost of sales is 55% of revenues, selling and administrative expense is 32% of revenue and income tax expense is 25% of income before income taxes. Assume that all costs are variables

a) Calculate budgeted net income if revenues increases by 10%.

b) Calculate budgeted net income if revenues decrease by 10%.

2. Assume cost of sales is a variable cost and is 55% of revenues, selling and administrative expense is fixed, and income tax expense is variable and is 25% of income before income taxes.

a) Calculate budgeted net income if revenues increases by 10%.

b) Calculate budgeted net income if revenues decreases by 10%.

3. Note that Nikes gross margin was 45% in fiscal 2008 but was slightly lower in 2006 and 2007, at 44% and 43.9%, respectively. Assume revenue for 2009 will be the same as in 2008, selling and administrative expense is a fixed cost of $5,954 million, and income tax expense is 25% of income before income taxes.

a) Calculate budgeted net income if the gross margin increases to 46%.

b) Calculate budgeted net income if the gross margin decreases to 44%

Subject | Business |

Due By (Pacific Time) | 10/30/2013 08:00 pm |

Tutor | Rating |
---|---|

pallavi Chat Now! |
out of 1971 reviews More.. |

amosmm Chat Now! |
out of 766 reviews More.. |

PhyzKyd Chat Now! |
out of 1164 reviews More.. |

rajdeep77 Chat Now! |
out of 721 reviews More.. |

sctys Chat Now! |
out of 1600 reviews More.. |

sharadgreen Chat Now! |
out of 770 reviews More.. |

topnotcher Chat Now! |
out of 766 reviews More.. |

XXXIAO Chat Now! |
out of 680 reviews More.. |