Chid Abuse

COMPONENT PERCENTAGES INCOME STATEMENT (each item is expressed as a percentage of net sales revenue)

Pam and Susan’s department stores are in the process of opening a new business unit. There are two locations that are being considered for the new store and decision is based upon estimates of sales for both of them. My job is to use data gathered from each store as well census data in store’s trading zones to predict sales at both of the sites that are being consider for their newest store.

o In summary this analysis shows the percent of every dollar in sales that is

Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example, the base case number of units sold in Cell B105 should be the number 1000; you should NOT have the formula =D29 in that cell. This is because you'll use D29 as the column input cell in the data table and if Excel tries to iteratively replace Cell D29 with the formula =D29 rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just

Another spreadsheet used is the sales department. The formulas on this spreadsheet will calculate their commissions, the type of units sold or rented, the address of each customer and what the total commissions to be paid will be. There are several other excel spreadsheets used in the company in each department using formulas, however, they are all similar to what has been

550 1 794 2 724 2 779 2 946 3 123 3 310 3 508 3 719 NA NA NA NA 3,0% 59,2x 12,2x 33,7x NA NA NA NA Capital Expenditures 2011 0 4 610 Net Working Capital Accounts Cash (=as a % of Sales Revenue) Accounts

As prevision on the Master Budget, I could calculate the division of Sales Centre expenses. Each quarter accounted for certain percentage: Q1 = Q3 = Q4 = 17.500/ 77.500= 22.58%.

OF DAYS | TOTAL | AVE.SALES VOLUME | AVERAGE SALES PER DAY | NUM. OF DAYS | TOTAL | AVE.SALES VOLUME | AVERAGE SALES PER DAY | NUM. OF DAYS | TOTAL |   |   | | | | | | | | | | | | | | | |   | 250ml | 20 | 15 | 300 | 54 | 16,200.00 | 15 | 300 | 52 | 15,600.00 | 15 | 300 | 52 | 15,600.00 | 95,760.00 |   | 500ml | 30.4 | 15 | 456 | 54 | 24,624.00 | 12 | 364.8 | 52 | 18,969.60 | 14 | 425.6 | 52 | 22,131.20 | 131,358.40 |   | 1Liter | 51.2 | 14 | 716.8 | 54 | 38,707.20 | 14 | 716.8 | 52 | 37,273.60 | 15 | 768 | 52 | 39,936.00 | 218,265.60 |   |   |   |   |   |   |   |   |   |   |   | Total Sales for Lemon Diswashing | 445,384.00 | Kalamansi |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   | 250ml | 20 | 15 | 300 | 54 | 16,200.00 | 14 | 280 | 52 | 14,560.00 | 13 | 260 | 52 | 13,520.00 | 86,440.00 |   | 500ml | 30.4 | 13 | 395.2 | 54 | 21,340.80 | 14 | 425.6 | 52 | 22,131.20 | 15 | 456 | 52 | 23,712.00 | 129,595.20 |   | 1Liter | 51.2 | 15 | 768 | 54 | 41,472.00 | 13 | 665.6 | 52 | 34,611.20 | 14 | 716.8 | 52 | 37,273.60 | 213,094.40 |   |   |   |   |   |   |   |   |   |   |   | Total Sales for Kalamansi Diswashing | 429,129.60 | Apple |   |   |   |   |

To get started, just enter your figures into the cells on the template and all of the green coloured cells will auto-calculate based on your responses. If you are using accounting software, your forecast should mirror your chart of accounts, so adapt the names accordingly.

1. Sales forecast – (at $ 30 retail price with the assumption of $15 whole sale price)

In cell C4, enter the following formula: Year 2 Sales (cell C4) = Year 1 Sales * (1 + Annual Sales Growth) * (1 - Annual Price Decrease) or =B4 * (1 + $B$25) * (1 - $B$26). Be sure to use absolute references where appropriate in the formula. Copy cell C4 to the range D4:G4.

(Yi) Sales ($100,000) 6.4 8.3 8.8 5.1 9.2 7.3 12.5 (Xi) Births Preceding Year 2.9 3.4 3.5 3.1 3.8 2.8 4.2

I have recently touched base with Bill and have talked through and emailed several formulas from the spreadsheet we had created to keep track of his expenses. In the first he was wanting to add the daily totals in column F. Bill would need to use the =sum formula by double clicking the cell so he can put in the formula =sum (F5:F11) and that gives him the total to that column. Bill can use the same formula that he used in the request above he will need to make the cell numbers read differently instead of =sum (F5:F11) he will need to use =sum (B13:F13) and that will give him the total for that row. In the next formula Bill will need to use the IF function in column G the formula should read =IF (F57100, YES). For his additional request he can use the same formula. If the total is less then $100 he needs to leave the row blank. With all of the above going on Bill is brainstorming about expanding his business.

Use Freezing to Keep Key Rows and Columns Visible . . . . . . . . . Set Options to Make Excel Easier to Use . . . . . . . . . . . . . . . . . . . . . . . . . Choose View Options to Customize Excel’s Visual Appearance . . Understand (and Maybe Choose) Calculation Options . . . . . . . . . Set Edit Options to Fine-Tune Editing Maneuvers . . . . . . . . . . . . . Choose General Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Choose Suitable Save Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . Choose Transition Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Set Spelling Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Objective Questions MARKS 10 10 10 Total weightage given to these assignments is 30%. OR 30 Marks All assignments are to be completed as typed in word/pdf. All questions are required to be attempted. All the three assignments are to be completed by due dates (specified from time to time) and need to be