Refer to Table 1. Write the Excel formula for each cell
marked with “?” in column C such that formula could be copied and
pasted into columns D and E using Microsoft Excel without further
editing. There is no need to explicitly write the Excel formula for
cells marked with “copy & paste”.
New Age Dolls makes the very successful line of Micky and Mimi
dolls, which rapidly gained popularity with children and teenagers
after only a few years on the market. New Age Dolls produces dolls
at two locations: St. Louis (STL) and Boston (BSN), and has been
leasing warehouses at both locations. Warehouses are used for
storage and for distribution to retail stores. New Age management
is not happy with the terms of the leases, which are too expensive.
The lease expires at the end of 2021. New Age management has
decided to build its own warehouses and has three possible
locations: Portland(PT),
Atlanta(AT) and Phoenix(PX)
(which is entered in cell C22), but New Age would construct
warehouses at only two of these locations. To choose these two
locations, New Age would like to forecast the net income, debt owed
and cash flow for the following three years (2022 to 2024) based on
2021’s data. You are asked to help New Age and write Excel formulas
in cells C25 to C76 for this forecast by performing a what-if
analysis using Microsoft Excel. One major factor that affects the
forecast is the expected state of economy over the three years
(2022 to 2024) in row 21:
The expected state of economy can vary from year to year. If the
expected state of economy is H for year 2022,
F for year 2023, and F for year
2024, then the pattern HFF would be entered in
cells C21 to E21.
Assume that there are no input errors (or typing mistakes) in
row 21, or c22 cell.
New Age management expects to sell all the dolls they produce in
Boston and St. Louis in the three years (2022 to 2024). Production
of the two types of dolls will be divided evenly between the two
locations. The expected production (and sales) levels are given
below:
Year 2022
Year 2023
Year 2024
Micky – St. Louis
500,000 (cell C4)
750,000 (cell D4)
1,000,000 (cell E4)
Mimi – St. Louis
750,000 (cell C5)
850,000 (cell D5)
1,000,000 (cell E5)
Micky – Boston
500,000 (cell C6)
750,000 (cell D6)
1,000,000 (cell E6)
Mimi – Boston
750,000 (cell C7)
850,000 (cell D7)
1,000,000 (cell E7)
To reduce distribution and storage risks (from strikes, natural
disasters etc.), management plans to send half of each plant’s
production to each of the two selected warehouse sites. Shipping
costs from each plant to each of the three warehouse sites differ.
Storage costs at each of the three warehouse sites differ as well.
The shipping and storage costs per doll are given below:
(From)Factory
(To)Warehouse
Year 2022
Year 2023
Year 2024
St. Louis
Portland
4.5 (cell C9)
4.95(cell D9)
5.45(cell E9)
St. Louis
Atlanta
5.5 (cell C10)
6.05(cell D10)
6.66(cell E10)
St. Louis
Phoenix
3.5 (cell C11)
3.85(cell D11)
4.24(cell E11)
Boston
Portland
6.0 (cell C12)
6.6 (cell D12)
7.26(cell E12)
Boston
Atlanta
4.5 (cell C13)
4.95(cell D13)
5.45(cell E13)
Boston
Phoenix
5.5 (cell C14)
6.05(cell D14)
6.66(cell E14)
The following constants (rows 15 to 17)
for the forecast are described below:
Calculations (rows 25 to 49) are described
below:
Flat Economy
Economy Heats up
Dolls made in St. Louis
1% per year
15% per year
Dolls made in Boston
2% per year
5% per year
Income & Cash Flow Statements (rows 53 to 70) are
described below:
Debt Owed (rows 73 to 76) is described
below:
Table 1: ‘NA’ = Not Applicable, meaning no entry is required in
the cell.
- Answer The E G C8 B2 Refer To Table 1 Write The Excel Formula For Each Cell Marked With In Column C Such T 1 (58.63 KiB) Viewed 35 times
- Answer The E G C8 B2 Refer To Table 1 Write The Excel Formula For Each Cell Marked With In Column C Such T 2 (49.14 KiB) Viewed 35 times
- Answer The E G C8 B2 Refer To Table 1 Write The Excel Formula For Each Cell Marked With In Column C Such T 3 (50.99 KiB) Viewed 35 times
- Answer The E G C8 B2 Refer To Table 1 Write The Excel Formula For Each Cell Marked With In Column C Such T 4 (24.64 KiB) Viewed 35 times
- Answer The E G C8 B2 Refer To Table 1 Write The Excel Formula For Each Cell Marked With In Column C Such T 5 (21.52 KiB) Viewed 35 times
A B с D E 24 CALCULATIONS 2021 2022 2023 2024 25 INTEREST RATE FOR YEAR NA ? [1.5 marks] copy & paste copy & paste 26 NUMBER OF DOLLS SHIPPED 27 FROM STL TO PT NA ? (1.5 marks] copy & paste copy & paste 28 FROM STL TO AT NA? [1.5 marks] copy & paste copy & paste 29 FROM STL TO PX NA ? [1.5 marks] copy & paste copy & paste 30 FROM BSN TO PT NA ? (1.5 marks] copy & paste copy & paste 31 FROM BSN TO AT NA ? [1.5 marks] copy & paste copy & paste 32 FROM BSN TO PX NA ? [1.5 marks] copy & paste copy & paste 33 SHIPPING & WAREHOUSING COSTS 34 FROM STL TO PT NA ? [1 mark] copy & paste copy & paste 35 FROM STL TO AT NA ? [1 mark] copy & paste copy & paste 36 FROM STL TO PX NA ? [1 mark] copy & paste copy & paste 37 FROM BSN TO PT NA ? [1 mark] copy & paste copy & paste 38 FROM BSN TO AT NA ? [1 mark] copy & paste copy & paste 39 FROM BSN TO PX NA ? [1 mark] copy & paste copy & paste 40 UNIT SELLING PRICE - Micky 11.25 ? (1.5 marks] copy & paste copy & paste 41 UNIT SELLING PRICE - Mimi 12.25 ? (1.5 marks] copy & paste copy & paste 42 SALES REVENUE - Micky NA ? [1 mark] copy & paste copy & paste 43 SALES REVENUE - Mimi NA? [1 mark] copy & paste copy & paste 44 UNIT PRODUCTION & MARKETING COSTS: 45 ST LOUIS (BOTH DOLLS) 5 ? [1.5 marks] copy & paste copy & paste 46 BSN (BOTH DOLLS) 5.5 ? [1.5 marks] copy & paste copy & paste 47 TOTAL PRODUCTION & MARKETING COSTS 48 ST LOUIS (BOTH DOLLS) NA ? [1 mark] copy & paste copy & paste 49 BOSTON (BOTH DOLLS) NA? [1 mark] copy & paste copy & paste +------------------------------------ LOLO
A B с D E INCOME STATEMENT AND 52 CASH FLOW STATEMENT 2021 53 BEGINNING OF YEAR CASH ON HAND NA 55 REVENUE (SALES) NA 56 COSTS AND EXPENSES: 57 PRODUCTION AND MARKETING COSTS NA 58 SHIPPING AND WAREHOUSE COSTS NA 59 FIXED ADMIN COSTS NA 60 TOTAL COSTS NA 61 PRE-INTEREST EXPENSE MARGIN 62 INTEREST EXPENSE NA 63 INCOME BEFORE TAX NA 64 TAX EXPENSE NA 65 NET INCOME NA NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF NA 67 DEBT (BEG CASH PLUS NET INCOME) 68 ADD: BORROWINGS FROM BANK NA 69 LESS: REPAYMENTS TO BANK NA 70 EQUALS: END OF YEAR CASH ON HAND 10000 72 DEBT OWED 73 OWED TO BANK AT BEGINNING OF YEAR NA 74 ADD: BORROWINGS FROM BANK NA 75 LESS: REPAYMENTS TO BANK NA 76 EQUALS: OWED TO BANK AT END OF YEAR 3000000 2022 2023 2024 ? [0.25 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste ? [0.25 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste ? [1 mark] copy & paste copy & paste ? [0.25 mark copy & paste copy & paste ? [1 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste NA ? [0.5 mark] copy & paste copy & paste ? [1.5 marks] copy & paste copy & paste ? [4 marks] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste ? [0.25 mark] copy & paste copy & paste ? [0.25 mark] copy & paste copy & paste ? [0.25 mark] copy & paste copy & paste ? [0.5 mark] copy & paste copy & paste
се 2022 1.5 marks 24 25e 26Jq3 27 ܢܢ281 29e 1.5 marks 1.5 marks 1.5 marks 1.5 marks 1.5 marks 1.5 marks ܒܢ303 ܢܢܒ31 32- 334 34633 ܒܢ ܢܢ351 ܒܢ363 37 ܒ381 1 mark 1 mark 1 mark 1 marke 1 marke 1 marke 1.5 marks 1.5 marks 1 marke 1 mark 39 ܢܢ401 ܒܢ413 ܒܢ423 ܒܢ433 ܒܢ 44e 450 1.5 marks 1.5 marks ܢܢ461 47 ܒܢ ܢܢ481 ܢܢ491 1 mark 1 mark ܒܢܒ50 ܒܢܒ51 52 20222
ܒܢܒ53 0.25 mark ܒܢ ܒ 54 ܝܐ 0.5 marke ܒܢ ܢܢ551 ܒܢܒ56 ܒܢܒ%57 ܒܢܒ58 0.5 mark 0.5 marke 0.25 mlarks 0.5 marke ܒܢܒ:59 ܒmlark 0.5 ܒ60 ܒܒ61 ܠܨܒ62 ܠܨܒ63 ܒܨܒܘ64 ܒܢܒ65 ܒܢܢ66 1 mark 0.25 mark 1 marke 0.5 mark ܒܢ ܒܢܒ%67 ܒܢܒ68 0.5 mark 1.5 marks 4 marks 0.5 mark 69 ܠܢܒ70 ܒܢܢ71 ܢܢ 7242 ܒܢ ܒܢܒ73 ܒܢܒܪ74 0.25 mark 0.25 mark 0.25 mark 0.5 mark ܢܢܒܪ75 7633